一、行锁之MySQL 使用SELECT ... FOR UPDATE 做事务写入前的确认
以MySQL 的InnoDB 为例,预设的Tansaction isolation level 为REPEATABLE READ。
在SELECT 的读取锁定主要分为两种方式:
SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE
这两种方式在事务(Transaction) 进行当中SELECT 到同一个数据表时,都必须等待其它事务数据被提交(Commit)后才会执行。而主要的不同在于LOCK IN SHARE MODE 在有一方事务要Update 同一个表单时很容易造成死锁 。
简单的说,如果SELECT 后面若要UPDATE 同一个表单,最好使用SELECT ... UPDATE。
查看死锁参考:《mysql 查看死锁和去除死锁》
举个例子说明:账号余额的更新业务
1.1、示例1:相同主键值的更新,第2次的select for update会阻塞 (明确指定主键,并且有此数据,row lock)
会话1开启一个事务:
SET AUTOCOMMIT=off; BEGIN; SELECT * FROM account_data.account WHERE account_id='0064ca796b7d450c9443bc540b2defc1' FOR UPDATE; SELECT SLEEP(160); ROLLBACK;
新开启会话2,为相同行执行for update的事务:
mysql> SET AUTOCOMMIT=off; Query OK, 0 rows affected (0.00 sec)mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM account_data.account WHERE account_id='0064ca796b7d450c9443bc540b2defc1' FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> SELECT SLEEP(160);
新开启会话3,查看lock情况:
SELECT * FROM `information_schema`.innodb_locks; SELECT * FROM `information_schema`.INNODB_LOCK_WAITS;
1.2、示例2:不同主键值的更新,第2次的select for update不会影响 (明确指定主键,若查无此数据,无lock)
会话1开启一个事务:
SET AUTOCOMMIT=off; BEGIN; SELECT * FROM account_data.account WHERE account_id='0064ca796b7d450c9443bc540b2defc1' FOR UPDATE; SELECT SLEEP(160); ROLLBACK;
新开启会话2,为相同行执行for update的事务:
mysql> SET AUTOCOMMIT=off; Query OK, 0 rows affected (0.00 sec)mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM account_data.account WHERE account_id='01213556b4704055bb7b1397780e8a6e' FOR UPDATE;
mysql> SELECT SLEEP(160);
新开启会话3,查看lock情况:
mysql> SELECT * FROM `information_schema`.innodb_locks\G; Empty set, 1 warning (0.00 sec)ERROR:
No query specifiedmysql>
1.3、示例3:相同索引值的更新,第2次的select for update会阻塞
会话1开启一个事务:
SET AUTOCOMMIT=off; BEGIN; SELECT * FROM account_data.account WHERE user_id='97189ba5dc624939873d39177dccf232' FOR UPDATE; SELECT SLEEP(160); ROLLBACK;
新开启会话2,为相同行执行for update的事务:
mysql> SET AUTOCOMMIT=off; Query OK, 0 rows affected (0.00 sec)mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM account_data.account WHERE user_id='97189ba5dc624939873d39177dccf232' FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> SELECT SLEEP(160);
新开启会话3,查看lock情况:
SELECT * FROM `information_schema`.innodb_locks;
结果如下:
锁的数据是我复合索引的两列值,我的索引如下:
SELECT * FROM `information_schema`.INNODB_LOCK_WAITS;
结果如下:
1.4、其它场景:
假设有个表单products ,里面有id 跟name 二个栏位,id 是主键。
例1: (明确指定主键,并且有此数据,row lock)
SELECT * FROM products WHERE id='3' FOR UPDATE;
例2: (明确指定主键,若查无此数据,无lock)
SELECT * FROM products WHERE id='-1' FOR UPDATE;
例3: (无主键,table lock)
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;
例4: (主键不明确,table lock)
SELECT * FROM products WHERE id<>'3' FOR UPDATE;
例5: (主键不明确,table lock)
SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;
注1: FOR UPDATE 仅适用于InnoDB,且必须在事务区块(BEGIN/COMMIT)中才能生效。
注2: 要测试锁定的状况,可以利用MySQL 的Command Mode ,开二个视窗来做测试。
锁超时参数:innodb_rollback_on_timeout见《MySQL锁之二:锁相关的配置参数》
mysql> SHOW VARIABLES LIKE 'innodb_rollback_on_timeout%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | innodb_rollback_on_timeout | OFF | +----------------------------+-------+ 1 row in set (0.01 sec)mysql>
二、表锁演示
表锁既可以是显式的也可以是隐式的。
3.1.1、表显式锁:
显式锁通过lock tables 和unlock tables完成。同时表锁还分为读锁和写锁。
早就听说lock tables和unlock tables这两个命令,从字面也大体知道,前者的作用是锁定表,后者的作用是解除锁定。但是具体如何用,怎么用,不太清楚。今天详细研究了下,总算搞明白了2者的用法。
lock tables 命令是为当前线程锁定表.这里有2种类型的锁定,一种是读锁定,用命令 lock tables tablename read;另外一种是写锁定,用命令lock tables tablename write.下边分别介绍:
3.1.1.1、 lock table 读锁定
如果一个线程获得在一个表上的read锁,那么该线程和所有其他线程只能从表中读数据,不能进行任何写操作。
下边我们测试下,测试表为user表。(user表必须为Myisam表)
不同的线程,可以通过开多个命令行MySQL客户端来实现:
时刻点 |
线程A(命令行窗口A) |
线程B(命令行窗口B) |
1 |
mysql> lock tables user read; Query OK, 0 rows affected (0.00 sec) mysql> 对user表加读锁定。 |
|
2 |
mysql> select * from user; +------+-----------+ | id | name | +------+-----------+ | 22 | abc | | 223 | dabc | | 2232 | dddabc | | 45 | asdsagd | | 23 | ddddddddd | +------+-----------+ 5 rows in set (0.00 sec) mysql> 自己的读操作未被阻塞 |
mysql> select * from user; +------+-----------+ | id | name | +------+-----------+ | 22 | abc | | 223 | dabc | | 2232 | dddabc | | 45 | asdsagd | | 23 | ddddddddd | +------+-----------+ 5 rows in set (0.00 sec) mysql> 其他线程的读也未被阻塞 |
3 |
mysql> insert into user values(12,'test'); ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be updated mysql> 发现本线程的写操作被阻塞 |
mysql> insert into user values(22,'2test'); 发现没有任何反应,一直等待中,说明没有得到写锁定,一直处于等待中。 |
4 |
mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) mysql> 释放读锁定。 |
mysql> insert into user values(22,'ddd'); Query OK, 1 row affected (1 min 27.25 sec) mysql> 在线程A释放读锁后,线程B获得了资源,刚才等待的写操作执行了。 |
5 |
mysql> lock tables user read local; Query OK, 0 rows affected (0.00 sec) mysql> 获得读锁定的时候增加local选项。 |
mysql> insert into user values(2,'b'); Query OK, 1 row affected (0.00 sec) mysql> 发现其他线程的insert未被阻塞。 |
6 |
mysql> update user set name = 'aaaaaaaaaaaaaaaaaaaaa' where id = 1; 但是其他线程的update操作被阻塞了。 |
注意:user表必须为Myisam表,以上测试才能全部OK,如果user表为innodb表,则lock tables user read local命令可能没有效果,也就是说,如果user表为innodb表,第6时刻将不会被阻塞,这是因为INNODB表是事务型的,对于事务表,例如InnoDB和BDB,--single-transaction是一个更好的选项,因为它不根本需要锁定表。
3.1.1.2、 lock table 写锁定
如果一个线程在一个表上得到一个 WRITE 锁,那么只有拥有这个锁的线程可以从表中读取和写表。其它的线程被阻塞。
写锁定的命令:lock tables user write (user表为Myisam类型的表)
参考如下测试:
时刻点 |
线程A(命令行窗口A) |
线程B(命令行窗口B) |
|
|
|
1 |
mysql> lock tables user write; Query OK, 0 rows affected (0.00 sec) 对user表加写锁定。 |
|
2 |
mysql> select * from user; +----+-----------------------+ | id | name | +----+-----------------------+ | 1 | aaaaaaaaaaaaaaaaaaaaa | | 2 | b | +----+-----------------------+ 2 rows in set (0.00 sec) 自己可以继续进行读操作 |
mysql> select * from user; 其他线程读操作被阻塞。 |
3 |
mysql> unlock tables ; Query OK, 0 rows affected (0.00 sec) 释放锁定。 |
|
4 |
mysql> select * from user; +----+-----------------------+ | id | name | +----+-----------------------+ | 1 | aaaaaaaaaaaaaaaaaaaaa | | 2 | b | +----+-----------------------+ 2 rows in set (32.56 sec) 其他线程获得资源,可以读数据了。 |
以上所有结果均在MySQL 5.4.3下测试通过。
原文地址:https://www.cnblogs.com/duanxz/p/3746568.html