MySQL——

摘要

主要是的分析介绍锁的相关的原理和锁的实战SQL语句。

什么是锁

锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访问。InnoDB存储引擎会在行级别上对表数据上锁，这固然不错。不过InnoDB存储引擎也会在数据库内部其他多个地方使用锁，从而允许对多种不同资源提供并发访问。例如，操作缓冲池中的LRU列表，删除、添加、移动LRU列表中的元素，为了保证一致性，必须有锁的介入。数据库系统使用锁是为了支持对共享资源进行并发访问,提供数据的完整性和一致性。人们认为行级锁总会增加开销。实际上，只有当实现本身会增加开销时，行级锁才会增加开销。（这个怎么理解）InnoDB存储引擎不需要锁升级，因为一个锁和多个锁的开销是相同的。对于MyISAM引擎，其锁是表锁设计。并发情况下的读没有问题，但是并发插入时的性能就要差一些了，若插入是在“底部”，MyISAM存储引擎还是可以有一定的并发写入操作的。InnoDB存储引擎锁的实现和Oracle数据库非常类似，提供一致性的非锁定读、行级锁支持。行级锁没有相关额外的开销，并可以同时得到并发性和--致性。

lock 与latch

latch一般称为门锁（轻量级的锁)，因为其要求锁定的时间必须非常短。若持续的时间长，则应用的性能会非常差。在InnoDB存储引擎中，latch 又可以分为mutex(互斥量〉和rwlock（读写锁)。其目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。

lock的对象是事务，用来锁定的是数据库中的对象，如表、页、行。并且一般lock的对象仅在事务commit或rollback后进行释放（不同事务隔离级别释放的时间可能不同)。lock，正如在大多数数据库中一样，是有死锁机制的。表6-1显示了lock 与latch的不同。

对于InnoDB存储引擎中的 latch，可以通过命令SHOW ENGINE INNODB MUTEX来进行查看，如图6-1所示。(show engine innodb mutex)

相对于latch 的查看，lock信息就显得直观多了。用户可以通过命令SHOW ENGINEINNODB STATUS及information_schema架构下的表INNODB_TRX、INNODB_LOCKs.INNODB_LOCK_WAITS来观察锁的信息。这将在下节中进行详细的介绍。

lnnoDB存储引擎中的锁

都是行锁：共享锁（S Lock)，允许事务读一行数据。（s锁）

都是行锁：排他锁（X Lock)，允许事务删除或更新一行数据。(x锁)        

如果一个事务T1已经获得了行r的共享锁，那么另外的事务T2可以立即获得行r的共享锁，因为读取并没有改变行r的数据，称这种情况为锁兼容（Lock Compatible)。但若有其他的事务T3想获得行r的排他锁，则其必须等待事务T1、T2释放行r上的共享锁——这种情况称为锁不兼容。表6-3显示了共享锁和排他锁的兼容性。（就是排他锁不允许有其他锁 但是共享锁可以有共享其他事务中。）从表6-3可以发现X锁与任何的锁都不兼容，而S锁仅和S锁兼容。需要特别注意的是，S和X锁都是行锁，兼容是指对同一记录（row）锁的兼容性情况。    

此外，InnoDB存储引擎支持多粒度( granular）锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持一种额外的锁方式，称之为意向锁(Intention Lock)。意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度（fine granularity)上进行加锁，如图6-3所示。若将上锁的对象看成一棵树，那么对最下层的对象上锁，也就是对最细粒度的对象进行上锁，那么首先需要对粗粒度的对象上锁。例如图6-3，如果需要对页上的记录r进行上X锁，那么分别需要对数据库A、表、页上意向锁IX，最后对记录R上X锁。若其中任何一个部分导致等待，那么该操作需要等待粗粒度锁的完成。举例来说，在对记录r加锁之前，已经有事务对表1进行了S表锁，那么表1上已存在S锁，之后事务需要对记录r在表1上加上IX，由于不兼容，所以该事务需要等待表锁操作的完成。

InnoDB存储引擎支持意向锁设计比较简练，其意向锁即为表级别的锁。设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型。其支持两种意向锁:

• 1）意向共享锁（IS Lock)，事务想要获得一张表中某几行的共享锁
• 2）意向排他锁（IX Lock)，事务想要获得一张表中某几行的排他锁

由于InnoDB存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫以外的任何请求。故表级意向锁与行级锁的兼容性如表6-4所示。意向锁之间是相互兼容的是可以同时加锁的。。那是因为的数据库中的意向锁是表锁。而x s 是属于行锁

在InnoDB 1.0版本之前，用户只能通过命令SHOW FULL PROCESSLIST(show full processlist),SHOW ENGINE INNODB STATUS等来查看当前数据库中锁的请求，然后再判断事务锁的情况。从InnoDB1.0开始，在 INFORMATION_SCHEMA架构下添加了表 INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS。通过这三张表，用户可以更简单地监控当前事务并分析可能存在的锁问题。我们将通过具体的示例来分析这三张表，在之前，首先了来看表6-5中表INNODB_TRX的定义，其由8个字段组成。

MyISAM中执行select和update，insert，delete操作时，系统会自动给表加上相应的锁。lock table时，需要一次锁定sql中用到的所有表，而且如果sql使用了表别名，则需要对别名也都加锁，如：select a.first_name,b.last_name from user a,user b where a.id=b.id;需要如下的锁定方式:lock table user as a read,user as b read;

一致性非锁定读

（就是SQL后面啥都没有的写的就是这样的原理）

一致性的非锁定读(consistent nonlocking read）是指InnoDB存储引擎通过行多版本控制(multi versioning)的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB存储引擎会去读取行的一个快照数据。

图6-4直观地展现了InnoDB存储引擎-致性的非锁定读。之所以称其为非锁定读，因为不需要等待访问的行上X （排他锁 ）锁的释放。快照数据是指该行的之前版本的数据，该实现是通过undo段来完成。而undo用来在事务中回滚数据，因此快照数据本身是没有额外的开销。此外，读取快照数据是不需要上锁的，因为没有事务需要对历史的数据进行修改操作。可以看到，非锁定读机制极大地提高了数据库的并发性。在 InnoDB存储引擎的默认设置下，这是默认的读取方式，即读取不会占用和等待表上的锁。但是在不同事务隔离级别下，读取的方式不同，并不是在每个事务隔离级别下都是采用非锁定的一致性读。此外，即使都是使用非锁定的一致性读，但是对于快照数据的定义也各不相同。通过图6-4可以知道，快照数据其实就是当前行数据之前的历史版本，每行记录可能有多个版本。就图6-4所显示的，一个行记录可能有不止一个快照数据，一般称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制（Multi VersionConcurrency Control，MVCC)。

在事务隔离级别READ COMMITTED和REPEATABLE READ (InnoDB存储引擎的默认事务隔离级别 读已提交）下，InnoDB存储引擎使用非锁定的一致性读。然而，对于快照数据的定义却不相同。

• 在 READ COMMITTED（读已提交事务）事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。
• 而在REPEATABLE READ（可以重复读的）事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。最开始数据的

会话A中已通过显式地执行命令BEGIN开启了一个事务，并读取了表parent 中id为1的数据，但是事务并没有结束。与此同时，用户再开启另一个会话B，这样可以模拟并发的情况，然后对会话B做如下的操作:

 在会话B中将事务表parent中id为1的记录修改为id=3，但是事务同样没有提交，这样id=1的行其实加了一个X锁。这时如果在会话A中再次读取id为1的记录，根据InnoDB存储引擎的特性，即在READ COMMITTED和REPEATETABLE READ的事务隔离级别下会使用非锁定的一致性读。回到之前的会话A，接着上次未提交的事务，执行SQL语句SELECT*FROM parent WHERE id=1的操作，这时不管使用READCOMMITTED还是REPEATABLE READ的事务隔离级别，显示的数据应该都是:

在会话B提交事务后，这时在会话A中再运行SELECT *FROM parent WHEREid=1的SQL语句，在READ COMMITTED和REPEATABLE事务隔离级别下得到结果就不一样了。对于READ COMMITTED的事务隔离级别，它总是读取行的最新版本，如果行被锁定了，则读取该行版本的最新一个快照(fresh snapshot)。在上述例子中，因为会话B已经提交了事务，所以READ COMMITTED事务隔离级别下会得到如下结果:

一致性锁定读

（表示就是在SQL语句后面添加相关的锁集表示的一致性锁定读）

在前一小节中讲到，在默认配置下，即事务的隔离级别为REPEATABLE READ模式下，InnoDB存储引擎的SELECT操作使用一致性非锁定读。但是在某些情况下，用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性。而这要求数据库支

SELECT…FOR UPDATE（select ****  for update ） SELECT…LOCK IN SHARE MODE(select …… lock in share mode)

SELECT…FOR UPDATE对读取的行记录加一个X锁，其他事务不能对已锁定的行加上任何锁（添加了的S锁）。

SELECT…LOCK IN SHARE MODE对读取的行记录加一个S锁，其他事务可以向被锁定的行加S锁，但是如果加×锁，则会被阻塞。

插人操作会依据这个自增长的计数器值加1赋予自增长列。这个实现方式称做AUTO-INC Locking。这种锁其实是采用-种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放。虽然AUTO-INC Locking 从一定程度上提高了并发插入的效率，但还是存在一-些性能上的问题。首先，对于有自增长值的列的并发插入性能较差，事务必须等待前一个插入的完成（虽然不用等待事务的完成)。其次，对于INSERT…SELECT的大数据量的插人会影响插入的性能，因为另一个事务中的插入会被阻塞。InnoDB存储引擎中提供了一种轻量级互斥量的自增长实现机制，这种机制大大提高了自增长值插入的性能。并且从该版本开始，InnoDB存储引擎提供了一个参数innodb_autoinc_lock_mode来控制自增长的模式，该参数的默认值1

此外，还需要特别注意的是InnoDB存储引擎中自增长的实现和MyISAM不同,MyISAM存储引擎是表锁设计，自增长不用考虑并发插入的问题。因此在master 上用InnoDB存储引擎，在slave上用 MyISAM存储引擎的replication架构下，用户必须考虑这种情况。

另外，在InnoDB存储引擎中，自增长值的列必须是索引，同时必须是索引的第专个列。如果不是第一个列，则 MySQL数据库会抛出异常，而MyISAM存储引擎没有这个问题。

外键和锁

前面已经介绍了外键,，外键主要用于引用完整性的约束检查。在InnoDB存储引擎中，对于一个外键列，如果没有显式地对这个列加索引，InnoDB存储引擎自动对其加一个索引，因为这样可以避免表锁——这比Oracle数据库做得好，Oracle数据库不会自动添加索引，用户必须自己手动添加，这也导致了Oracle数据库中可能产生死锁。对于外键值的插入或更新，首先需要查询父表中的记录，即SELECT父表。但是对于父表的SELECT操作，不是使用一致性非锁定读的方式，因为这样会发生数据不一致的问题，因此这时使用的是SELECT…LOCK IN SHARE MODE 方式，即主动对父表加一个S锁。如果这时父表上已经这样加锁，子表上的操作会被阻塞。

锁的算法

InnoDB存储引擎有3种行锁的算法，其分别是:

• Record Lock:单个行记录上的锁
• Gap Lock:间隙锁，锁定一个范围，但不包含记录本身
• Next-Key Lock : Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身

Record Lock总是会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。

Next-Key Lock是结合了Gap Lock和 Record Lock的一种锁定算法，在Next-KeyLock算法下，InnoDB对于行的查询都是采用这种锁定算法。所以在避免了幻读的问题的。保证了数据的一致性。

解决Phantom Problem

在默认的事务隔离级别下，即 REPEATABLE READ 下，InnoDB存储引擎采用Next-Key Locking 机制来避免Phantom Problem(幻像问题)。这点可能不同于与其他的数据库，如Oracle数据库，因为其可能需要在SERIALIZABLE的事务隔离级别下才能解决Phantom Problem。

Phantom Problem是指在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行。下面将演示这个例子，使用前一小节所创建的表t。表t由1、2、5这三个值组成，若这时事务T1执行如下的SQL语句:

SELECT *FROMt WHERE a> 2FOR UPDATE;

注意这时事务T1并没有进行提交操作，上述应该返回5这个结果。若与此同时，另一个事务T2插入了4这个值，并且数据库允许该操作，那么事务T1再次执行上述SQL语句会得到结果4和5。这与第一次得到的结果不同，违反了事务的隔离性，即当前事务能够看到其他事务的结果。其过程如表6-13所示。

InnoDB存储引擎采用Next-Key Locking 的算法避免Phantom Problem。对于上述的SQL语句SELECT *FROM t WHERE a>2 FOR UPDATE，其锁住的不是5这单个值，而是对（2，+ o）这个范围加了X锁。因此任何对于这个范围的插入都是不被允许的，从而避免Phantom Problem。InnoDB存储引擎默认的事务隔离级别是REPEATABLE READ，在该隔离级别下，其采用Next-Key Locking的方式来加锁。而在事务隔离级别READ COMMITTED 下，其仅采用Record Lock，因此在上述的示例中，会话A需要将事务的隔离级别设置为READ COMMITTED。

数据库的死锁

死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。若无外力作用，事务都将无法推进下去。解决死锁问题最简单的方式是不要有等待，将任何的等待都转化为回滚，并且事务重新开始。毫无疑问，这的确可以避免死锁问题的产生。然而在线上环境中，这可能导致并发性能的下降，甚至任何一个事务都不能进行。而这所带来的问题远比死锁问题更为严重，因为这很难被发现并且浪费资源。

解决死锁问题最简单的一种方法是超时，即当两个事务互相等待时，当一个等待时间超过设置的某一阈值时，其中一个事务进行回滚，另一个等待的事务就能继续进行。在 InnoDB存储引擎中，参数innodb_lock_wait_timeout用来设置超时的时间。

超时机制虽然简单，但是其仅通过超时后对事务进行回滚的方式来处理，或者说其是根据FIFO的顺序选择回滚对象。但若超时的事务所占权重比较大，如事务操作更新了很多行，占用了较多的undo log，这时采用FIFO的方式，就显得不合适了，因为回滚这个事务的时间相对另一个事务所占用的时间可能会很多。因此，除了超时机制，当前数据库还都普遍采用wait-for graph(等待图）的方式来进行死锁检测。较之超时的解决方案，这是一种更为主动的死锁检测方式。InnoDB存储引擎也采用的这种方式。wait-for graph要求数据库保存以下两种信息:

• 锁的信息链表
• 事务等待链表

通过图6-6可以发现存在回路(（t1，t2)，因此存在死锁。通过上述的介绍，可以发现wait-for graph是一种较为主动的死锁检测机制，在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁通常来说InnoDB存储引擎选择回滚undo量最小的事务。

wait-for graph 的死锁检测通常采用深度优先的算法实现，在InnoDB1.2版本之前，都是采用递归方式实现。而从1.2版本开始，对wait-for graph 的死锁检测进行了优化，将递归用非递归的方式实现，从而进一步提高了InnoDB存储引擎的性能。

锁升级（只有得Microsoft SQL InnoDB是不存在锁升级的）

锁升级（Lock Escalation）是指将当前锁的粒度降低。举例来说，数据库可以把一个表的1000个行锁升级为一个页锁，或者将页锁升级为表锁。如果在数据库的设计中认为锁是一种稀有资源，而且想避免锁的开销，那数据库中会频繁出现锁升级现象。Microsoft sQL Server数据库的设计认为锁是一种稀有的资源，在适合的时候会自动地将行、键或分页锁升级为更粗粒度的表级锁。这种升级保护了系统资源，防止系统使用太多的内存来维护锁，在一定程度上提高了效率。即使在Microsoft sQL Server 2005版本之后，SQL Server数据库支持了行锁，但是其设计和InnoDB存储引擎完全不同，在以下情况下依然可能发生锁升级:

• 由一句单独的SQL语句在一个对象上持有的锁的数量超过了阈值，默认这个阈值为5000。值得注意的是，如果是不同对象，则不会发生锁升级
• 锁资源占用的内存超过了激活内存的40%时就会发生锁升级

在Microsoft sQL Server数据库中，由于锁是一种稀有的资源，因此锁升级会带来一定的效率提高。但是锁升级带来的一个问题却是因为锁粒度的降低而导致并发性能的降低。

InnoDB存储引擎不存在锁升级的问题。因为其不是根据每个记录来产生行锁的，相反，其根据每个事务访问的每个页对锁进行管理的，采用的是位图的方式。因此不管一个事务锁住页中一个记录还是多个记录，其开销通常都是一致的。

数据库实战： MySQL中使用表锁的SQL语句如下：

lock table table_name read；共享读锁，会阻塞写

lock table table_name write；独占写锁，会阻塞读

unlock  tables；释放锁

mysql中共享锁与排他锁的SQL语句：

select .....in share mode;共享锁

select .....for update;排他锁

数据库锁面试问题

MySQL大致可归纳为以下3种锁：表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

MySQL（MyISAM引擎下）表级锁的锁模式

MySQL表级锁有两种模式：表共享锁（Table Read Lock）和表独占写锁（Table Write Lock）。这个是在INNODB中是一种的意向锁的形式。 对MyISAM的读操作，不会阻塞其他用户对同一表请求，但会阻塞对同一表的写请求； 对MyISAM的写操作，则会阻塞其他用户对同一表的读和写操作； MyISAM表的读操作和写操作之间，以及写操作之间是串行的。 当一个线程获得对一个表的写锁后，只有持有锁线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

MySQL表级锁的锁模式

ＭySQL的表锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

MyISAM如何加表锁

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。显式加锁基本上都是为了方便而已，并非必须如此。给MyISAM表显示加锁，一般是为了一定程度模拟事务操作，实现对某一时间点多个表的一致性读取。例如，有一个订单表orders，其中记录有订单的总金额total，同时还有一个订单明细表order_detail，其中记录有订单每一产品的金额小计subtotal，假设我们需要检查这两个表的金额合计是否相等，可能就需要执行如下两条SQL：

SELECT SUM(total) FROM orders;
SELECT SUM(subtotal) FROM order_detail;

这时，如果不先给这两个表加锁，就可能产生错误的结果，因为第一条语句执行过程中，order_detail表可能已经发生了改变。因此，正确的方法应该是：

LOCK tables orders read local,order_detail read local;
SELECT SUM(total) FROM orders;
SELECT SUM(subtotal) FROM order_detail;
Unlock tables;

MyISAM下的并发锁

在一定条件下，MyISAM也支持查询和操作的并发进行。 MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。 当concurrent_insert设置为0时，不允许并发插入。 当concurrent_insert设置为1时，如果MyISAM允许在一个读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾插入记录，都允许在表尾并发插入记录。 可以利用MyISAM存储引擎的并发插入特性，来解决应用中对同一表查询和插入锁争用。例如，将concurrent_insert系统变量为2，总是允许并发插入；通过定期在系统空闲时段执行OPTIONMIZE TABLE语句来整理空间碎片，收到因删除记录而产生的中间空洞。

MyISAM的锁调度

MyISAM存储引擎的读和写锁是互斥，读操作是串行的。那么，一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？

答案是写进程先获得锁。不仅如此，即使读进程先请求先到锁等待队列，写请求后到，写锁也会插到读请求之前！这是因为MySQL认为写请求一般比读请求重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节MyISAM的调度行为。通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。虽然上面3种方法都是要么更新优先，要么查询优先的方法，但还是可以用其来解决查询相对重要的应用（如用户登录系统）中，读锁等待严重的问题。另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL变暂时将写请求的优先级降低，给读进程一定获得锁的机会。 上面已经讨论了写优先调度机制和解决办法。这里还要强调一点：一些需要长时间运行的查询操作，也会使写进程“饿死”！因此，应用中应尽量避免出现长时间运行的查询操作，不要总想用一条SELECT语句来解决问题。因为这种看似巧妙的SQL语句，往往比较复杂，执行时间较长，在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”，使每一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行。

MYSQL(InnoDB引擎下)什么时候使用表锁

对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个另特殊事务中，也可以考虑使用表级锁。

• 第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
• 第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。当然，应用中这两种事务不能太多，否则，就应该考虑使用ＭyISAＭ表。

在InnoDB下 ，使用表锁要注意以下两点。

• （１）使用LOCK TALBES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层ＭySQL Server负责的，仅当autocommit=0、innodb_table_lock=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，ＭySQL Server才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。
• （２）在用LOCAK TABLES对InnoDB锁时要注意，要将AUTOCOMMIT设为0，否则ＭySQL不会给表加锁；事务结束前，不要用UNLOCAK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK产不能释放用LOCAK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁，正确的方式见如下语句。

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

对于ＭyISAM的表锁，主要有以下几点

• （１）共享读锁（S）之间是兼容的，但共享读锁（S）和排他写锁（X）之间，以及排他写锁之间（X）是互斥的，也就是说读和写是串行的。
• （２）在一定条件下，ＭyISAM允许查询和插入并发执行，我们可以利用这一点来解决应用中对同一表和插入的锁争用问题。
• （３）ＭyISAM默认的锁调度机制是写优先，这并不一定适合所有应用，用户可以通过设置LOW_PRIPORITY_UPDATES参数，或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用。
• （４）由于表锁的锁定粒度大，读写之间又是串行的，因此，如果更新操作较多，ＭyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突。

对于InnoDB表，主要有以下几点

• （１）InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁。
• （２）InnoDB间隙锁机制，以及InnoDB使用间隙锁的原因。
• （３）在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。
• （４）ＭySQL的恢复和复制对InnoDB锁机制和一致性读策略也有较大影响。
• （５）锁冲突甚至死锁很难完全避免。

在了解InnoDB的锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁，包括：

• 尽量使用较低的隔离级别
• 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会。
• 选择合理的事务大小，小事务发生锁冲突的几率也更小。
• 给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁。
• 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大减少死锁的机会。
• 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响。
• 不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁。
• 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。

常见的解决死锁的方法

1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

如果业务处理不好可以用分布式事务锁或者使用乐观锁

数据库的乐观锁和悲观锁是什么？怎么实现的？

• 数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。
• 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制
• 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过Version的方式来进行锁定。实现方式：一般会使用版本号机制或CAS算法实现。

两种锁的使用场景

• 从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。
• 但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行Retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

并发事务带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况。更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题——最后的更新覆盖了其他事务所做的更新。例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改保存其更改副本的编辑人员覆盖另一个编辑人员所做的修改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”。不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了！这种现象叫做“不可重复读”。幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”

事务４种隔离级别比较

隔离级别/读数据一致性及允许的并发副作用 读数据一致性 脏读 不可重复读 幻读 未提交读（Read uncommitted） 最低级别，只能保证不读取物理上损坏的数据 是 是 是 已提交度（Read committed） 语句级 否 是 是 可重复读（Repeatable read） 事务级 否 否 是 可序列化（Serializable） 最高级别，事务级 否 否 否 最后要说明的是：各具体数据库并不一定完全实现了上述４个隔离级别，例如，Oracle只提供Read committed和Serializable两个标准级别，另外还自己定义的Read only隔离级别：SQL Server除支持上述ISO/ANSI SQL92定义的４个级别外，还支持一个叫做＂快照＂的隔离级别，但严格来说它是一个用MVCC实现的Serializable隔离级别。ＭySQL支持全部４个隔离级别，但在具体实现时，有一些特点，比如在一些隔离级下是采用MVCC一致性读，但某些情况又不是。