mysql关于死锁的优化_mysql死锁探究及优化

什么是锁？

锁是计算机为了平衡协调多个进程或线程并发访问资源的机制。计算机除了cpu，ram，I/O，数据也是一种共享资源。锁冲突是影响数据库并发性能的一个重要因素。

例如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。这些锁统称为悲观锁(Pessimistic Lock)。

mysql数据库根据不同的存储引擎支持不同的锁机制。

MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking)

BDB存储引擎采用的是页面锁(page-level locking)， 也支持表级锁。(已被innodb取代)

InnoDB存储引擎既支持行级锁(row-level locking)，也支持表级锁，但默认情况下是采用行级锁。

表级锁：开销小，不会死锁，锁定力度大，发生锁冲突的概率高，并发度低。(适合以查询为主，少量按索引更新数据的应用，web界面等)

页面锁：开销中，会死锁，锁定力度中，发生锁冲突的概率中，并发度中。

行级锁：开销大，会死锁，锁定力度小，发生锁冲突的概率小，并发度高。(适合通过大量的索引并发更新不同的数据，同时还有并发查询的应用)

MyISAM表

读操作：不会阻塞其他用户对同一表的读请求，会阻塞写请求。

写操作：会阻塞其他用户对同一表的读和写请求。(读写互斥，写操作的优先级最高，即使读操作先到。所以不适宜读写并存的情况)

MyISAM(不支持事务)总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁(Deadlock Free)的原因。

当使用LOCK TABLES时，不仅是一次就可锁定所有用到的表，当需要用到别名查询时，需要分别对表的别名分别锁定。

支持并发插入(通过设置 0、1或2值)

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：

Variable_name | Value

Table_locks_immediate | 2979

Table_locks_waited | 0

2 rows in set (0.00 sec))

如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。

InnoDB表

和MyISAM表不同，它支持事务，而且是行级锁。

事务具有ACID四个特性：

原子性( Atomicity)：操作是原子操作，要么全部执行，要么全部不执行。

一致性( Consistent)：在事务开始和完成时，数据必须保持一致完整的性质。

隔离性( Isolation)：数据库会提供一些隔离的机制，保证执行过程不受外界并发的干扰。同时中间的处理过程对外部是不可见的。

持久性( Durable)：事务完成，对数据的修改是永久的，即使出现故障也能保持住。

Innodb目前处理死锁的方法是将持有最少行级排他锁的事务进行回滚。

Innodb支持行锁和表锁，行锁是给索引上的索引项加锁实现的。

Oracle是通过在数据块中对相应数据行加锁来实现的。

Innodb这种加锁方式意味着：

只有通过索引条件检索数据，Innodb才能使用行级锁。否则使用表锁，会把所有扫描过的行都锁定。

使用的时候注意这点，避免大量的锁冲突。影响并发性能。

innodb 默认是自动提交。

怎样有效降低 innodb 死锁的几率：

用比较低的隔离级别( RC(read committed)级别)。

精心设计索引，并尽量通过索引访问数据，使得锁更加精确，减少锁冲突

选择合理的事务大小(小事物发生锁冲突的几率更低)

不同的程序去访问一组表，尽量约定相同的顺序访问各表。存储也一样。这样可以大大减少死锁几率。