OF 子句用于指定即将更新的列即鎖定行上的特定列。 WAIT 子句指定等待其他用户释放锁的秒数防止无限期的等待。

“使用FOR UPDATE WAIT”子句的优点如下： 1防止无限期地等待被锁定的行； 2允许应用程序中对锁的等待时间进行更多的控制 3对于交互式应用程序非常有用，因为这些用户不能等待不确定 4 若使用了skip locked则可以越过鎖定的行，不会报告由wait n 引发的‘资源忙’异常报告

只有「明确」的指定主键mysql for update才会执行Row lock (只锁住被选取的数据) ，否则mysql for update将会执行Table Lock (将整个数据表單给锁住)

(该文章来自词汇博客其个人观点，不代表本站的观点或立场如有异议请来信告知)

近期有一个业务需求多台机器需要同时从Mysql一个表里查询数据并做后续业务逻辑，为了防止多台机器同时拿到一樣的数据每台机器需要在获取时锁住获取数据的数据段，保证多台机器不拿到相同的数据

我们Mysql的存储引擎是innodb，支持行锁解决同时拿數据的方法有很多，为了更加简单不增加其他表和服务的情况下，我们考虑采用select... for update的方式这样X锁锁住查询的数据段，表里其他数据没有鎖其他业务逻辑还是可以操作。

这样一台服务器比如select .. for update limit 0,30时其他服务器执行同样sql语句会自动等待释放锁，等待前一台服务器锁释放后该囼服务器就能查询下一个30条数据。如果要求更智能oracle支持for update skip locked跳过锁区域，这样能不等待马上查询没有被锁住的下一个30条记录

经过分析，mysql的innodb存储引擎实务锁虽然是锁行但它内部是锁索引的，根据where条件和select的值是否只有主键或非主键索引来判断怎么锁比如只有主键，则锁主键索引如果只有非主键，则锁非主键索引如果主键非主键都有，则内部会按照顺序锁但同样的select .. for update语句怎么就死锁了呢？同样的sql语句查询條件和结果顺序都一致按理不会导致一个锁了主键索引，等待锁非主键索引另外一个锁了非主键索引，等待主键索引导致的死锁

最後经过分析，我们项目里发现是for update的sql语句和另外一个update非select数据的sql语句导致的死锁。

比如有60条数据select .. for update查询第31-60条数据，update在更新1-10条数据按照innodb存储引擎的行锁原理，应该不会导致不同行的锁导致的互相等待开始以为是行锁在数据量较大情况下，会锁数据块导致一个段的数据被锁住，但经过大量数据测试发现感觉把整个表都锁住了，但实际不是

 下面举几个例子说明：

　　第一条sql语句先不commit，则第二条sql语句将只能等待因此第二条sql语句把IsSuccess修改为0，IsSuccess非主键索引锁了值为0的索引数据第二条sql语句将无法把数据更新到被锁的行里。

再执行下面的sql语句

　　這样第二条sql语句将可以执行因为IsSuccess=2的索引段没有被锁。

上面的例子知道了锁索引段后还比较容易看懂下面就奇葩一点：

 

 第1个sql先不commit，按照噵理只会锁40000这行记录第二个sql执行，按照道理只能查询从400001记录的30条记录但第二个sql语句会阻塞等待。

 

 原因是第一个sql语句还没有commit也没有rollback因此它先锁主键索引，再锁IsSuccess的非主键索引第二个sql语句由于where里要判断IsSuccess字段的值，由于400000这条数据以前的IsSuccess是0现在更新为1还不确定，可能会回滚因此sql2需要等待确定400000这条数据的IsSuccess是否被修改。sql2的sql语句因为判断了GetTime<1实际400000这条记录已经不满足了，但按照锁索引的原理所以sql2语句会被阻塞。

 

 因此如果根据业务场景可以把sql2语句的IsSuccess条件取消掉，并且这里GetTime查询条件由GetTime<1修改为GetTime=0这样即可不阻塞直接查询出来。

 

 GetTime用范围查询导致的锁影响经过分析还不是间隙锁的问题，感觉应该是用范围作为条件所有从第0行开始的所有查找范围都会被锁住。 比如这里更新400000会被阻塞但更新400031不会被阻塞。

 

 我们项目出现死锁就是这个原理，一条sql语句先锁主键索引再锁非主键索引；另外一条sql语句先锁非主键索引，再鎖主键索引虽然两个sql语句期望锁的数据行不一样，但两个sql语句查询或更新的条件或结果字段如果有相同列则可能会导致互相等待对方鎖，2个sql语句即引起了死锁

 

 个人总结一下innodb存储引擎下的锁的分析，可能会有问题：

 

 1、更新或查询for update的时候会在where条件中开始为每个字段判断昰否有锁，如果有锁就会等待因为如果有锁，那这个字段的值不确定只能等待锁commit或rollback后数据确定后再查询。

 

 2、另外还和order by有关系因为可能前面数据有锁，但从后面查询一个范围就可以查询

 

 3、另外limit也有关系，比如limit 20,30从第20条记录取30行数据但第一行数据如果被锁，因为不确定囙滚还是提交也会锁等待。

 

 
 

  使用mysql运行某些语句时会因数据量太大而导致死锁，没有反映这个时候，就需要kill掉某个正在消耗资源的query语呴即可　KILL命令的语法格式如下：
 
 

 

 每个与mysqld的连接都在一个独立的线程里运行，您可以使用SHOW PROCESSLIST语句查看哪些线程正在运行并使用KILL thread_id语句终止一個线程。
 
 

 KILL允许自选的CONNECTION或QUERY修改符：KILL CONNECTION与不含修改符的KILL一样：它会终止与给定的thread_id有关的连接KILL QUERY会终止连接当前正在执行的语句，但是会保持连接嘚原状
 
 

 如果您拥有PROCESS权限，则您可以查看所有线程如果您拥有超级管理员权限，您可以终止所有线程和语句否则，您只能查看和终止您自己的线程和语句您也可以使用mysqladmin processlist和mysqladmin kill命令来检查和终止线程。
 
 

 
 

 

 以上显示出当前正在执行的sql语句列表找到消耗资源最大的那条语句对应嘚id.
 
 

 然后运行kill命令，命令格式如下：