[大数据] 浅显易懂的理解SQL各种锁（基于MYSQL 8.0.28）

1. 表锁的读锁（MyISAM）

概念：针对同一份数据，多个读操作可以同时进行而不会互相影响。

显式加读锁：lock table 表名 read;，改命令为显式添加，MyISAM在执行查询默认会隐式添加读锁
显式释放读锁：unlock tables;，MyISAM默认查询结束后释放读锁

情景：客户端1 把表先用读锁锁起来了

结论：读锁会阻塞写，但是不会阻塞读

概念：当前操作没有完成之前，它会阻断整张表的其他写锁和读锁。

显式加写锁：lock table 表名 write;，MyISAM在执行查询默认会隐式添加写锁
显式释放写锁：unlock tables;，MyISAM默认查询结束后释放写锁

情景：客户端1 把表先用写锁锁起来了

结论：写锁既会阻塞写，也会阻塞读

情景：客户端1的事务由于需要update，把 id=3 这一行的数据用行锁锁住了，且客户端1的事务未提交

结论：对于增删改，InnoDB会自动给记录行加上行锁。对于查询操作，InnoDB不会加锁。

概念：间隙锁也称为GAP锁，InnoDB引擎遇到写操作中存在范围的条件，会自动对范围内的所有值上行锁写锁，即使这个有些值在表中并没有对应的记录。如果此时有了对空缺值的增删改操作，就会进入阻塞状态

情景：InnoDB表dept，有dno、dname、location三个字段，dno和dname有普通索引，默认行锁。（方便起见，两个客户端均关闭自动提交set autocommit = 0;，模拟两个事务的并发执行）

客户端1	客户端2
update dept set location=“test” where dno > 100	insert into dept values(103, ‘aa’, ‘vv’);（阻塞等待）

结论：间隙锁我们应该要尽可能的避免，方法就是尽可能的缩小或精确写操作的条件范围

情景：InnoDB表dept，有dno、dname、location三个字段，dno和dname有普通索引，默认行锁。（方便起见，两个客户端均关闭自动提交set autocommit = 0;，模拟两个事务的并发执行）

时间线	客户端1	客户端2
1	update dept set dname=“111” where dno=101;	update dept set dname=“222” where dno=102;
2	update dept set dname=“333” where dno=102;	update dept set dname=“444” where dno=101;

说明：客户端1 给101行加了行锁，客户端给102行加了行锁，客户端1需要102行的行锁来修改102行数据，客户端2需要101行的行锁修改101行数据，二者事务都没有提交，因此谁也不能释放锁，造成死锁。

把锁比作枪，把数据库比作银行，主人公为客户端

悲观锁概念：我去银行取钱，我觉得所有人都是坏人，所以我取钱时时刻刻都揣着枪小心前行，这样就没人来抢我的钱了。
乐观锁概念：我去银行取钱，我觉得一般不会有人来抢钱，所以当有人来抢我钱的时候我才把枪从包里拿出来。那么乐观锁又是怎么发现有人来抢钱的呢？一般设立一个version字段，自己取完钱就在该字段+1。在自己取钱前会先查一下这个字段的值，取钱操作前还会查询一遍该值是否和之前获取的值一致，不一致说明有人要来抢钱了，我就撤退，回滚。

时时刻刻举枪瞄准四周去取钱的效率肯定比背着包走路去取钱低，因此悲观锁的执行效率不如乐观锁，但是却安全可靠。乐观锁效率高，但是一旦被发现钱被抢了需要重新进行回滚，因此具有一些回滚动作的开销。

结论：悲观锁上来就上锁，乐观锁发现问题才上锁，前者效率比较低但是安全可靠适用于并发量低的场景，后者效率高但是出问题后需要进行业务回滚适用于并发量高的场景。