MySQL中的锁机制（表锁、行锁）

Mysql锁机制比较简单，但其最显著的特点是不同的存储引擎支持不同的所机制。
大致上可分为以下3种锁：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

表级锁（MyISAM）

Mysql表级锁有两种模式：表共享锁（Table Read Lock）和表独占写锁（Table Write Lock）

对MyISAM的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求
对MyISAM的写操作，则会阻塞其他用户对同一表的读和写操作

MyISAM表的读和写之间、写与写之间是串行的，读与读之间是并行的，即读写、写写互斥；当一个线程获得对一个表的写锁后，只有持有锁线程可以对表进行更新操作。其他线程的读、写操作都会等待直到锁被释放为止。

加锁过程

MyISAM在执行查询语句（select）前，会自动给涉及的所有表加读锁，在执行更新操作（update、delete、insert）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要直接使用LOCK TABLE命令给MyISAM表显示加锁。
下面对MyISAM显示加锁的命令进行讲解

1	LOCK tables table_name read local

local选项，其作用就是在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾插入记录
在用LOCK tables给表显示加表锁时，必须同时取得所有涉及表的锁，并且MySQL支持锁升级。也就是说，在执行LOCK TABLES后，只能访问显示加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。其实，在自动加锁的情况下也基本如此，MYSQL一次性获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁（deadlock free）的原因。

一次session使用LOCK TABLES命令给表加了读锁，这个session可以查询锁定表中的记录，但更新或访问其他表都会提示错误；同时，另外一个session可以查询表中的记录，但更新就会出现锁等待。当使用LOCK TABLES时，不仅需要一次锁定用到的所有表，而且，同一个表在SQL语句中出现多少次，就要通过与SQL语句中相同的别名锁多少次，否则也会出错。

并发锁

MyISAM在读操作占主导的情况下是很高效的。可一旦出现大量的读写并发，同InnoDB相比，MyISAM的效率就会直线下降，而且，MyISAM和InnoDB的数据存储方式也有显著不同：通常，在MyISAM里，新数据会被附加到数据文件的结尾，可如果时常做一些UPDATE，DELETE操作之后，数据文件就不再是连续的，形象一点来说，就是数据文件里出现了很多洞洞，此时再插入新数据时，按缺省设置会先看这些洞洞的大小是否可以容纳下新数据，如果可以，则直接把新数据保存到洞洞里，反之，则把新数据保存到数据文件的结尾。之所以这样做是为了减少数据文件的大小，降低文件碎片的产生。但InnoDB里则不是这样，在InnoDB里，由于主键是cluster的，所以，数据文件始终是按照主键排序的，如果使用自增ID做主键，则新数据始终是位于数据文件的结尾。

在一定条件下，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表不太适合于大量更新操作和查询操作应用的原因，大量的更新操作会造成查询很难获得读锁，从而可能永远阻塞。可以通过设置以下手段来调整

通过指定启动参数low_priority_updates，使MyISAM引擎默认给予读请求以优先的权利

通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级

另外，MYSQL也提供了一种折中得办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL便暂时将写请求的优先级降低，给读进程一定获得锁的机会。

应用中应尽量避免出现长时间运行的查询操作，不要总想用一条SELECT语句来解决问题。因为这种看似巧妙的SQL语句，往往比较复杂，执行时间较长，在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”，使每一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行。

行级锁（InnoDB）

InnoDB与MyISAM的最大不同有两点：1、支持事务；2、采用了行级锁。

事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元。事务具有ACID属性。

原子性（Actomicity）: 事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。

一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持完整性；事务结束时，所有的内部数据结构（B树索引或双向链表）也都必须是正确的。

隔离性（isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的独立环境执行。这意味着事务处理过程中的中间状态对外部是不可见的。

持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

并发事务带来的问题

更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题，最后的更新覆盖了其他事务所作的更新。

脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些脏的数据，并据此做了进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做脏读。
　一句话：事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。

不可重复读（Non-Repeatable Reads）：一个事务在读取了某些数据后的某个时间，再次读取以前读取过的数据，却发现其读出的数据已经发生了改变！这种现象叫做不可重复读。
一句话：事务A读取到了事务B已经提交的修改数据，不符合隔离性

幻读（Phantom Reads）：　一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据或某些记录已经被删除了，这种现象就称为“幻读”。一句话：事务A读取到了事务B提交的新增数据或事务B删除了数据，不符合隔离性

事务隔离级别

要防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决。因此，防止更新丢失应该是应用的责任。
脏读，不可重复读和幻读，都是数据库一致性问题，必须由数据库提供一定的事务隔离机制来解决。
数据库实现事务隔离（isolation）的方式，基本可分为以下两种（悲观锁和乐观锁）：

悲观锁方式：一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改；
乐观锁方式：不用加锁，通过一定机制生成一个数据请求时间点的一致性数据快照，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度，好像是数据库可以提供同一个数据的多个版本，这种技术叫做数据多版本并发控制（ＭultiVersion Concurrency Control，简称MVCC或MCC），也称为多版本数据库。

4种隔离级别

Serializable （串行化）：最严格的级别，事务串行执行，资源消耗最大；

REPEATABLE READ（重复读）：保证了一个事务不会修改已经由另一个事务读取但未提交（回滚）的数据。避免了“脏读取”和“不可重复读取”的情况，但不能避免“幻读”，但是带来了更多的性能损失。

READ COMMITTED （提交读）：大多数主流数据库的默认事务等级，保证了一个事务不会读到另一个并行事务已修改但未提交的数据，避免了“脏读取”，但不能避免“幻读”和“不可重复读取”。该级别适用于大多数系统。

Read Uncommitted（读未提交）：事务中的修改，即使没有提交，其他事务也可以看得到，会导致“脏读”、“幻读”和“不可重复读取”。

数据库的事务隔离级别越严格，并发副作用越小，但事务执行效率越差。因为事务隔离实质上就是使事务在一定程度上”串行化”执行。

数据库一致性问题

脏读：所谓的脏读，其实就是读到了别的事务回滚前的脏数据。比如事务B执行过程中修改了数据X，在未提交前，事务A读取了X，而事务B却回滚了，这样事务A就形成了脏读。

也就是说，当前事务读到的数据是别的事务想要修改成为的但是没有修改成功的数据。

不可重复读：事务A首先读取了一条数据，然后执行逻辑的时候，事务B将这条数据改变了，然后事务A再次读取的时候，发现数据不匹配了，就是所谓的不可重复读了。

也就是说，当前事务先进行了一次数据读取，然后再次读取到的数据是别的事务修改成功的数据，导致两次读取到的数据不匹配，也就照应了不可重复读的语义。

幻读：事务A首先根据条件索引得到N条数据，然后事务B改变了这N条数据之外的M条或者增添了M条符合事务A搜索条件的数据，导致事务A再次搜索发现有N+M条数据了，就产生了幻读。

也就是说，当前事务读第一次取到的数据比后来读取到数据条目少。

不可重复读和幻读比较：
两者有些相似，但是前者针对的是update或delete，后者针对的insert。

事务4种隔离级别比较

隔离级别/读数据一致性及允许的并发副作用	读数据一致性	脏读	不可重复读	幻读
读未提交（Read uncommitted）	最低级别，只能保证不读取物理上损坏的数据	是	是	是
已提交读（Read committed）	语句级	否	是	是
可重复读（Repeatable read）	事务级	否	否	是
可序列化（Serializable）	最高级别，事务级	否	否	否

Mysql查看事务隔离级别命令为

1	show VARIABLES like '%tx_isolation%'

Oracle只提供Read committed（默认）和Serializable两个标准级别，另外还自己定义的Read only隔离级别：ＭySQL支持全部４个隔离级别，默认为REPEATABLE-READ。

InnoDB的行锁模式及加锁方法

InnoDB实现了以下两种类型的行锁

共享锁：又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

排他锁：又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

意向共享锁（IS）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

InnoDB行锁模式兼容性列表

当前锁模式/是否兼容/请求锁模式	X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容
S	冲突	冲突	兼容	兼容
IS	冲突	兼容	兼容	兼容

如果一个事务请求的锁模式与当前的锁兼容，Innodb就请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。
意向锁是InnoDB自动加的，不需用户干预。对于update、delete和insert语句，innodb会自动给涉及数据行加排他锁；对于普通的select语句，innodb不会加任何锁。事务可以通过以下语句显示给记录集加共享锁和排他锁。
共享锁（S）： select * from table_name where … lock in share mode;
排他锁（X）： select * from table_name where … for update;
用select…in share mode获得共享锁，主要用在需要数据依存关系时确认某行记录是否存在，并确保没有人对这个记录进行update或者delete操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定记录后需要进行更新操作的应用，应该用select…for update方式获取排他锁。

innodb实现行锁的方式

innodb行锁是通过索引上的索引项来实现的，Oracle是通过在数据中对相应数据行加锁来实现的，innodb行锁只有通过索引条件检索数据，innodb才会使用到行锁，否则，innodb将使用表锁！
在实际应用中，要特别注意innodb行锁这一特性，不然可能会导致大量的锁冲突，从而影响并发性能。

间隙锁（Next-Key锁）

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，innodb会给符合条件的已有数据索引项加锁；对于键值在条件范围内但不存在的记录，叫做间隙（GAP），innodb也会对这个间隙加锁。
举例来说，假如emp表中只有101条记录，其中empid的值分别是1-101，下面的SQL：

1	select * from emp where empid > 100 for update;

是一个范围条件的检索，innodb不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101的数据加锁，尽管这些数据不存在。
innodb使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求。如果不使用间隙锁，其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另一方面，是为了满足其恢复和复制的需要。
很显然，在使用范围条件检索并锁定记录时，innodb这种加锁机制会阻塞符合条件范围内键值的并发插入。这种往往会造成严重的锁等待。因此，在实际开发中，尤其是并发插入比较多的应用，我们尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件！

使用表锁的场景

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

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

在innodb下，使用表锁要注意以下两点：

使用lock tables虽然可以给innodb加表锁，但表锁不是innodb存储引擎管理的，而是由其上一层MySQL Server负责的，仅当autocommit=0、innodb_table_lock=1（默认设置）时，innodb层才能直到Mysql加的表锁，Mysql Server才能感知Innodb加的行锁，这种情况下，innodb才能自动识别涉及表锁的死锁，否则，innodb将无法自动检测并处理这种死锁。

在用lock tables对innodb锁时要注意，要将autocommit=0，否则MySQL不会给表加锁；事务结束前，不要用UNLOCAK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK产不能释放用LOCAK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。

死锁

死锁的四个必要条件：

互斥条件(Mutual exclusion)：资源不能被共享，只能由一个进程使用。

请求与保持条件(Hold and wait)：已经得到资源的进程可以再次申请新的资源。

非剥夺条件(No pre-emption)：已经分配的资源不能从相应的进程中被强制地剥夺。

循环等待条件(Circular wait)：系统中若干进程组成环路，该环路中每个进程都在等待相邻进程正占用的资源。

MyISAM表锁是deadlock free的，因为MyISAM总是一次性获得所需要的全部锁。而在innodb中，除单个SQL组成的事务外，锁是逐步获得的，这就决定了innodb发生死锁的可能性。
发生死锁后，innodb一般都能自动检测到，并使一个事务释放锁并退回，另一个事务获得锁，继续完成事务。但在涉及外部锁的情况下，innodb并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。需要说明的是，这个参数并不是只是用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获取所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖垮数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

导致死锁情况

在应用中，如果不同的程序会并发存取多个表，应尽量约定按相同的顺序访问表，这样可以大大降低产生死锁的机会。如果两个session访问两个表的顺序不同，发生死锁的机会就非常高！但如果以相同的顺序来访问，死锁就可能避免。
在程序按批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低死锁的可能性
在事务中，如果要更新记录，应该直接申请足够级别的锁，排他锁，而不是应该先申请共享锁，更新时再申请排他锁，甚至死锁。
在REPEATEABLE READ隔离级别下，如果两个线程同时对相同条件记录用SELECT…ROR UPDATE加排他锁，在没有符合该记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录。如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可以避免问题。
当隔离级别为READ COMMITED时，如果两个线程都先执行SELECT…FOR UPDATE，判断是否存在符号条件记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现所等待，当第1个线程提交后，第2个线程会因主键出错，但是这个线程出错了，却会获得一个排他锁！这时如果有第3个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键异常，或者在遇到主键错误时，总是执行ROLLBACK释放获得的排他锁。
如果出现死锁，可以用SHOW ENGINE INNODB STATUS命令来确定最后一个死锁产生的原因和改进措施。

总结

对于MyISAM的表锁，主要有以下几点：

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

对于innodb表，主要有以下几点：

innodb的行锁是基于索引实现的，如果不通过索引访问数据，innodb会使用表锁
innodb锁冲突甚至死锁很难完全避免

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

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