sql使用for update控制并发
MySQL中select * from table where …... for update的用法
由于
InnoDB预设是Row-Level Lock(行级锁)，所以只有「明确」的指定主键，MySQL才会执行Row lock (只锁住被选取的资料例)，否则MySQL将会执行Table Lock (表锁)。
举个例子: 假设有个表单t，里面有id跟name二个栏位，id是主键。
CREATE TABLE t (
id int(11) NOT NULL DEFAULT '0',
name varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
例1: (明确指定主键，并且有此笔资料，row lock)
- 由此可见，当明确指定主键，并且有此资料时，锁的是where后面的记录，即这里的
id= 1；接下来来看下没有此资料的情况下会不会被锁。
窗口一：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where id = 1 for update;
id name
1 bingo
1 row in set (0.00 sec)
窗口二：
mysql> select * from t where id = 1;
id name
1 bingo
1 row in set (0.01 sec)
mysql> update t set name = 'xxm' where id = 2;
Query OK, 1 row affected
(0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t set name = 'icey' where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
例2: (明确指定主键，若查无此笔资料，无lock)
- 由此得出结论，在没有此资料的情况下，即使你for update也是不锁的
窗口1：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where id = 11 for update;
Empty set (0.00 sec)
窗口2：
mysql> update t set name = 'qweq' where id = 1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t set name = 'qw' where id = 2;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t set name = 'vqw' where id = 3;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t set name = 'vqws' where id = 4;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t set name = 'vqs' where id = 5;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t set name = 'vqs' where id = 11;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> select * from t;
+----+------+
id name
1 qweq
2 qw
3 vqw
4 vqws
5 vqs
例3: (无主键，table lock)
- 由此可见，没有明确主键的情况下锁全表；
窗口1：
mysql> select * from t where name ='qweq' for update;
id name
1 qweq
1 row in set (0.00 sec)
窗口2：
mysql> update t set name = 'vqs' where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t set name = 'vqs' where id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
例4:(主键不明确，table lock)
- 在主键不明确的情况下，锁全表
SELECT * FROM products WHERE id<>'3' FOR UPDATE;
窗口1：
mysql> select * from t where id <> 2 for update;
id name
1 qweq
3 vqw
4 vqws
5 vqs
4 rows in set (0.00 sec)
窗口2：
mysql> update t set name = 'vqs' where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t set name = 'vqs' where id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
例5: (主键不明确，table lock)
- 锁全表
窗口1：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where id like 3 for update;
id name
3 vqw
1 row in set (0.00 sec)
窗口2：
mysql> update t set name = 'vqs' where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t set name = 'vqs' where id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
注1:FOR UPDATE仅适用于InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。