山东大学移动信息门户13

2021SC@SDUSC

mysql的锁

乐观锁：假设本次操作，没有冲突，本质上就是CAS操作，需要我们自己实现，本质上是加一个version字段，在一个事务中，
              先获取某一行的version字段，等到更新的时候查看version是否跟刚才的version一样。 

             一般不会产生死锁，适合用于读多写少的情况

悲观锁：先假设这次操作一定有冲突，然后加锁（共享锁或者排他锁）， 共享锁或者排他锁都是悲观锁
              共享锁也叫读锁（会排斥其他写操作，不排斥读操作）， lock in share mode 指定
              排他锁：写锁，会排斥其他写和读操作,for update 指定 (update insert delete 自动加入排他锁，根据sql的编写情况决定是锁行还是锁表)

             适用于写多读少的情况

mysql通过分析sql ，隐式的采用行级锁或者表锁：好像mysql变聪明了，什么时候用行锁，什么时候用表锁，好像不能明确。。。

好像是这样的，用索引做的条件限制，一定用行锁，但是不是用索引做的条件限制，不一定不用行锁

看到过这样一句话：mysql默认隔离级别是可重复读，众所周知可重复读是可能导致幻读的，但是innodb引擎 通过mvvc和间隙锁 解决了幻读的问题！！！

读锁和写锁描述的是 锁之间的相互作用

行锁和表锁描述的是 锁的范围（对象）

表锁：锁住整个表，可能是被写锁锁定的，也可能是被读锁锁定的，可以减少死锁
行锁：锁住某一行，可能是被写锁锁定的，也可能是被读锁锁定的

一些测试

 mysql8.0 

T1表示第一个事务    T2 表示第二个事务

表结构，注意看user_id不是主键！！

 

我还插入了两个一样的user_id，并且我下面所有的测试，都是用user_id（非主键）来做where条件查询的！！！

共享锁的测试:结论：select 也就是读操作，不显示的加共享锁，是不会加锁的
 

T1对某一行加共享锁 T2也加共享锁 结果：成功

T1

SET AUTOCOMMIT=0;
BEGIN;
    SELECT * FROM account where user_id = '1' lock in share mode;

T2 

SET AUTOCOMMIT=0;
BEGIN;
    SELECT * FROM account where user_id = '1' lock in share mode;

T1不显式的加共享锁，然后做查询，T2做更新，成功

T1

SET AUTOCOMMIT=0;
begin;
select * from account where user_id = 1;

T2

SET AUTOCOMMIT=0;
begin;
UPDATE account set money = money + 100 where user_id = '1';

T1显式的加共享锁，然后做查询，T2做更新，失败

T1

SET AUTOCOMMIT=0;
begin;
select * from account where user_id = 1 lock in share mode;

T2

SET AUTOCOMMIT=0;
begin;
select * from account where user_id = 1 lock in share mode;

排他锁测试:update insert delete 自动加排他锁，排他锁允许不加锁的读

不能加 lock in share mode

UPDATE account set money = money - 100 where user_id = '2'  lock in share mode
> 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lock in share mode' at line 1

update自动加行锁的测试，并导致了死锁

T1

SET AUTOCOMMIT=0;
begin;
UPDATE account set money = money + 100 where user_id = '1';//步骤1，给用户1(两条记录)加了一个行锁，正常
UPDATE account set money = money  - 100 where user_id = '2';//步骤3，请求用户2的一个行锁，正常
COMMIT;//步骤五commit，T2事务导致的死锁被mysq解除了，不影响我T1的执行

T2

SET AUTOCOMMIT=0;
begin;
UPDATE account set money = money  - 100 where user_id = '2';//步骤2，给用户2加了一个行锁，正常，也说明步骤一，虽然没有用主键来做条件查询，但他还是是行锁，不是表锁
UPDATE account set money = money + 100 where user_id = '1';//步骤4，请求用户1的一个行锁，当我执行这一句的时候报了下面的错：死锁

UPDATE account set money = money + 100 where user_id = '1'
> 1213 - Deadlock found when trying to get lock; try restarting transaction
> 时间: 0.031s

> 1213 - Deadlock found when trying to get lock; try restarting transaction
> 时间: 0.031s

T1 update

SET AUTOCOMMIT=0;
begin;
UPDATE account set money = money + 100 where user_id = '1';

T2 select 并尝试加共享锁 失败

SET AUTOCOMMIT=0;
begin;
select * from account where user_id = 1 lock in share mode;

T2 select 但不显示加共享锁 成功

SET AUTOCOMMIT=0;
begin;
select * from account where user_id = 1 ;

所以我们这里也得出一个结论，排他锁会排斥其他排他锁和共享锁，但是不排斥不加锁的读操作！！！排他锁允许不加锁的读！！！

delete

步骤一 在T1中对第三条数据做删除但没提交

SET AUTOCOMMIT=0;
begin;
DELETE from account where user_id = '1' and money = 2200;

步骤二 在T2中做和步骤一一样的操作，阻塞，说明步骤一确实隐式的加锁了

SET AUTOCOMMIT=0;
begin;
DELETE from account where user_id = '1' and money = 2200;

步骤三 在T2中对第二条数据做更新，正常，说明步骤一是一个行级锁
 

SET AUTOCOMMIT=0;
begin;
UPDATE account set money = money - 100 where user_id = '2';