mysql并发实验

从工程角度说，系统设计时不推荐利用这里的结果细节，不容易理解与维护。

 

-- 实验准备：
CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`field` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into t (id, field) values(1, 1);

select * from t;
+----+-------+
| id | field |
+----+-------+
| 1 | 1 |
+----+-------+

-- 并发实验1：
A：begin;
B：begin;
A：select * from t where id = 1 for update;
B：update t set field = 2 where id = 1;（请求挂起，不会立即返回）
A：update t set field = 3 where field = 1;
B：(死锁异常，事务退出)
A：commit;

select * from t;
+----+-------+
| id | field |
+----+-------+
| 1 | 3 |
+----+-------+

-- 并发实验2（数据准备同上）：
A：begin;
B：begin;
A：select * from t where id = 1 for update;
B：update t set field = 2 where field = 1;（请求挂起，不会立即返回）
A：update t set field = 3 where field = 1;
B：(死锁异常，事务退出)
A：commit;

select * from t;
+----+-------+
| id | field |
+----+-------+
| 1 | 3 |
+----+-------+

-- 并发实验3（数据准备同上）：
A：begin;
B：begin;
A：select * from t where field = 1 for update;
B：update t set field = 2 where field = 1;（请求挂起，不会立即返回）
A：update t set field = 3 where field = 1;
A：commit;
B：(挂起请求正常返回，0 rows affected)
B：commit;

select * from t;
+----+-------+
| id | field |
+----+-------+
| 1 | 3 |
+----+-------+

-- 并发实验4（数据准备同上）：
A：begin;
B：begin;
A：select * from t where field = 1 for update;
B：update t set field = 2 where id = 1;（请求挂起，不会立即返回）
A：update t set field = 3 where field = 1;
A：commit;
B：(挂起请求正常返回，1 rows affected)
B：commit;

select * from t;
+----+-------+
| id | field |
+----+-------+
| 1 | 2 |
+----+-------+

-- 并发实验5（数据准备同上）：
A：begin;
B：begin;
B：update t set field = 2 where field = 1;
A：select * from t where field = 1 for update;（请求挂起，不会立即返回）
B：commit;
A：(挂起请求正常返回，空集合)
A：update t set field = 3 where field = 1;（0 rows affected）
A：commit;

select * from t;
+----+-------+
| id | field |
+----+-------+
| 1 | 2 |
+----+-------+