Select * from emp where empid > 100 for update; |
session_1 | session_2 |
mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec) mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) | mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec) mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) |
当前session对不存在的记录加for update的锁: mysql> select * from emp where empid = 102 for update; Empty set (0.00 sec) | |
这时,如果其他session插入empid为201的记录(注意:这条记录并不存在),也会出现锁等待: mysql>insert into emp(empid,...) values(201,...); 阻塞等待 | |
Session_1 执行rollback: mysql> rollback; Query OK, 0 rows affected (13.04 sec) | |
由于其他session_1回退后释放了Next-Key锁,当前session可以获得锁并成功插入记录: mysql>insert into emp(empid,...) values(201,...); Query OK, 1 row affected (13.35 sec) |
欢迎光临 MariaDB社区 (http://123.56.88.72/) | Powered by Discuz! X3.2 |