建表：
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 ‘测试表‘;
-
插入数据
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) |
本文出自 “Linux运维” 博客，请务必保留此出处http://2853725.blog.51cto.com/2843725/1394413