mysql 开发进阶篇系列 7 锁问题 (lock in share mode与for update)演示
1 .innodb 共享锁(lock in share mode)演示
|
会话1 |
会话2 |
|
SET autocommit=0; SELECT cityname FROM city WHERE city_id=14; city_id country_id cityname CityCode 14 2 22 001 |
SET autocommit=0; SELECT cityname FROM city WHERE city_id=14; city_id country_id cityname CityCode 14 2 22 001 |
|
-- 对 city_id=14加共享锁 SELECT * FROM city WHERE city_id=14 LOCK IN SHARE MODE; city_id country_id cityname CityCode 14 2 22 001 |
|
|
|
-- 也对 city_id=14加共享锁 SELECT * FROM city WHERE city_id=14 LOCK IN SHARE MODE; city_id country_id cityname CityCode 14 2 22 001 |
|
-- 当前会话对锁定的记录进行更新操作，等待锁。等待中.... UPDATE city SET cityname='深圳' WHERE city_id =14;
|
|
|
|
-- 会话2也对锁定的记录进行更新操作，则会导致死锁退出 UPDATE city SET cityname='深圳' WHERE city_id =14; 错误代码： 1213 Deadlock found when trying to get lock; try restarting transaction |
|
获得锁后，更新成功 查询：update city set cityname='深圳' where city_id =14 共 1 行受到影响 |
|
2. innodb 排它锁(for update)演示
|
会话1 |
会话2 |
|
SET autocommit=0; SELECT * FROM city WHERE city_id=14; city_id country_id cityname CityCode 14 2 22 001 |
SET autocommit=0; SELECT * FROM city WHERE city_id=14; city_id country_id cityname CityCode 14 2 22 001 |
|
-- 对 city_id=14加for update 共享锁 SELECT cityname FROM city WHERE city_id=14 FOR UPDATE ; cityname 11 |
|
|
|
-- 可以查询 SELECT cityname FROM city WHERE city_id=14 cityname 11 -- 但不能对 city_id=14加for update 共享锁 SELECT cityname FROM city WHERE city_id=14 FOR UPDATE ; 等待中... |
|
-- 更新后，释放锁 UPDATE city SET cityname='深圳' WHERE city_id =14; COMMIT; |
|
|
|
获取锁 for update共享锁,值还是11 cityname 11 |