MySQL中的事务和锁简单测试(r10笔记第46天)

一直以来,对于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的数据字典还是比较少,不过使用起来还是比较清晰。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2016-10-08

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据库

MySQL数据库性能优化之一

文章来自:博客 数据库属于 IO密集型的应用程序,其主要职责就是数据的管理及存储工作。而我们知道,从内存中读取一个数据库的时间是微秒级别,而从一块普通硬盘上读取...

60110
来自专栏Linux运维学习之路

mysql存储引擎、事务

MySQL存储引擎介绍 文件系统 操作系统组织和存取数据的一种机制。 文件系统是一种软件。 文件系统类型 ext2  ext3  ext4  xfs 数据 不管...

39514
来自专栏腾讯云数据库团队的专栏

PostgreSQL 的 MVCC 机制解析

PostgreSQL 是通过 MVCC (Multi-Version Concurrency Control) 来保证事务的原子性和隔离性,本文通过一些事例对 ...

6200
来自专栏沃趣科技

“mysqlbinlog”工具做binlog server靠谱吗?

玩过binlog server的同学都知道,它使用mysqlbinlog命令以daemon进程的方式模拟一个slave的IO线程与主库连接,可以很方便地即时同...

5008
来自专栏杨建荣的学习笔记

MySQL中的MVCC(r12笔记第35天)

最近同事也问了我关于MySQL MVCC的一些问题,我觉得这个话题蛮有意思, 而之前似乎也没有总结过,就参考了一些资料,把一些内容摘录出来。 什么是MVCC 以...

3657
来自专栏北京马哥教育

为MySQL入门者整理的MySQL常用命令

作者:o0DarkNessYY0o 来源:http://blog.csdn.net/o0darknessyy0o/article/details/5208097...

3169
来自专栏大眼瞪小眼

MySQL基础复习

2NF:每一个非主属性完全依赖于候选码(属性组的值能唯一的标识一个元组,但是其子集不可以)。

862
来自专栏用户2442861的专栏

数据库经典问题

原文  http://blog.csdn.net/fangjian1204/article/details/39085941

1513
来自专栏一枝花算不算浪漫

[Linux]Linux下安装和配置solr/tomcat/IK分词器 详细实例二.

35310
来自专栏数据和云

12c create spfile的警示

在12c中,create spfile命令又有了新的参数变更,引入了as copy选项,这个变化是由于一个Bug引入的。 通过以下的测试和验证过程,大家会发现新...

2925

扫码关注云+社区

领取腾讯云代金券