Mysql锁和死锁分析

　　在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;