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)中才能生效。

标签: for update