在MySQL中,行级锁并不是直接锁记录,而是锁索引。索引分为主键索引和非主键索引两种,如果一条sql语句操作了主键索引,MySQL就会锁定这条主键索引;如果一条语句操作了非主键索引,MySQL会先锁定该非主键索引,再锁定相关的主键索引。
在UPDATE、DELETE操作时,MySQL不仅锁定WHERE条件扫描过的所有索引记录,而且会锁定相邻的键值(update时的set),即所谓的next-key locking。
案例分析1:
tab_test 结构如下:
id:主键;
state:状态;
time:时间;
索引:idx_1(state,time)
出现死锁的2条sql语句
update tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute) update tab_test set state=1067,time=now () where id in (9921180)
原因分析:
当“update tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute)”执行时,MySQL会使用idx_1索引,因此首先锁定相关的索引记录,因为idx_1是非主键索引,为执行该语句,MySQL还会锁定主键索引。
假设“update tab_test set state=1067,time=now () where id in (9921180)”几乎同时执行时,本语句首先锁定主键索引,由于需要更新state的值,所以还需要锁定idx_1的某些索引记录。
这样第一条语句锁定了idx_1的记录,等待主键索引,而第二条语句则锁定了主键索引记录,而等待idx_1的记录,这样死锁就产生了。
在第一条语句给主键加锁前,第二条语句已经给主键加了锁,所以在高并发的数据操作时,死锁的情况就容易产生
InnoDB 会自动检测一个事务的死锁并回滚一个或多个事务来防止死锁。Innodb会选择代价比较小的事务回滚,此次事务(1)解锁并回滚,语句(2)继续运行直至事务结束。
解决办法
拆分第一条sql,先查出符合条件的主键值,再按照主键更新记录:
select id from tab_test where state=1061 and time < date_sub(now(), INTERVAL 30 minute); update tab_test state=1064,time=now() where id in(......);
案例分析2
teamUser表的表结构如下:
PRIMARY KEY (`uid`,`Id`),
KEY `k_id_titleWeight_score` (`Id`,`titleWeight`,`score`),
ENGINE=InnoDB
出现死锁的2条sql语句
insert into teamUser_20110121 select * from teamUser DELETE FROM teamUser WHERE teamId=$teamId AND titleWeight<32768 AND joinTime<'$daysago_1week'
两语句加锁情况
在innodb默认的事务隔离级别下,普通的SELECT是不需要加行锁的,但LOCK IN SHARE MODE、FOR UPDATE及高串行化级别中的SELECT都要加锁。有一个例外,此案例中,语句(1)insert into teamUser_20110121 select * from teamUser会对表teamUser_20110121(ENGINE= MyISAM)加表锁,并对teamUser表所有行的主键索引(即聚簇索引)加共享锁。默认对其使用主键索引。
而语句(2)DELETE FROM teamUser WHERE teamId=$teamId AND titleWeight<32768 AND joinTime<'$daysago_1week'为删除操作,会对选中行的主键索引加排他锁。由于此语句还使用了非聚簇索引KEY `k_teamid_titleWeight_score` (`teamId`,`titleWeight`,`score`)的前缀索引,于是,还会对相关行的此非聚簇索引加排他锁。
锁冲突的产生
由于共享锁与排他锁是互斥的,当一方拥有了某行记录的排他锁后,另一方就不能其拥有共享锁,同样,一方拥有了其共享锁后,另一方也无法得到其排他锁。所 以,当语句(1)、(2)同时运行时,相当于两个事务会同时申请某相同记录行的锁资源,于是会产生锁冲突。由于两个事务都会申请主键索引,锁冲突只会发生 在主键索引上。
避免死锁的方法
InnoDB给MySQL提供了具有提交,回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎。InnoDB锁定在行级并且也在SELECT语句提供非锁定读。这些特色增加了多用户部署和性能。
但其行锁的机制也带来了产生死锁的风险,这就需要在应用程序设计时避免死锁的发生。以单个SQL语句组成的隐式事务来说,建议的避免死锁的方法如下:
1.如果使用insert…select语句备份表格且数据量较大,在单独的时间点操作,避免与其他sql语句争夺资源,或使用select into outfile加上load data infile代替 insert…select,这样不仅快,而且不会要求锁定
2. 一个锁定记录集的事务,其操作结果集应尽量简短,以免一次占用太多资源,与其他事务处理的记录冲突。
3.更新或者删除表格数据,sql语句的where条件都是主键或都是索引,避免两种情况交叉,造成死锁。对于where子句较复杂的情况,将其单独通过sql得到后,再在更新语句中使用。
4. sql语句的嵌套表格不要太多,能拆分就拆分,避免占有资源同时等待资源,导致与其他事务冲突。
5. 对定点运行脚本的情况,避免在同一时间点运行多个对同一表进行读写的脚本,特别注意加锁且操作数据量比较大的语句。
6.应用程序中增加对死锁的判断,如果事务意外结束,重新运行该事务,减少对功能的影响。
案例分析3
多表操作发生的死锁
session1
session2
session2结果分析
案例分析4
当前具有两个 session 登录到 MySQL 服务器中, 简称 sessionA 与 sessionB。
sessionA 中执行下面操作。
sessionA> show global status like "table_locks%";
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 36 |
| Table_locks_waited | 0 |
+-----------------------+-------+
2 rows in set (0.00 sec)
解释:查询当前 MySQL 中表锁定信息。
sessionA> lock table sbtest.new write;
Query OK, 0 rows affected (0.00 sec)
解释:对测试表 sbtest.new 锁定,该操作只会影响其他会话对 sbtest.new 表执行 DDL 及 DML 操作。
sessionA> show global status like "table_locks%";
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 37 |
| Table_locks_waited | 0 |
+-----------------------+-------+
2 rows in set (0.00 sec)
解释:当执行 lock table 操作之后,系统会对 sbtest.new 表执行一次锁定操作,当完成在表中数据库头部标记锁定资源操作后,释放锁。
在当前 sessionA 执行锁定操作状态下,不影响 sessionA 对表 sbtest.new 进行增删改操作,参考例子。
sessionA> insert into sbtest.new values (4),(5),(6);
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
sessionA> select * from sbtest.new;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
6 rows in set (0.00 sec)
sessionA> delete from sbtest.new where id > 3;
Query OK, 3 rows affected (0.06 sec)
完成上述操作后, 切换到 sessionB 会话中, 执行下面操作。
sessionB> select * from sbtest.new;
解释:当 sessionB 进行表查询时,由于 sessionA 执行锁定操作,导致查询等待,直到锁定结束为止。
利用管理员创建 sessionC 登录到 MySQL, 利用 show processlist 命令显示当前登录到 MySQL 终端的所有状态状态信息。
sessionC> show processlist;
+----+------+---------+------+---------------------------------+--------------------------+
| Id | db | Command | Time | State | Info |
+----+------+---------+------+---------------------------------+--------------------------+
| 1 | NULL | Sleep | 120 | | NULL |
| 2 | NULL | Query | 0 | NULL | show processlist |
| 3 | NULL | Query | 112 | Waiting for table metadata lock | select * from sbtest.new |
+----+------+---------+------+---------------------------------+--------------------------+
3 rows in set (0.00 sec)
要使用 show processlist 必须具有 PROCESS 权限,由于排版关系,返回信息进行部分折断,从 State 状态栏中可以清楚看到, ID 3 的会话当前正在处于查询等待状态, Waiting for table metadata lock 显示当前等待状态信息。
只要 sessionA 对表执行解锁操作,sessionB 就能够重新获得资源,继续之前 SQL 操作。
sessionA> unlock tables;
Query OK, 0 rows affected (0.00 sec)
sessionB> select * from sbtest.new;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (29 min 52.91 sec)
1 show processlist;
SHOW PROCESSLIST显示哪些线程正在运行。您也可以使用mysqladmin processlist语句得到此信息。如果您有SUPER权限,您可以看到所有线程。否则,您只能看到您自己的线程(也就是,与您正在使用的MySQL账户相关的线程)。如果有线程在update或者insert 某个表,此时进程的status为updating 或者 sending data。
如果您得到“too many connections”错误信息,并且想要了解正在发生的情况,本语句是非常有用的。MySQL保留一个额外的连接,让拥有SUPER权限的账户使用,以确保管理员能够随时连接和检查系统(假设您没有把此权限给予所有的用户)。
Status | 含义 |
Checking table | 正在检查数据表(这是自动的)。 |
Closing tables | 正在将表中修改的数据刷新到磁盘中,同时正在关闭已经用完的表。这是一个很快的操作,如果不是这样的话,就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。 |
Connect Out | 复制从服务器正在连接主服务器。 |
Copying to tmp table on disk | 由于临时结果集大于tmp_table_size,正在将临时表从内存存储转为磁盘存储以此节省内存。 |
Creating tmp table | 正在创建临时表以存放部分查询结果。 |
deleting from main table | 服务器正在执行多表删除中的第一部分,刚删除第一个表。 |
deleting from reference tables | 服务器正在执行多表删除中的第二部分,正在删除其他表的记录。 |
Flushing tables | 正在执行FLUSH TABLES,等待其他线程关闭数据表。 |
Killed | 发送了一个kill请求给某线程,那么这个线程将会检查kill标志位,同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位,不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了,那么kill请求会在锁释放时马上生效。 |
Locked | 被其他查询锁住了。 |
Sending data | 正在处理SELECT查询的记录,同时正在把结果发送给客户端。 |
Sorting for group | 正在为GROUP BY做排序。 |
Sorting for order | 正在为ORDER BY做排序。 |
Opening tables | 这个过程应该会很快,除非受到其他因素的干扰。例如,在执ALTER TABLE或LOCK TABLE语句行完以前,数据表无法被其他线程打开。正尝试打开一个表。 |
Removing duplicates | 正在执行一个SELECT DISTINCT方式的查询,但是MySQL无法在前一个阶段优化掉那些重复的记录。因此,MySQL需要再次去掉重复的记录,然后再把结果发送给客户端。 |
Reopen table | 获得了对一个表的锁,但是必须在表结构修改之后才能获得这个锁。已经释放锁,关闭数据表,正尝试重新打开数据表。 |
Repair by sorting | 修复指令正在排序以创建索引。 |
Repair with keycache | 修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。 |
Searching rows for update | 正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。 |
Sleeping | 正在等待客户端发送新请求。 |
System lock | 正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表,那么可以通过增加--skip-external-locking参数来禁止外部系统锁。 |
Upgrading lock | INSERT DELAYED正在尝试取得一个锁表以插入新记录。 |
Updating | 正在搜索匹配的记录,并且修改它们。 |
User Lock | 正在等待GET_LOCK()。 |
Waiting for tables | 该线程得到通知,数据表结构已经被修改了,需要重新打开数据表以取得新的结构。然后,为了能的重新打开数据表,必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知:FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。 |
waiting for handler insert | INSERT DELAYED已经处理完了所有待处理的插入操作,正在等待新的请求。 |
大部分状态对应很快的操作,只要有一个线程保持同一个状态好几秒钟,那么可能是有问题发生了,需要检查一下。还有其他的状态没在上面中列出来,不过它们大部分只是在查看服务器是否有存在错误是才用得着。
2 show full processlist;
show processlist;只列出前100条,如果想全列出请使用show full processlist;