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 forupdate此类语句，那么就是表锁定。

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

如果有select * from table whereid= 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。

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