越努力越幸运!
Mysql锁介绍
Mysql不同的存储引擎支持不同的锁机制,根据不同的引擎划分,mysql锁机制分类如下:
行锁 | 表锁 | |
MyISAM | 不支持 | 支持 |
InnoDB | 支持 | 支持 |
很明显可以看到,MyISAM不支持行锁,而InnoDB支持行锁,可能还有人说BDB引擎的页锁呢? 这里不做介绍,BDB已经被InnoDB取代了。
- 表锁:开销小,加锁快;不会出现死锁;锁定力度大,发生锁冲突概率高,并发度最低
- 行锁:开销大,加锁慢;会出现死锁;锁定粒度小,发生锁冲突的概率低,并发度高
所以MyISAM不会出现死锁的情况。而InnoDB则会出现,后面会介绍到。
MyISAM锁
基本介绍
我们可以在使用数据库时,可以通过命令来查看数据库表锁的竞争情况
MariaDB [test]> show status like 'table%';
+-----------------------+-----------+
| Variable_name | Value |
+-----------------------+-----------+
| Table_locks_immediate | 128366245 |
| Table_locks_waited | 1670689 |
+-----------------------+-----------+
2 rows in set (0.00 sec)
Table_locks_waited 的值越高说明存在较严重的表级锁竞争情况;
MyISAM的表级锁有两种模式,表共享读锁和表独占写锁。锁模式的兼容性如下所示
请求锁模式 是否兼容 当前所模式 | 共享读锁 | 独占写锁 |
共享读锁 | Y | N |
独占写锁 | N | N |
可见MyISAM的特点:
1.对于表的读操作,不会阻塞其他用户对同一表的读操作;但是会阻塞其他用户对同一表的写操作;
2.对于表的写操作,会阻塞其他用户对同一表的读和写操作,只有当写操作释放锁后,其他用户才能继续执行。
下面来看一下实际例子
实际演示
这里来模拟MyISAM引擎加锁的操作,事实上,在sql执行update,insert,delete操作时,会自动给表加上独占写锁,在select前会自动给加上表读锁。
假设有两个session分布操作mysql,如下(假设MyISAM引擎类型的表已经创建好)
session1 | session2 |
1.获取locktest表的写锁 MariaDB [test]> lock table locktest write; | |
2.对当前表进行select,或者update都是可以成功的 MariaDB [test]> select * from locktest; +----+------+------+-----------------+ | id | name | age | address | +----+------+------+-----------------+ | 1 | aaa | 1 | aaa@aaa.com | | 2 | bbb | 2 | bbb@bbb.com |
MariaDB [test]> update locktest set address='aa@aa.com' where id=1; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0
| 在session2中对locktest表进行select,发现一直卡住了,没有执行 MariaDB [test]> select * from locktest; 等待
|
3.释放写锁 MariaDB [test]> unlock tables ; Query OK, 0 rows affected (0.00 sec) | 等待的语句开始执行 MariaDB [test]> select * from locktest; +----+------+------+-----------+ | id | name | age | address | +----+------+------+-----------+ | 1 | aaa | 1 | aa@aa.com | | 2 | bbb | 2 | bb@bb.com | +----+------+------+-----------+ 2 rows in set (3 min 53.21 sec)
|
再次测试下读锁,获取locktest的读锁 MariaDB [test]> lock table locktest read; Query OK, 0 rows affected (0.01 sec)
| |
执行select语句 MariaDB [test]> select * from locktest; +----+------+------+-----------+ | id | name | age | address | +----+------+------+-----------+ | 1 | aaa | 1 | aa@aa.com | | 2 | bbb | 2 | bb@bb.com |
这时候如果在session1中执行update操作,会怎么样? MariaDB [test]> update locktest set age=10 where id=1; ERROR 1099 (HY000): Table 'locktest' was locked with a READ lock and can't be updated 提示目前获取的read锁,无法update操作
如果这时候select其他表呢? MariaDB [test]> select * from locktestInno; ERROR 1100 (HY000): Table 'locktestInno' was not locked with LOCK TABLES 提示其他表没有获取锁。 MyISAM总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁(Deadlock Free)的原因
如果select 当前表的别名呢? MariaDB [test]> select * from locktest as a; ERROR 1100 (HY000): Table 'a' was not locked with LOCK TABLES 也会提示错误,怎么解决呢? 这时候,我们需要在锁表的时候,也加上别名 MariaDB [test]> lock table locktest as a read; Query OK, 0 rows affected (0.00 sec) | 执行update,这时候update会获取write锁,但是session1的read锁还未释放,所以进入等待 MariaDB [test]> update locktest set age=10 where id=1; 等待 |
那是不是MyISAM在写锁的情况下,其他session就一定无法进行插入操作呢?
答案是否定的,接下来介绍一下mysql的并发插入;
MyISAM存储引擎有一个系统变量concurrent_insert,专门用以控制其并发插入的行为,其值分别可以为0、1或2。
- 当concurrent_insert设置为0时,不允许并发插入。
- 当concurrent_insert设置为1时,如果MyISAM表中没有空洞(即表的中间没有被删除的行),MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录。这也是MySQL的默认设置。
- 当concurrent_insert设置为2时,无论MyISAM表中有没有空洞,都允许在表尾并发插入记录。
Mysql里面先查一下变量concurrent_insert的value
MariaDB [test]> show variables like '%concurrent_insert%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| concurrent_insert | AUTO |
+-------------------+-------+
5.5.3以后的value改版了,AUTO代表为1的情况,下图为对应关系:
在如下表所示的例子中,session1获得了一个表的READ LOCAL锁,可以对表进行查询操作,但不能对表进行更新操作;其他的线程(session2),虽然不能对表进行删除和更新操作,但却可以对该表进行并发插入操作,这里假设该表中间不存在空洞(没有delete或update操作)。
session1 | session2 |
1.获取locktest的read锁,必须加上local关键字,否则不生效,local关键字就是满足并发插入的条件下,允许其他进程进行表尾插入记录。 MariaDB [test]> lock table freeblock read local; Query OK, 0 rows affected (0.00 sec) | |
2.当前session进入插入或者update均失败,原因是只获取到了读锁
MariaDB [test]> insert into freeblock(count)values(2); ERROR 1099 (HY000): Table 'freeblock' was locked with a READ lock and can't be updated MariaDB [test]> update freeblock set count=4 where id=1; ERROR 1099 (HY000): Table 'freeblock' was locked with a READ lock and can't be updated | 这是如果在session2执行insert操作,则可以成功 insert into freeblock(count)values(3); |
3.当前session查看session2插入的记录,发现查不到 MariaDB [test]> select * from freeblock where count=3; Empty set (0.00 sec)
| session2进行update操作,进入等待 MariaDB [test]> update freeblock set count=10 where id=1; 等待 |
4.释放表读锁 MariaDB [test]> unlock tables ; Query OK, 0 rows affected (0.00 sec) | update操作执行成功 MariaDB [test]> update freeblock set count=10 where id=1; Query OK, 1 row affected (23.73 sec) Rows matched: 1 Changed: 1 Warnings: 0
|
5.再次查看session2插入的记录 MariaDB [test]> select * from freeblock where count=3; +----+-------+ | id | count | +----+-------+ | 3 | 3 | +----+-------+ 1 row in set (0.00 sec) |
MyISAM的锁调度情况:
MyISAM存储引擎的读锁和写锁是互斥的,读写操作是串行的。那么,一个进程请求某个 MyISAM表的读锁,同时另一个进程也请求同一表的写锁,MySQL如何处理呢?答案是写进程先获得锁。不仅如此,即使读请求先到锁等待队列,写请求后到,写锁也会插到读锁请求之前!这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因,因为,大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞。这种情况有时可能会变得非常糟糕!幸好我们可以通过一些设置来调节MyISAM 的调度行为。
- 通过指定启动参数low-priority-updates,使MyISAM引擎默认给予读请求以优先的权利。
- 通过执行命令SET LOW_PRIORITY_UPDATES=1,使该连接发出的更新请求优先级降低。
- 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性,降低该语句的优先级。
虽然上面3种方法都是要么更新优先,要么查询优先的方法,但还是可以用其来解决查询相对重要的应用(如用户登录系统)中,读锁等待严重的问题。
InnoDB锁
InnoDB比MyISAM的最大的不同点是,1.支持事务;2.使用行锁。
事务的一些特点及面临的一些问题这里不做介绍,类似银行转账的例子,要么成功,要么失败。
InnoDB行锁介绍
我们可以通过show status like 'innodb_row_lock%'命令来查看行锁竞争情况
MariaDB [test]> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 257703 |
| Innodb_row_lock_time_avg | 100 |
| Innodb_row_lock_time_max | 51834 |
| Innodb_row_lock_waits | 2556 |
+-------------------------------+--------+
Innodb_row_lock_waits和InnoDB_row_lock_time_avg值越大,代表行锁争夺严重;可以通过InnoDB Monitors来观察。
InnoDB实现了以下两种行锁:
- 共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
- 排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。
另外,为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁。
- 意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。
- 意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。
意向锁是InnoDB自动加的,不需用户干预。对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);对于普通SELECT语句,InnoDB不会加任何锁;事务可以通过以下语句显示给记录集加共享锁或排他锁。
- 共享锁(S):SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
- 排他锁(X):SELECT * FROM table_name WHERE ... FOR UPDATE。
锁的兼容情况如下:
请求锁模式 当前锁模式 | S | X | IS | IX |
S | Y | N | Y | N |
X | N | N | N | N |
IS | Y | N | Y | Y |
IX | N | N | Y | Y |
InnoDB示例
之前有讲到InnoDB会出现死锁的情况,这里我们会设计一个这样的场景出来,有个事务使用select share mode获取共享锁,这时事务还想update数据,就直接update了,这样有可能会造成死锁的产生,具体来看一下步骤:
session1 | session2 |
1.设置autocommit=0,即不立即提交; MariaDB [test]> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
| |
2.通过select share mode来获取共享锁 MariaDB [test]> select * from locktestInno lock in share mode; +----+------+------+------------+ | id | name | age | address | +----+------+------+------------+ | 1 | aaa | 20 | aa@aa.com | | 2 | bbb | 2 | abb@aa.com | +----+------+------+------------+ | 执行select操作 MariaDB [test]> select * from locktestInno; +----+------+------+------------+ | id | name | age | address | +----+------+------+------------+ | 1 | aaa | 20 | aa@aa.com | | 2 | bbb | 2 | abb@aa.com |
通过share mode获取锁,也能够获取成功 MariaDB [test]> select * from locktestInno lock in share mode; +----+------+------+------------+ | id | name | age | address | +----+------+------+------------+ | 1 | aaa | 20 | aa@aa.com | | 2 | bbb | 2 | abb@aa.com | +----+------+------+------------+
|
3.执行update操作 MariaDB [test]> update locktestInno set age=10 where id=1; 等待
| session2页执行update操作,这时就会出现死锁 MariaDB [test]> update locktestInno set age=10 where id=1; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
|
4.session2出现死锁后,会释放锁,这边的等待的更新操作就会执行成功了 MariaDB [test]> update locktestInno set age=10 where id=1; Query OK, 1 row affected (14.18 sec) Rows matched: 1 Changed: 1 Warnings: 0 | |
通过commit提交,锁会自动释放 MariaDB [test]> commit; Query OK, 0 rows affected (0.17 sec) | commit提交 |
可以上到上述出现死锁的原因,获取共享锁后进行更新操作,所以更新操作要通过for update来获取排它锁,那现在来看下排它锁
session1 | session2 |
1.设置autocommit=0 MariaDB [test]> set autocommit=0; Query OK, 0 rows affected (0.00 sec) | 设置autocommit=0 |
2.获取排它锁 MariaDB [test]> select * from locktestInno for update; +----+------+------+------------+ | id | name | age | address | +----+------+------+------------+ | 1 | aaa | 10 | aa@aa.com | | 2 | bbb | 2 | abb@aa.com | +----+------+------+------------+
| session2可以进行select操作,但是无法获取共享锁(InnoDb select操作是不加锁的) MariaDB [test]> select * from locktestInno; +----+------+------+------------+ | id | name | age | address | +----+------+------+------------+ | 1 | aaa | 10 | aa@aa.com | | 2 | bbb | 2 | abb@aa.com | +----+------+------+------------+ 2 rows in set (0.00 sec)
MariaDB [test]> select * from locktestInno lock in share mode; 等待 |
3.进行update操作,并释放锁 MariaDB [test]> update locktestInno set age=30 where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
| |
4.释放锁 MariaDB [test]> commit; Query OK, 0 rows affected (0.00 sec)
| commit后,其他session能够获取到锁 MariaDB [test]> select * from locktestInno lock in share mode; +----+------+------+------------+ | id | name | age | address | +----+------+------+------------+ | 1 | aaa | 30 | aa@aa.com | | 2 | bbb | 2 | abb@aa.com | +----+------+------+------------+ |
重要说明,InnoDB的行锁是针对索引的,而不是记录。
InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!
在实际应用中,要特别注意InnoDB行锁的这一特性,不然的话,可能导致大量的锁冲突,从而影响并发性能。下面通过一些实际例子来加以说明。
下面通过例子来说明:
1.在where语句中不使用索引字段,session1获取其中一行的排它锁后,session2再获取另一行的排它锁,按照理论来讲,session2会进入等待;
session1 | session2 |
1.同样设置autocommit=0,获取排它锁,根据条件age来获取 MariaDB [test]> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> select * from locktestInno where age=2 for update; +----+------+------+------------+ | id | name | age | address | +----+------+------+------------+ | 2 | bbb | 2 | abb@aa.com | +----+------+------+------------+ | session2获取排它锁时,遇到等待,奇怪,InnoDB不是行锁吗?为什么取不同的行还是要阻塞呢? 可见是使用了表锁 MariaDB [test]> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> select * from locktestInno where age=10 for update; 等待 |
2.将age字段添加索引 MariaDB [test]> alter table locktestInno add index(age); Query OK, 0 rows affected (0.25 sec) Records: 0 Duplicates: 0 Warnings: 0 | |
3.再次操作步骤1 | 能够执行了,所以这里使用了行锁,可以验证之前的说法,行锁是基于索引的, MariaDB [test]> select * from locktestInno where age=30 for update; +----+------+------+-----------+ | id | name | age | address | +----+------+------+-----------+ | 1 | aaa | 30 | aa@aa.com | +----+------+------+-----------+ 1 row in set (0.00 sec) |
无论是主键索引还是普通索引InnoDB均使用的行锁进行加锁,可以看下table的索引,自己感兴趣可以试下,是否会使用表锁。
MariaDB [test]> show index in locktestInno;
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| locktestInno | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
| locktestInno | 1 | name | 1 | name | A | 2 | NULL | NULL | YES | BTREE | | |
| locktestInno | 1 | age | 1 | age | A | 2 | NULL | NULL | YES | BTREE | | |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
即便在条件中使用了索引字段,但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的,如果MySQL认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,这种情况下InnoDB将使用表锁,而不是行锁。因此,在分析锁冲突时,别忘了检查SQL的执行计划,以确认是否真正使用了索引。
比如对于下面的例子就没有使用到索引,table中的name字段为varchar类型,且建了索引。但是在搜索的时候,如果使用的不是varchar类型进行比较,则会进行类型转换,而使用全表扫描。
MariaDB [test]> select * from courseInno;
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | 1 | 10 |
| 2 | 2 | 20 |
+----+------+-------+
2 rows in set (0.00 sec)
MariaDB [test]> alter table courseInno add index(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test]> explain select * from courseInno where name=1;
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | courseInno | ALL | name | NULL | NULL | NULL | 2 | Using where |
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
MariaDB [test]> explain select * from courseInno where name='1';
+------+-------------+------------+------+---------------+------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+------------+------+---------------+------+---------+-------+------+-----------------------+
| 1 | SIMPLE | courseInno | ref | name | name | 138 | const | 1 | Using index condition |
+------+-------------+------------+------+---------------+------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
由于行锁是针对索引的,所以对于两个session,如果使用了相同的索引,其中一个也会进行阻塞,如下
session1 | session2 |
1.设置autocommit=0,获取排它锁 MariaDB [test]> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> select * from locktestInno where age=2 and address like 'aa%' for update; +----+------+------+-----------+ | id | name | age | address | +----+------+------+-----------+ | 1 | aaa | 2 | aa@aa.com | +----+------+------+-----------+ 1 row in set (0.35 sec) | session2也获取排它锁,这时进入等待,原因是使用了相同的索引age=2; MariaDB [test]> select * from locktestInno where age=2 and address like 'abb%' for update; 等待 |
间隙锁(Next-Key锁)
什么是间隙锁?当我们用范围条件而不是用相等条件去检索数据时,并请求共享或排它锁时,InnoDB会给符合条件的已有数据记录的索引项加上锁,对于键值在条件范围内但不存在的记录,叫做间隙;InnoDB也会对这种间隙加锁,这种锁机制就是间隙锁。
举例来说,假如一个表locktestInno里面的id字段的值分别1,2,3,。。。101,那sql语句
select * from locktestInno where id >100;
是一个范围检索,这时InnoDB不仅会对id为101的记录加锁,也会对id大于101的记录(这些记录不存在)加锁
InnoDB使用间隙锁的目的,一方面是为了防止幻读,以满足相关隔离级别的要求,对于上面的例子,要是不使用间隙锁,如果其它事务插入了empid大于100的任何记录,那么本事务如果再次执行上述语句,就会发生幻读;另外一方面,是为了满足其恢复和复制的需要。有关其恢复和复制对锁机制的影响,以及不同隔离级别下InnoDB使用间隙锁的情况。
很显然,在使用范围条件检索并锁定记录时,InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入,这往往会造成严重的锁等待。因此,在实际应用开发中,尤其是并发插入比较多的应用,我们要尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件。
还要特别说明的是,InnoDB除了通过范围条件加锁时使用间隙锁外,如果使用相等条件请求给一个不存在的记录加锁,InnoDB也会使用间隙锁!
下面来看下间隙锁的例子:
session1 | session2 |
1.首先看下事务隔离性 MariaDB [test]> select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 事务隔离级别描述: READ UNCOMMITTED:幻读,不可重复读和脏读均允许; READ COMMITTED:允许幻读和不可重复读,但不允许脏读; REPEATABLE READ:允许幻读,但不允许不可重复读和脏读; SERIALIZABLE:幻读,不可重复读和脏读都不允许; ORACLE默认的是 READ COMMITTED。 MYSQL默认的是 REPEATABLE READ。
| |
2.对不存在的记录加for update的锁 MariaDB [test]> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> select * from locktestInno where id=3 for update; Empty set (0.00 sec) | 对id为3执行insert操作 MariaDB [test]> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> insert into locktestInno(name,age,address) values('ccc',3,'cc@cc.com'); 等待
|
3.回滚 MariaDB [test]> rollback; Query OK, 0 rows affected (0.00 sec) | 插入成功,可以看到mysql事务默认是运行幻读的,但是通过间隙锁是会阻塞的 MariaDB [test]> insert into locktestInno(name,age,address) values('ccc',3,'cc@cc.com'); Query OK, 1 row affected (39.60 sec) |
什么时候使用表锁,什么时候使用行锁?
对于InnoDB表,在绝大部分情况下都应该使用行级锁,因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中,也可以考虑使用表级锁。
- 第一种情况是:事务需要更新大部分或全部数据,表又比较大,如果使用默认的行锁,不仅这个事务执行效率低,而且可能造成其他事务长时间锁等待和锁冲突,这种情况下可以考虑使用表锁来提高该事务的执行速度。
- 第二种情况是:事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表,从而避免死锁、减少数据库因事务回滚带来的开销。
当然,应用中这两种事务不能太多,否则,就应该考虑使用MyISAM表了。
在InnoDB下,使用表锁要注意以下两点。
(1)使用LOCK TABLES虽然可以给InnoDB加表级锁,但必须说明的是,表锁不是由InnoDB存储引擎层管理的,而是由其上一层──MySQL Server负责的,仅当autocommit=0、innodb_table_locks=1(默认设置)时,InnoDB层才能知道MySQL加的表锁,MySQL Server也才能感知InnoDB加的行锁,这种情况下,InnoDB才能自动识别涉及表级锁的死锁;否则,InnoDB将无法自动检测并处理这种死锁。有关死锁,下一小节还会继续讨论。
(2)在用 LOCK TABLES对InnoDB表加锁时要注意,要将AUTOCOMMIT设为0,否则MySQL不会给表加锁;事务结束前,不要用UNLOCK TABLES释放表锁,因为UNLOCK TABLES会隐含地提交事务;COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁,必须用UNLOCK TABLES释放表锁。正确的方式见如下语句:
例如,如果需要写表t1并从表t读,可以按如下做:
关于死锁
MyISAM表锁是deadlock free的,这是因为MyISAM总是一次获得所需的全部锁,要么全部满足,要么等待,因此不会出现死锁。但在InnoDB中,除单个SQL组成的事务外,锁是逐步获得的,这就决定了在InnoDB中发生死锁是可能的。如下所示的就是一个发生死锁的例子。
session1 | session2 |
1.获取locktestInno的排它锁 MariaDB [test]> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> select * from locktestInno where id=1 for update; +----+------+------+-----------+ | id | name | age | address | +----+------+------+-----------+ | 1 | aaa | 2 | aa@aa.com | +----+------+------+-----------+ 1 row in set (0.12 sec)
| 获取courseInno的排它锁 MariaDB [test]> select * from courseInno where id=1 for update; +----+------+-------+ | id | name | score | +----+------+-------+ | 1 | 1 | 10 | +----+------+-------+
|
2.获取courseInno的排它锁 MariaDB [test]> select * from courseInno where id=1 for update; 等待
在session2发生死锁后,继续执行
MariaDB [test]> select * from courseInno where id=1 for update;
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | 1 | 10 |
+----+------+-------+
| 获取locktestInno 的排它锁,出现死锁 MariaDB [test]> select * from locktestInno where id=1 for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
这个死锁很明显,在步骤2中,session1获取courseInno的排它锁,但是排它锁还在session2中没有释放,进入等待;这是session2又来获取locktestInno的排它锁,而session1之前已经进入阻塞状态了,所以session2出现死锁。这里死锁就是因为资源的相互等待。
发生死锁后,InnoDB一般都能自动检测到,并使一个事务释放锁并回退,另一个事务获得锁,继续完成事务。
但在涉及外部锁,或涉及表锁的情况下,InnoDB并不能完全自动检测到死锁,这需要通过设置锁等待超时参数 innodb_lock_wait_timeout来解决。需要说明的是,这个参数并不是只用来解决死锁问题,在并发访问比较高的情况下,如果大量事务因无法立即获得所需的锁而挂起,会占用大量计算机资源,造成严重性能问题,甚至拖跨数据库。我们通过设置合适的锁等待超时阈值,可以避免这种情况发生。
通常来说,死锁都是应用设计的问题,通过调整业务流程、数据库对象设计、事务大小,以及访问数据库的SQL语句,绝大部分死锁都可以避免。下面就通过实例来介绍几种避免死锁的常用方法。
1) 在应用中,如果不同的程序会并发存取多个表,应尽量约定以相同的顺序来访问表,这样可以大大降低产生死锁的机会。在上一个的例子中,由于两个session访问两个表的顺序不同,发生死锁的机会就非常高!但如果以相同的顺序来访问,死锁就可以避免。
2)在程序以批量方式处理数据的时候,如果事先对数据排序,保证每个线程按固定的顺序来处理记录,也可以大大降低出现死锁的可能。比如下面的例子,处理数据时,没有对条件的字段id进行排序,一会获取1,一会获取3,这样很容易造成死锁;
session1 | session2 |
1.获取ID为1的数据的for update锁 MariaDB [test]> select * from locktestInno where id=1 for update; +----+------+------+-----------+ | id | name | age | address | +----+------+------+-----------+ | 1 | aaa | 2 | aa@aa.com | +----+------+------+-----------+ 1 row in set (0.00 sec)
| 获取id为3的for update 锁 MariaDB [test]> select * from locktestInno where id=3 for update; +----+------+------+-----------+ | id | name | age | address | +----+------+------+-----------+ | 3 | ccc | 3 | cc@cc.com | +----+------+------+-----------+ |
2.获取id为3的 for update锁 MariaDB [test]> select * from locktestInno where id=3 for update; | 获取id为1的for update锁,出现死锁的情况 MariaDB [test]> select * from locktestInno where id=1 for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
(3)在事务中,如果要更新记录,应该直接申请足够级别的锁,即排他锁,而不应先申请共享锁,更新时再申请排他锁,因为当用户申请排他锁时,其他事务可能又已经获得了相同记录的共享锁,从而造成锁冲突,甚至死锁。
(4)在REPEATABLE-READ隔离级别下,如果两个线程同时对相同条件记录用SELECT...FOR UPDATE加排他锁,在没有符合该条件记录情况下,两个线程都会加锁成功。程序发现记录尚不存在,就试图插入一条新记录,如果两个线程都这么做,就会出现死锁。这种情况下,将隔离级别改成READ COMMITTED,就可避免问题,如下所示。
session1 | session2 |
1.对不存在的记录获取for update锁 MariaDB [test]> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec)
MariaDB [test]> select * from locktestInno where id=4 for update; Empty set (0.43 sec) | 也获取id为4的for update锁,能够获取成功 MariaDB [test]> select * from locktestInno where id=4 for update; Empty set (0.00 sec) |
2.执行insert操作 MariaDB [test]> insert into locktestInno(name,age,address)values('dd',4,'dd@dd.com'); 等待
| 执行insert操作,出现死锁 MariaDB [test]> insert into locktestInno(name,age,address)values('dd',4,'dd@dd.com'); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
|
session2死锁退出后,insert成功 MariaDB [test]> insert into locktestInno(name,age,address)values('dd',4,'dd@dd.com'); Query OK, 1 row affected (6.68 sec) |
5)当隔离级别为READ COMMITTED时,如果两个线程都先执行SELECT...FOR UPDATE,判断是否存在符合条件的记录,如果没有,就插入记录。此时,只有一个线程能插入成功,另一个线程会出现锁等待,当第1个线程提交后,第2个线程会因主键重出错,但虽然这个线程出错了,却会获得一个排他锁!这时如果有第3个线程又来申请排他锁,也会出现死锁