首页 > SQL数据库管理 > [演示]Innodb默认隔离级别和死锁

[演示]Innodb默认隔离级别和死锁

2014年10月21日

[演示]Innodb默认隔离级别和死锁

http://blog.chinaunix.net/uid-16844903-id-3664861.html

关于MVCC,REPEATABLE-READ,死锁的概念这里不再做介绍,主要用两个测试说明隔离级别和死锁.

演示环境

  1. 建表语句
    CREATE TABLE `t01` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `a` varchar(45) NOT NULL COMMENT '',
    `b` varchar(45) NOT NULL COMMENT '',
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '测试表';
    
  2. 插入数据
    insert into t01 (a,b) values(123,500);
    insert into t01 (a,b) values(456,200);
    insert into t01 (a,b) values(789,100);
    

测试一:目的,演示MVCC和REPEATABLE-READ隔离界别

操作时间 操作说明 session1 session2
14:31:27 s1开始事务 begin;
14:31:31 s1查询
select * from t01;
+----+-----+-----+
| id | a   | b   |
+----+-----+-----+
| 1  | 123 | 500 |
| 2  | 456 | 200 |
| 3  | 789 | 100 |
+----+-----+-----+
14:34:48 s2开始事务 begin;
14:34:51 s2更改数据,并没有出现锁等待,
说明s1并没有锁住数据.
update t01 set b = b-50 where id = 3;
update t01 set b = b+50 where id = 1;
14:35:17 s2提交,并查看数据 commit;

select * from t01;
+----+-----+-----+
| id | a   | b   |
+----+-----+-----+
| 1  | 123 | 550 |
| 2  | 456 | 200 |
| 3  | 789 | 50  |
+----+-----+-----+
14:35:31 s1查询,结果发现,
用s2更新的数据并没有
在s1的结果集中出现,
所以说,s1读取的数据,
是在s1事务开始时的快照.
select * from t01;
+----+-----+-----+
| id | a   | b   |
+----+-----+-----+
| 1  | 123 | 500 |
| 2  | 456 | 200 |
| 3  | 789 | 100 |
+----+-----+-----+
14:36:14 s1提交,然后再查看,
结果集与s2查询的一致
select * from t01;
+----+-----+-----+
| id | a   | b   |
+----+-----+-----+
| 1  | 123 | 550 |
| 2  | 456 | 200 |
| 3  | 789 | 50  |
+----+-----+-----+

测试二:目的,演示死锁

操作时间 操作说明 session1 session2
16:03:16 s1开始事务 begin;
16:03:28 s1查询
select * from t01 where id =1;
+----+-----+-----+
| id | a   | b   |
+----+-----+-----+
|  1 | 123 | 550 |
+----+-----+-----+
16:03:49 s1更改数据
update t01 set b=b-50 where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
16:03:59 s2开始事务 begin;
16:04:08 s2查看数据
select * from t01 ;
+----+-----+-----+
| id | a   | b   |
+----+-----+-----+
|  1 | 123 | 550 |
|  2 | 456 | 200 |
|  3 | 789 | 50  |
+----+-----+-----+
3 rows in set (0.00 sec)
16:04:37 s2更改数据
update t01 set b=b-10 where id =3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
16:05:16 s1更改被s2锁住的数据,开始锁等待,19.97秒
update t01 set b=b+50 where id =3;
Query OK, 1 row affected (19.97 sec)
Rows matched: 1  Changed: 1  Warnings: 0
16:05:35 s2更改被s1锁住的数据,然后报告了死锁.
update t01 set b= b+10 where id =1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
16:05:57 s1提交事务 commit;
16:06:02 s2提交事务 commit;
16:06:09 用s2的窗口查看结果集,
发现提交生效的事务是s1.
select * from t01;
+----+-----+-----+
| id | a   | b   |
+----+-----+-----+
|  1 | 123 | 500 |
|  2 | 456 | 200 |
|  3 | 789 | 100 |
+----+-----+-----+
3 rows in set (0.00 sec)
16:07:24 查看死锁信息,发现回滚的事务是s2
------------------------
LATEST DETECTED DEADLOCK
------------------------
130508 16:05:35
*** (1) TRANSACTION:
TRANSACTION 132B, ACTIVE 127 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s), undo log entries 1
MySQL thread id 16, OS thread handle 0x7f7cb4ac8700, query id 85 192.168.236.242 root Updating
update t01 set b=b+50 where id =3
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12 page no 3 n bits 72 index `PRIMARY` of table `test`.`t01` trx id 132B lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 132C, ACTIVE 87 sec starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 376, 2 row lock(s), undo log entries 1
MySQL thread id 15, OS thread handle 0x7f7cb7d11700, query id 86 192.168.236.242 root Updating
update t01 set b= b+10 where id =1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 12 page no 3 n bits 72 index `PRIMARY` of table `test`.`t01` trx id 132C lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12 page no 3 n bits 72 index `PRIMARY` of table `test`.`t01` trx id 132C lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)

 

本文的评论功能被关闭了.