共计 2662 个字符,预计需要花费 7 分钟才能阅读完成。
本篇文章为大家展示了怎样进行 MySQL 中的事务和锁简单测试,内容简明扼要并且容易理解,绝对能使你眼前一亮,通过这篇文章的详细介绍希望你能有所收获。
一直以来,对于 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 中的事务和锁简单测试,你们学到知识或技能了吗?如果还想学到更多技能或者丰富自己的知识储备,欢迎关注丸趣 TV 行业资讯频道。