未加星标

MySQL中的事务和锁简单测试

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二05 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏

一直以来,对于mysql中的事务和锁的内容是浅尝辄止,没有花时间了解过,在一次看同事排查的故障中有个问题引起了我的兴趣,虽然过去了很久,但是现在简单总结一下还是有一些收获。

首先我们初始化数据,事务的隔离级别还是MySQL默认的RR,存储引擎为InnoDB

> create table test(id int,name varchar(30));

> insert into test values(1,'aa');

开启一个会话,开启事务。

会话1:

[test]>start transaction;
MySQL中的事务和锁简单测试

这个时候我们查看show processlist的信息是不会看到更为具体的SQL等的信息。


MySQL中的事务和锁简单测试

我们在另外一个会话中查看事务相关的一个表,Innodb_trx,其实它对应的存储引擎是MEMORY

[information_schema]>select *from innodb_trx\G
MySQL中的事务和锁简单测试

然后在会话1执行一条语句。

select * from test where id=1 for update;

再次查看事务表的信息,我们对比前后两次的结果变化,发现唯一的不同是trx_lock_structs的地方,由0变为了2


MySQL中的事务和锁简单测试

对于这个字段的含义,可以参考官方文档的介绍。

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如下:


MySQL中的事务和锁简单测试

我们简称为check_trx.sql,在这个场景中我们运行check_trx.sql会发现线程3573在等待,阻塞它的正是线程3574


MySQL中的事务和锁简单测试
MySQL中的事务和锁简单测试
MySQL中的事务和锁简单测试

这个时候有一个地方需要注意,那就是通过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条记录。


MySQL中的事务和锁简单测试
会话 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条记录了。


MySQL中的事务和锁简单测试
MySQL中的事务和锁简单测试

会话1:

在会话1中修改id=2的数据行。

select * from test where id=2 for update;

查看事务表,会有一条阻塞的信息。


MySQL中的事务和锁简单测试

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

本文地址 : http://www.linuxidc.com/Linux/2016-10/136030.htm

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: SQLMySQL数据InnoDBLinux360RYUC其实RIM
分页:12
转载请注明
本文标题:MySQL中的事务和锁简单测试
本站链接:http://www.codesec.net/view/483715.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(29)