mysql 开发进阶篇系列 8 锁问题 (共享锁与排它锁演示)

时间：2022-09-17 23:13:53

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
标签：

相关文章