mysql两个写锁_MySql性能（4）-mysql锁知多少（表/行锁、共享读锁/独占写锁、共享锁/排他锁、间隙锁、意念锁）...

MyISAM到底怎么读？，这篇问题从多个角度分析mysql数据库锁的基本知识。

1. MySQL锁概论：

Mysql的锁机制比较简单，其最显著的特定就是：不同存储引擎支持不同的锁机制！！！

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

BDB存储引擎采用的是页面锁(page-level locking)，但也支持表级锁；

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

那么什么叫做表级锁、行级锁、页面锁呢？

表级锁：开销小，加锁快；不会出现死锁；锁粒度大。发生冲突几率最大，并发度最低。

行级锁：开销大，加锁慢；会出现死锁，发生锁冲突几率最低，并发度最高。

页面锁：介于表级锁和行级锁之间。

那选用那种锁最好呢？

从上述特点来说，无法确切的说出那种锁更好，只能根据具体应用的特点来说那种最合适。

表级锁：更适合于查询为主的场景。

行级锁：更适合于大量按索引条件 并发更新 少量不同的数据，同时又有 并发查询 的应用。

1.1那么mysql何时使用MyISAM，什么时候使用InnoDB

INNODB和MyISAM是mysql数据库提供的两种存储引擎。INNODB会支持一些关系数据库的高级功能，如事务功能和行级锁；MyISAM不支持，但是MyISAM的性能更优，占用的空间少。

如果应用程序一定要使用事务，那么要选择INNODB引擎。但是INNODB的行级锁是有条件的。在where条件没有使用主键时，照样会锁住全表。比如delete from mytable

若是应用程序对查询性能要求高，就要使用MyISAM。MyISAM索引和数据是分开的，而且索引是压缩的，可以更好的利用内存。所以它的查询性能明细优于Innodb。MyISAM拥有全文索引的功能，这可以极大的优化like查询的效率。

InnoDB什么时候使用表锁，什么时候使用行锁？

事务需要更新大部分或者全部数据，表又比较大。如果使用默认的行锁，那么不仅事务效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高事务的执行速度。

事务设计到多个表，比较复杂，很可能造成死锁，造成大量事务回滚。也可以考虑一次性锁定事务涉及到的表，从而避免死锁，减少数据库事务回滚的开销。

当然应用中若是1，2比较多，那么就可以考虑使用MyISAM表了。

MyISAM和InnoDB的区别：

InnoDB支持事务和外键以及行级锁，MyISAM不支持。

MyISAM读性能优于InnoDB。

MyISAM索引和数据是分开的，而且索引是压缩的，而InnoDB索引和数据是紧密捆绑在一起的，无法压缩，所以InnoDB的体积比MyISAM庞大。

MyISAM引擎索引结构的叶子节点的数据域，存放的并不是实际的数据类型，而是数据记录的地址。索引文件与数据文件分离，这样的索引称之：“非聚簇索引”。

InnoDB引擎索引结构的叶子节点的数据域，存放是就是实际的数据记录。这样的索引被称为“聚簇索引”，一个表只能有一个聚簇索引。

InnoDB并不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描整个表来计算有多少行，但是MyISAM只要简单读出保存好的行数即可。注意的是，当count(*)语句包含where条件时，两个表的操作是一样的。

InnoDB表的行锁也不是绝对的，假如在执行一个SQL语句时MySQL不能确定扫描的范围，InnoDB表同样也会锁全表。

在where条件没有主键时，InnoDB照样会锁全表。

2. MyISAM和MEMORY引擎的表锁

MySQL表级锁有两种模式：表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock)。

对于MyISAM表的读操作，不会阻塞其他用户对同一表的读操作。但会阻塞对同一个表的写操作。

对于MyISAM表的写操作，则会阻塞其他用户对同一表的读操作和写操作。

2.1 如何加表锁

MyISAM在执行查询语句(SELECT)前，会自动给涉及的所有表加读锁，再执行查询操作。

(update、delete、insert)前，会自动给涉及到的表加写锁。用户一般不需要显示加锁。

2.2 如何优化MyISAM表锁

对于MyISAM存储引擎，虽然使用表级锁加锁和解锁的开销最小，但是由于加锁粒度大，所以会造成更多的资源争用的情况。会较大程度上降低并发处理能力。

优化MyISAM存储引擎关键是提高并发度。

2.2.1 查询表级锁争用情况：

使用show status like 'table%'可以查询系统内部锁资源争用的情况。

表级优化

Table_locks_immediate：产生表级锁定的次数；

Table_locks_waited：出现表级锁定争用而等待的次数。

两个参数值都是系统启动后开始记录的，出现一次对应的事件，则数量加一。当Table_locks_waited状态值比较高时，那么说明系统中表级锁定争用现象比较明显。

2.2.2 缩短锁定时间：

如何让锁定时间尽可能的短呢？唯一的办法就是让我们的Query执行的时间尽可能短。

尽量减少大的复杂的sql，将复杂的query分拆成为小的query分步进行。

尽可能建立高效索引，让数据检索更迅速。

尽量让MyISAM存储引擎的表只存放必要信息，控制字段类型。

利用合适的机会优化MyISAM表的数据。

2.2.3 分离能并行的操作：

虽然MyISAM是读写相互阻塞的表锁，但是MyISAM存储引擎还有一个有用的特性，那就是ConcurrentInsert(并发插入)的特性。

当concurrent_insert=0时，此时读不能与insert写共存。

当concurrent_insert=1时，如果表中没有空数据块时，读可以与insert写共存，insert新增加的数据将插入到数据文件尾部。(默认设置)。

当concurrent_insert=2时，不论有没有空数据块，insert新增加的数据都将插入到数据文件尾部。

注：在myisam里，如果没有空的数据块，新增加的数据都会附加到数据文件的尾部，但是如果经常做update和delete操作，数据文件就不再是连续的，出现了许多空的数据块，此时再插入数据，按照缺省设置会先看看这些空的数据块是否能够容纳新插入的数据，如果可以则直接存储到这些空的数据块里，如果不行再直接插入到数据文件的尾部。MyISAM的默认的这种处理方式是为了减少数据文件的大小和碎片，以免造成IO性能问题。

如果我们要设置concurrent_insert=2，后面插入的数据都会直接追加到数据文件的尾部，而系统如果有频繁的delete和update操作，可能就会有过多的碎片造成过多的IO消耗，小鱼建议如果需要设置concurrent_insert=2一定要定期对表进行(优化)optimizer，以免造成过多的碎片。

可以利用MyISAM存储引擎的并发插入特性，来解决应用中对同一表的插入和查询操作。可以将concurrent_insert设置为2，总是允许并发插入；同时通过定期在系统空闲时段执行optimizer ['ɑ:ptɪmaɪzər] table语句来整理空间碎片，

2.2.4 合理利用读写优先级：

MyISAM存储引擎的读写时相互阻塞的，但是，一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一个表的写锁，MySQL如何处理呢？

默认情况下：写进程先获得锁，不仅如此，即便读请求先到锁等待队列，写请求后到，写锁也会插入到读锁之前。

这是由于Mysql的表级锁定对于读和写是有不同优先级设定的，默认情况下写的优先级要大于读的优先级。

所以可以利用各自系统的差异觉得写与读的优先级：

通过执行命令：set low_priority_updates=1 [praɪˈɒrəti]，使该连接的读请求优先级比写的优先级高。如果系统是一个以读为主，可以设置改参数，否则，不需要设置。

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

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

还有一点：一些需要长时间运行的查询操作，也会使得写进程“饿死”，因此，应用中尽量避免出现长时间运行的查询操作。

2.3 Innodb行锁

总的来说，InnoDB的锁定机制和Oracle数据库有不少相似之处。InnoDB的行级锁分为两种类型：共享锁和排他锁。而在锁定机制的实现过程中，为了让行级锁和表级锁共存，InnoDB也同样使用了意向锁(表级锁定)的概念，于是也就有了意向共享锁和意向排他锁两种。

当一个事务需要给自己需要的资源加锁时，如果遇到有一个共享锁正锁定自己需要的资源的时候，自己可以再加一个共享锁，不过不能加排他锁。但是，如果遇到自己需要的资源已经被排它锁占用之后，则只能等待该排他锁释放资源之后自己才能获取资源，并将其锁定。

2.3.1 什么叫做意向锁

可以说：Inoodb的锁定模式实际上可以分为四种：共享锁(S)，排它锁(X)，意向共享锁(IS)，意向排他锁(IX)

首先，申请意向锁的动作是数据库完成的，也就是说，事务A申请一行行锁的时候，数据库会自动先开始申请表的意向锁。

那么到底意向锁有什么用？

首先，意向锁是——表级锁。

(1)事务A锁住了表中的一行，这一行只能读，不能写。【事务A加了共享锁】

(2)事务B申请整个表的写锁。【请注意：是整个表！！！】

(3)如果事务B申请成功，那么理论上它就能修改表中的任意一行，这与A持有的共享锁时冲突的。【事务A不允许修改】

(4)数据库为避免这种冲突，怎么办的？就是让B的申请阻塞，直到A释放行锁。

解决方案：

step1：判断表是否已被其他事务用表锁锁住。

step2：判断表中每一行是否已经被行锁锁住。

请注意step2，需要遍历整个表。效率实在不高。

改进版：

step1：不变

step2：发现表上有意向共享锁，说明表中有些行被共享行锁锁住了，因此，事务B申请表的写锁会被阻塞。

锁之间的兼容关系

假如一个表被加了意向排他锁(IX)，证明此时有事务在修改表中具体某行的数据，那么对应的某行可能加了x锁，1.如果这时候其他事务要再加意向锁，那么可以加成功(因为加了意向锁之后，后续查询或者修改的是某行的数据，这行和上面的x锁未必冲突)所以意向锁之间是兼容的。2.如果此时其他事务加的是全表共享锁S，因为前面表中的数据正在被修改，所以S锁是加不成功的。所以意向排他锁和表共享锁是冲突的。

(敲黑板，划重点)意向锁的作用就是协调行锁和表锁之间的关系的，是将行锁从另一个角度提高到了表锁的等级(伪表锁)，与表锁进行判断。

注意：select语句不是加锁！！！

意向锁是InnDB自动加的，不需要用户的干预。

对于update、delete和insert语句，Innodb会自动给涉及数据集加排他锁(X)；对于普通的select语句，Innodb不会加任何锁！！！事务可以通过以下语句显式的给记录集加锁：

//共享锁

select * from table_name where ... lock in share mode;

//排它锁

select * from table_name where ... for update;

2.3.2 使用共享锁注意事项

使用 select ... in share mode获取共享锁，主要用在数据依存关系时，确认某行记录是否存在，并且确保没有人对这个记录进行update或者delete操作。

(敲黑板，划重点)但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定记录后进行更新的应用，应该使用select...for update方式获得排他锁。

2.3.3 InnoDB行锁实现方式

InnoDB行锁是通过给索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。

在实际开发中，要特别注意InnoDB这一特性，不然，可能造成大量的锁冲突，从而影响并发！！！

InnoDB使用索引的条件：

在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。

mysql的行锁是针对索引加的锁。不是针对记录加的锁，虽然是访问不同的行，但是若是相同的索引，会出现锁锁冲突的。

当表中含有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行。

即使在条件中使用了索引，但是是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价决定的，如果MySQL认为全表扫描效率更高，比如很小的表，他也不会使用索引，此时InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突的时候，不要忘记检查SQL的执行计划，以确定是否真正使用了索引。

关于InnoDB到底是使用行锁还是表锁，我们需要依据索引来决定的，本质上行锁是针对索引加的锁，而非记录！！！虽然是访问不同的行，但是若是含有相同的索引，还是会发生锁冲突的！！！而且就算条件里面使用了索引，Mysql也不一定走索引，还是要看SQL的执行计划！！！

2.3 间隙锁

当我们用范围条件而不是相等条件检索数据的时候，并请求共享或者排他锁时，InnoDB会给符合条件的已有的数据记录的索引项加锁。

对于键值在条件范围内但是不存在的记录，叫做间隙(GAP)。InnoDB也会对这个“间隙”加锁，这种锁机制就是间隙锁(Next-Key锁)

InnoDB使用间隙锁的目的：

防止幻读，以满足相关隔离级别的要求；

满足恢复和复制的需要；

InnoDB的危害：

使用范围条件检索并锁定记录时，即使某些不存在的键值也会被无辜的锁定。而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些时刻可能会造成很大的危害。

在实际应用开发中，尤其是并发插入比较多的应用，尽量优化业务逻辑，尽量使用相等的条件来访问更新数据，避免使用范围条件。

需要特别说明的是：InnoDB除了通过范围条件加锁使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁。

3. 死锁

MyISAM表锁总是一次性获得所需的全部锁，要么全部满足，要不等待，因此不会出现死锁。

InnoDB中，处理单个sql组成的事务外，锁是逐步获得的，当两个事务都需要获得对方持有的排他锁才能完成事务时，这种循环等待就是典型的死锁。

在InnoDB的事务管理和锁机制中，有专门检测死锁的机制，会在系统产生死锁之后的很短的时间内就检测到死锁的存在。当InnoDB检测到系统产生了死锁之后，InnoDB会通过相应的判断来选出产生死锁两个事务中较小的事务回滚，而让较大的事务成功完成。

但是需要注意，当产生死锁的场景涉及到的不只是InnoDB存储引擎的情况下，InnoDB是无法检测到死锁的，只能通过锁的超时限制参数InnoDB_lock_wait_timeout来解决。

需要说明的是，这个参数并不是解决死锁问题的，而事故在并发高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重的性能问题。甚至拖垮数据库。我们可以通过设置合适的锁等待超时的阈值，避免这种情况的发送。

通常来说，死锁都是应用设计的问题，我们可以通过：

若不同程序并发存取多个表，应约定以相同的顺序来访问表，这样可以降低死锁的机会。

在程序中以批量的方式处理数据时，如果事先对数据进行排序，保证每个线程按固定顺序处理记录，也可以大大降低死锁可能。

在事务中，如果要更新记录，应该申请足够级别的锁(排它锁)，而不是先申请共享锁，更新时在申请排他锁。当用户申请排他锁时，其他事务可能已经获得了相同记录的共享锁，从而造成锁等待，甚至死锁。

历史文章

文章参考