建表：

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 '测试表';

  1. 插入数据 

    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)