; SHOW OPEN TABLES WHERE In_use > 0;
这个命令监控的是被表锁锁住的表,亲测如果用行锁,这个命令是没有反应的,真的得自己动手实践才能发现真相。
获取行锁争用情况:
下面介绍几张表,可以帮助我们监控当前的事务并分析可能存在的锁问题。
select * from information_schema.innodb_trx;
主要字段如下:
trx_id:唯一的事务id号
trx_state:当前事务的状态,lock wait锁等待状态,running执行中状态。
trx_started:事务开始时间
trx_wait_started:事务开始等待时间
trx_mysql_thread_id:线程id
trx_query:事务运行的SQL语句
持有锁的对象:
select * from information_schema.innodb_locks;
锁等待的对象:
select * from information_schema.innodb_lock_waits;
实验内容:两个会话两个事务,会话1锁,会话2改,目标是不同的行数据。
会话1的where条件必须是索引,才能锁住这一行,否则就会锁住整张表的数据,让会话2上不了锁。
会话2的where条件也必须是索引,才能锁住这一行,否则会试图去锁整张表的数据,而整张表的数据已经有一行被会话1锁了,所以会话2锁不上。
即使在条件中使用了索引,但是是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的,如果MySQL认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,这种情况下InnoDB也会对全表记录上锁(申明一点,行锁不会升级成表锁,它实际上是把所有行都上了锁)。
MySQL的服务层不管理事务,事务是由下层的存储引擎实现的(表锁是由MySQL的服务层实现的),所以在同一个事务中,使用多种存储引擎的表是有风险的。
比如在事务中同时操作innodb和myisam的表,正常提交不会有问题,但是如果要回滚,myisam的表是不会被回滚的。
因此,在一个事务中,最好不要使用不同存储引擎的表。
答案是先开事务再锁表,因为START TRANSACTION语句会隐含了UNLOCK TABLES,一开事务就等于释放了之前的表锁。
InnoDB采用的是两阶段锁定协议。
在事务执行过程中,随时都可以执行锁定,锁只有在commit或者rollback的时候才会释放(这里说的是行锁哈^_^,表锁是不在存储引擎这层的),并且所有的锁是在同一时刻释放。
innodb会根据隔离级别在需要的时候自动加锁,优先走隔离级别的规则,然后才是行锁,如果数据确实隔离了,那么是不会上锁的(不信小伙伴们可以亲测,开事务改数据会自动上锁,但是开事务查数据不会上锁)。
显式加锁语句是LOCK IN SHARE MODE 和 FOR UPDATE了。
两种办法:
第一种,在事务中使用显式加锁语句,不在事务中使用你是感觉不到它上了锁的。
第二种,关闭自动提交模式
SET autocommit=0
关闭之后就可以不开事务直接显式上锁,直到你执行commit或者rollback它才会释放锁。
这其实就证明了一个很多人都不知道的事情:每一条SQL都是一个事务。只不过都是自动提交的,所以人们感觉不到事务的存在而已,当关闭了自动提交后,就必须手动提交事务才可以让SQL生效。
查询自动提交是否开启:
(这里有一个我还没弄明白的问题:我只能确定每个更新语句是开了事务的,但我不知道每一个查询是不是开了事务,没办法去证明,也没想出来该怎么去证明,有知道的小伙伴可以交流一下哦)