//加锁Flush tables with read lock;//释放锁命令unlock tables;
全局锁的典型使用场景是,做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。
mysql> UPDATE runoob_tbl SET runoob_title="学习 C++" WHERE runoob_id=1;2013 - Lost connection to server during querymysql> INSERT INTO runoob_tbl (runoob_title, runoob_author, submission_date) VALUES ("学习 PHP", "菜鸟教程", NOW());2013 - Lost connection to server during querymysql> SELECT * FROM runoob_tbl;+-----------+--------------+---------------+-----------------+| runoob_id | runoob_title | runoob_author | submission_date |+-----------+--------------+---------------+-----------------+| 1 | Go 学习 | 菜鸟教程 | 2023-03-22 |+-----------+--------------+---------------+-----------------+1 row in set (0.01 sec)
//select * from Table //快照读//Insert Update Delete //当前读//Select ... lock in share mode //当前读//Select ... for update //当前读
事务的隔离解决有四种,可重复读(RR)、读已提交(RC)、读未提交、序列化,查看全局隔离权限语句,旧版的myql使用tx开头,否则报错1193 - Unknown system variable "tx_isolation"。
mysql> show variables like "transaction_isolation";+-----------------------+-----------------+| Variable_name | Value |+-----------------------+-----------------+| transaction_isolation | REPEATABLE-READ |+-----------------------+-----------------+1 row in set (0.07 sec)mysql> select @@transaction_isolation;+-------------------------+| @@transaction_isolation |+-------------------------+| REPEATABLE-READ |+-------------------------+
如果没有设置隔离级别,可使用下面语句进行设置。
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;Query OK, 0 rows affected (0.03 sec)