mysql数据库锁

一个数据库操作需要半个小时，并且在一个transaction 内，导致其它的操作因为lock 而timeout 。

 

为了解决这个问题，找了DBA 了解了数据锁的知识：

 

1. 两种类型的锁： locks:

     共享的(S) 锁允许一个事务去读一行（tuple ）。

     独占的锁(X) 允许一个事务更新或删除一行。

 

2. 表锁定，页锁定，行锁定？

 

看看mysql 使用的是什么存储引擎，以区分对锁定的支持：SHOW TABLE STATUS FROM database_name 。ok ，是InnoDB ，它实现标准行级锁定。

 

锁的有效期决定于transaction 或者for update 这类语句在何时结束或者roll back 。

 

如果有select * from table for update 此类语句，那么就是表锁定。

如果有select * from table where id between 1 and 10000  for update 此类语句，那么就是页级锁定。

如果有select * from table where id= 1 for update 此类语句，那么就是行级锁定。

 

还有，索引也会影响到锁的多少。

 

锁直到transaction 结束后释放。

 

3. 乐观锁和悲观锁 ：悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。 乐观锁机制往往基于系统中的数据存储逻辑，因此也具备一定的局限性。可以参见Hibernate 的乐观锁与悲观锁  

 

4. 事务隔离级别：InnoDB默认是可重复读的（REPEATABLE READ）。

 

如何解决此问题？

 

思路两种：

a. 优化查询、更新的sql 语句，减少页锁定、表锁定以降低冲突的可能性。

b. 增加transaction 粒度。

 

 

还有，如何解决死锁？

 

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

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

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

·         如果你正使用锁定读，（SELECT ... FOR UPDATE或 ... LOCK IN SHARE MODE），试着用更低的隔离级别，比如READ COMMITTED。

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

 

等等，详见： 解决死锁问题