一直以来，对于MySQL中的事务和锁的内容是浅尝辄止，没有花时间了解过，在一次看同事排查的故障中有个问题引起了我的兴趣，虽然过去了很久，但是现在简单总结一下还是有一些收获。
首先我们初始化数据，事务的隔离级别还是MySQL默认的RR，存储引擎为InnoDB
> create table test(id int,name varchar(30));
> insert into test values(1,"aa");
开启一个会话，开启事务。
会话1：
[test]>start transaction;
这个时候我们查看show processlist的信息是不会看到更为具体的SQL等的信息。
我们在另外一个会话中查看事务相关的一个表,Innodb_trx,其实它对应的存储引擎是MEMORY
[information_schema]>select *from innodb_trx\G
然后在会话1执行一条语句。
select * from test where id=1 for update;
再次查看事务表的信息，我们对比前后两次的结果变化，发现唯一的不同是trx_lock_structs的地方，由0变为了2
对于这个字段的含义，可以参考官方文档的介绍。
https://dev.mysql.com/doc/refman/5.6/en/innodb-trx-table.html
对于字段TRX_LOCK_STRUCTS的官方解释如下：
The number of locks reserved by the transaction.
会话2：
这个时候在会话2中执行语句会发生阻塞，因为存在相应的锁等待。
select * from test where id=1 for update;
等待一段时间，会话2就会提示超时。
[test]>select * from test where id=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
这个地方和一个参数是有关联的，innodb_lock_wait_timeout它会控制阻塞等待的时长。
[test]>show variables like "%innodb_lock_wait_timeout%";
| Variable_name | Value |
| innodb_lock_wait_timeout | 120 |
对于事务相关的信息查看，在MySQL中有三个比较经典的数据字典，innodb_lock_waits,innodb_trx,innodb_trx，三者可以结合起来，就能够查到相对比较完整的阻塞信息和事务的情况，官方提供的一个SQL如下：
我们简称为check_trx.sql，在这个场景中我们运行check_trx.sql会发现线程3573在等待，阻塞它的正是线程3574
这个时候有一个地方需要注意，那就是通过show engine innodb status得到的结果中，标红的部分可以看出锁是表级锁。这个还是和表的结构有一定的关系。
我们可以换一个方式来测试完善，比如测试一下死锁。
测试死锁
首先给表test添加一条记录
insert into test values(2,"bb");
为了杜绝表级锁，对表test 添加主键,如果采用下面的方式添加主键，竟然不可以，看来Oracle用惯了，很多思维方式要复制过来，SQL语法还是有不少地方需要注意。
[test]>alter table test modify id primary key;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server vline 1。。。
可以使用下面的方式来添加主键。
[test]>ALTER TABLE test ADD UNIQUE INDEX (id), ADD PRIMARY KEY (id);
Query OK, 2 rows affected (0.25 sec)
Records: 2 Duplicates: 0 Warnings: 0
接下来来复现一下死锁的情况。
会话1：
开启事务，更新id=1的那行数据。
start transaction;
[test]>select * from test where id=1 for update;
+----+------+
| id | name |
+----+------+
| 1 | aa |
+----+------+
1 row in set (0.00 sec)
这个时候查看innodb_trx的信息，只有1条记录。
会话2：
开启事务，更新id=2的那行数据。
start transaction;
select * from test where id=2 for update;
(root:localhost:Sat Oct 8 18:15:10 2016)[test]>select * from test where id=2 for update;
+----+------+
| id | name |
+----+------+
| 2 | bb |
+----+------+
1 row in set (0.00 sec)
这个时候两者是不存在阻塞的情况，因为彼此都是影响独立的行。
>source check_trx.sql
Empty set (0.00 sec)
查看事务表，里面就是2条记录了。
会话1：
在会话1中修改id=2的数据行。
select * from test where id=2 for update;
查看事务表，会有一条阻塞的信息。
会话2：
在会话2中修改id=1的数据行，这个时候会发现存在死锁，而MySQL会毫不犹豫的清理掉阻塞的那个会话。这个过程是自动完成的。
[test]>select * from test where id=1 for update;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
查看阻塞的信息，就会发现已经被清理掉了。
[(none)]>source check_trx.sql
Empty set (0.00 sec)
查看事务表，会发现只有1条记录了。
总体感觉MySQL的数据字典还是比较少，不过使用起来还是比较清晰。
死锁原理与分析
本文前面的部分，基本上已经涵盖了MySQL/InnoDB所有的加锁规则。深入理解MySQL如何加锁，有两个比较重要的作用：
可以根据MySQL的加锁规则，写出不会发生死锁的SQL； 可以根据MySQL的加锁规则，定位出线上产生死锁的原因； 下面，来看看两个死锁的例子 (一个是两个Session的两条SQL产生死锁；另一个是两个Session的一条SQL，产生死锁)： 上面的两个死锁用例。第一个非常好理解，也是最常见的死锁，每个事务执行两条SQL，分别持有了一把锁，然后加另一把锁，产生死锁。 第二个用例，虽然每个Session都只有一条语句，仍旧会产生死锁。要分析这个死锁，首先必须用到本文前面提到的MySQL加锁的规则。针对Session 1，从name索引出发，读到的[hdc, 1]，[hdc, 6]均满足条件，不仅会加name索引上的记录X锁，而且会加聚簇索引上的记录X锁，加锁顺序为先[1,hdc,100]，后[6,hdc,10]。而Session 2，从pubtime索引出发，[10,6],[100,1]均满足过滤条件，同样也会加聚簇索引上的记录X锁，加锁顺序为[6,hdc,10]，后[1,hdc,100]。发现没有，跟Session 1的加锁顺序正好相反，如果两个Session恰好都持有了第一把锁，请求加第二把锁，死锁就发生了。 结论：死锁的发生与否，并不在于事务中有多少条SQL语句，死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。而使用本文上面提到的，分析MySQL每条SQL语句的加锁规则，分析出每条语句的加锁顺序，然后检查多个并发SQL间是否存在以相反的顺序加锁的情况，就可以分析出各种潜在的死锁情况，也可以分析出线上死锁发生的原因。http://m.bbyears.com/shujuku/111852.html
推荐访问: