MySQL数据库锁问题:unique-key、select for update等等

MySQL数据库操作的时候经常会出现死锁问题,而且锁的类型有好多。

从mysql的insert 加锁的源码可以看出,insert 插入的时候是用的是LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION (这就是插入意向锁)去检查插入的gap,这个锁模式是与LOCK_S | LOCK_GAP,LOCK_X | LOCK_GAP锁模式冲突的,但对于相同的gap,两个锁模式为LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION,是兼容的。

参考:Insert into 加锁机制  http://blog.csdn.net/and1kaney/article/details/51214001

我本人在开发过程中有遇到了一下锁的问题:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1560007910-oLLel0jx8tTSUwlHEzsfKVsaUJqM' for key 'phone_2'

在以上链接是第四种场景。

一、表的记录锁【共享锁(Share Locks,即S锁)、乐观锁(Version)和排它锁(Exclusive Locks,即X锁)、悲观锁】

两个请求同时更新一条记录的情况会加上事务锁,先执行一条更新,另一条更新进行等待,等上一条执行完后再更新记录;在时间一致的情况是有可能出现死锁的,亲身经历过的,两个锁进行了互斥就出现了死锁,处理最直接方法是回滚事务;。

如果UpdateA是根据主键来更新记录,而且UpdateB是根据非主键来更新记录的情况。一个是根据主键更新记录,此时会针对更新记录的主键加锁,一个是根据非主键更新记录,此时会针对扫描的所有行的主键加锁。

如何处理死锁
死锁是事务型数据库典型的问题,但是除非它们频繁出现以至于你更本不能运行某个事务,它们一般是不危险的。正常地,你必须编写你的应用程序使得它们总是准备如果因为死锁而 回滚一个事务就重新发出一个事务。

InnoDB使用自动行级锁定。即使在只插入或删除单个行的事务的情况下,你可以遇到死锁。这是因为这些操作不是真正的“极小的”,它们自动对插入或删除的行的(可能是数个)索引记录设置锁定。

你可以用下列技术对付死锁减少它们发生的可能性:

用Use SHOW INNODB STATUS来确定最后一个死锁的原因。这样可以帮助你调节应用程序来避免死锁。

总是准备着重新发出事务,如果它因为死锁而失败了。死锁不危险,再试一次。

经常提交你的事务。小事务更少地倾向于冲突。

如果你正使用锁定读,(SELECT … FOR UPDATE或 … LOCK IN SHARE MODE),试着用更低的隔离级别,比如READ COMMITTED。

以固定的顺序访问你的表和行。则事务形成良好定义的查询并且没有死锁。

添加精心选定的索引到你的表。则你的查询需要扫描更少的索引记录并且因此设置更少的锁定。使用EXPLAIN SELECT来确定对于你的查询,MySQL认为哪个索引是最适当的。

使用更少的锁定。如果你可以接受允许一个SELECT从一个旧的快照返回数据,不要给它添加FOR UPDATE或LOCK IN SHARE MODE子句。这里使用READ COMMITTED隔离级别是比较好的,因为每个在同一事务里的持续读从它自己新鲜的快照里读取。

如果没有别的有帮助的了,用表级锁定系列化你的事务。用LOCK TABLES对事务型表(如InnoDB)的正确方法是设置AUTOCOMMIT = 0 并且不调用UNLOCK TABLES直到你明确地提交了事务。例如,如果你需要写表t1并从表t读,你可以按如下做:

SET AUTOCOMMIT=0;
 
LOCK TABLES t1 WRITE, t2 READ, ...;
 
[do something with tables t1 and t2 here];
 
COMMIT;
 
UNLOCK TABLES;

表级锁定使得你的事务很好地排队,并且死锁被避免了。

领一个系列化事务的方法是创建一个辅助的“semaphore” 表,它只包含一个单行。让每个事务在访问其它表之前更新那个行。以这种方式,所有事务以序列的方式发生。注意,InnoDB即时死锁检测算法也能在这种情况下起租用,因为系列化锁定是行级锁定。超时方法,用MySQL表级锁定,必须被用来解决死锁。

在应用程序中使用LOCK TABLES命令,如果AUTOCOMMIT=1,MySQL不设定InnoDB表锁定。

※如果做了以上的事务回滚机制处理还是出现了记录更新死锁状态,那就必须使用Redis或者Memcache缓存机制,队列业务处理。

二、涉及数字的数据库字段更新有两种情况:

1.累加、累减更新;

  遇到并发修改同一条记录数据的情况,数据就会混乱,两条同时请求的时候,数据可能更新为后面一条的数据,
  所以要使用:update table set field = field + ${num}  方式更新。

2.直接替换
  
  update table set field = ${num}

三、涉及到下单或者扣除金额的情况

※一定要先判断原来DB的账户余额或者指定的信息金额是否足够,足够才可以扣除(减少)。

mysql数据库死锁的产生原因及解决办法

来源:https://www.cnblogs.com/sivkun/p/7518540.html