mysql行锁表锁与索引的关系,主动加读(乐观)写(悲观)锁；意向锁

innodb和mysam

目前大家用的mysql中表的engine都是innodb,很少会用mysam了,就是因为在更新数据的时候mysam会锁表,导致并发更新的时候只能一个个排队更新,对效率影响很大,而innodb则是行锁,大家只更新自己的那条,冲突的时候才排队,这样效率会高很多.

打个比方来说,开车通过高速收费站,这个收费站有3个口.一辆车通过的时候mysam engine把3个都锁了,innodb则只锁自己那一个通道.效率可想而知.

demo

因为上图中,开启了事务,锁定了product_id=1的那行记录,所以其他请求去更新的时候肯定是被阻塞的.只有等到上图的事务commit之后,其他请求才能更新这行记录

innodb的索引与行级锁

如果更新一个字段,然后在筛选的时候where后面的字段没有加索引,就会把整个表的所有记录都锁定.

在where后面的筛选字段加上索引后就不会锁表只会锁行,即使这个筛选范围字段只是组合索引里的其中一个

这里我经过实际操作发现了个现象,当更新的时候如果以主键为where条件,并且主键类型是varcahr的但是你在条件里写成int的了(都是数字,类型不同),第一个用户没有提交事务,会阻塞后面的用户更新另一条记录.
所以这里要注意,更新的时候一定要注意看清字段的类型写sql

2个demo如下

product_info表没有设置索引,我在更新product_id=1的记录之前开启了事务,然后把这一行锁定了,但是我接着去更新product_id=3的记录的时候发现,那行记录也被锁定了,这是因为筛选的列没有加索引.导致锁表.这是mysql为了保证数据的稳定性

demo1
product_id是varchar类型的主键但是sql中写成了int,这会造成不走索引

demo2,加上索引后可以实现行级锁
product_id是varcahr类型的主键,第一个用户在更新product_id='1’的记录的时候第2个用户仍旧可以更新其它行的记录

范围更新的时候的行级锁

在范围更新的时候也是同理(加了索引的前提,不加索引直接锁表GG),会把整个范围之间的记录全部锁定,别人想操作只有等你释放这个范围之间的行级锁

mysql使用悲观锁

demo如下
当select XXXX for update就会锁住这一行记录,别人只能查不能更新,即加了写锁

mysql 乐观锁

用户1在查询的时候version=0,用户2在查询的时候version也是0,然后用户1update前开启了事务,更新的时候 让version = version+1,num = num -100
条件是之前查询的id和version=0.此时事务还没提交,更新的结果已经写进表里了(此时version=1,num=400),然后用户2也是同样的更新语句,在用户1事务提交后释放这一行记录的行级锁,用户2也更新了,但是因为where条件中的version=0,所以更新没成功

demo如下

mysql读锁和写锁(针对整张表)操作

读锁

加锁:lock table XX read
释放锁:unlock table
加上读锁后大家都可以查询,对于当前加锁的这个用户来说,也只能查询(读),更新插入(写)的时候就会报错
对于其他用户来说可以查询,但是更新(写)操作会被阻塞,指导加锁的用户释放这个读锁

写锁

在当前会话中可以读写,别的会话不能读也不能写,都会被阻塞,直到当前会话释放这个锁

加锁:lock table XXX write;
解锁:unlock table;

在实际操作写锁的时候发现navicat一个查询标签在加了写锁后会把自己也锁住,这操作骚的一批.

意向锁

在平时一般sql是不太会锁表的，但是如果一些情况下需要锁表，那么mysql是怎么判断当前有没有事务正持有行级排它锁呢。（暴力法就是for循环一行行的寻找，但效率太低，所以mysql用了意向锁）

当A事务持有id=5的数据的行锁的时候；
B事务想锁表，那么必需等待A事务释放意向排他锁。空出一个干净的表，来让B事务锁表。

其实在A事务获取行锁前会先获取表的意向排它锁。这样B事务就不需要轮询去检查了。
并且此时C事务在A事务持有意向排过程中，还能在这基础上添加自己的意向排它锁。
所以可以得出意向排它锁之间不是互斥的。