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;
