MySQL中的锁（表锁、⾏锁）并发控制锁
github/MrLining/mysql/wiki/MySQL%E4%B8%AD%E7%9A%84%E9%94%81%EF%BC%88%E8%A1%A8%E9%94%81%E3%80%81%E8%A1%8C%E9%94%81%EF%BC%89-%E5%B9%B6%E5%8F%91%E6%8E%A7%E5%88%B6%E9%94%81
6、表锁差异
MyISAM：只⽀持表级锁，⽤户在操作myisam表时，select，update，delete，insert语句都会给表⾃动加锁，如果加锁以后的表满⾜insert并发的情况下，可以在表的尾部插⼊新
的数据。
InnoDB：⽀持事务和⾏级锁，是innodb的最⼤特⾊。⾏锁⼤幅度提⾼了多⽤户并发操作的新能。但是InnoDB的⾏锁，只是在WHERE的主键是有效的，⾮主
键的WHERE都会锁全表的。
8、表主键
MyISAM：允许没有任何索引和主键的表存在，索引都是保存⾏的地址。
InnoDB：如果没有设定主键或者⾮空唯⼀索引，就会⾃动⽣成⼀个6字节的主键(⽤户不可见)，数据是主索引的⼀部分，附加索引保存的是主索引的值的数据列。
11、外键
MyISAM：不⽀持
InnoDB：⽀持
通过上述的分析，基本上可以考虑使⽤InnoDB来替代MyISAM引擎了，原因是InnoDB⾃⾝很多良好的特点，⽐如事务⽀持、存储过程、视图、⾏级锁定等等，在并发很多的情
况下，相信InnoDB的表现肯定要⽐MyISAM强很多。另外，任何⼀种表都不是万能的，只⽤恰当的针对业务类型来选择合适的表类型，才能最⼤的发挥MySQL的性能优势。如果
不是很复杂的Web应⽤，⾮关键应⽤，还是可以继续考虑MyISAM的，这个具体情况可以⾃⼰斟酌。
存储引擎选择的基本原则
采⽤MyISAM引擎
R/W > 100:1 且update相对较少
并发不⾼
表数据量⼩
硬件资源有限
采⽤InnoDB引擎
R/W⽐较⼩，频繁更新⼤字段
表数据量超过1000万，并发⾼
安全性和可⽤性要求⾼
采⽤Memory引擎
有⾜够的内存
对数据⼀致性要求不⾼，如在线⼈数和session等应⽤
需要定期归档数据
作者：codefix
链接：www.jianshu/p/a957b18ba40d
來源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。
=============================
⼀、MySQL中的锁（表锁、⾏锁）
背景：当数据库中有多个操作需要修改同⼀数据时，不可避免的会产⽣数据的脏读。这时就需要数据库具有良好的并发控制能⼒，这⼀切在 MySQL 中都是由服务器和存储引擎
来实现的。
解决并发问题最有效的⽅案是引⼊了锁的机制，锁在功能上分为共享锁 (shared lock) 和排它锁 (exclusive lock) 即通常说的读锁和写锁。
锁是计算机协调多个进程或纯线程并发访问某⼀资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争⽤以外，数据也是⼀种供许多⽤户共享的资源
概述
相对其他数据库⽽⾔，MySQL的锁机制⽐较简单，其最显著的特点是不同的存储引擎⽀持不同的锁机制。 MySQL⼤致可归纳为以下3种锁：
1. 表级锁：开销⼩，加锁快；不会出现死锁；锁定粒度⼤，发⽣锁冲突的概率最⾼，并发度最低。
2. ⾏级锁：开销⼤，加锁慢；会出现死锁；锁定粒度最⼩，发⽣锁冲突的概率最低，并发度也最⾼。
3. 页⾯锁：开销和加锁时间界于表锁和⾏锁之间；会出现死锁；锁定粒度界于表锁和⾏锁之间，并发度⼀般
注意：
有两种类型的表级锁：读锁和写锁。读锁是共享锁，⽀持并发读，写操作被锁。写锁是独占锁，上锁期间其他线程不能读表或写表。如果要⽀持并发读写，建议采⽤ InnoDB 表，因为它是采⽤⾏级锁，可以获得更多的更新性能。
MySQL表级锁的锁模式（MyISAM)
ＭySQL的表锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。ＭySQL中的表锁兼容性如下表：
当前锁模式/是否兼容/请求锁模式读锁写锁
读锁是否
写锁否否
对ＭyISAM表的读操作，不会阻塞其他⽤户对同⼀表的读请求，但会阻塞对同⼀表的写请求；
对ＭyISAM 表的写操作，则会阻塞其他⽤户对同⼀表的读和写请求；ＭyISAM 表的读和写操作之间，以及写和写操作之间是串⾏的！
（***当⼀线程获得对⼀个表的写锁后，只有持有锁的线程可以对表进⾏更新操作。其他线程的读、写操作都会等待，直到锁被释放为⽌。***）
当前锁模式/是否兼容/请求锁模式读锁写锁并发锁，表锁并发插⼊ 在⼀定条件下，MyISAM 也⽀持查询和操作的并发进⾏。 MyISAM 存储引擎有⼀个系统变量concurrent_insert ，专门⽤以控制其并发插⼊的⾏为，其值分
别可以为0、1或2。
当concurrent_insert 设置为0时，不允许并发插⼊。
当concurrent_insert 设置为1时，MyISAM 允许在⼀个读表的同时，另⼀个进程从表尾插⼊记录。这也是MySQL 的默认设置。当concurrent_insert 设置为2时，⽆论MyISAM 表中有没有空洞，都允许在表尾并发插⼊记录。可以利⽤MyISAM
存储引擎的并发插⼊特性，来解决应⽤中对同⼀表查询和插⼊锁争⽤。
例如，将concurrent_insert 系统变量为2，总是允许并发插⼊；同时，通过定期在系统空闲时段执⾏O
PTIONMIZE TABLE 语句来整理空间碎⽚，收到因删除记录⽽产⽣的中间空洞。
MyISAM 的锁调度
MyISAM 存储引擎的读和写锁是互斥的，读操作是串⾏的。那么，⼀个进程请求某个MyISAM 表的读锁，同时另⼀个进程也请求同⼀表的写锁，MySQL 如何处理呢？答案是写进程先获得锁。 不仅如此，即使读进程先请求先到锁等待队列，写请求后到，写锁也会插到读请求之前！mysql中delete语句
这是因为MySQL 认为写请求⼀般⽐读请求重要。这也正是MyISAM 表不太适合于有⼤量更新操作和查询操作应⽤的原因，因为，⼤量的更新操作会造成查询操作很难获得读锁，从⽽可能永远阻塞。这种情况有时可能会变得⾮常糟糕！ 我们可以通过⼀些设置来调节MyISAM 的调度⾏为：1. 通过指定启动参数low-priority-updates ，使MyISAM 引擎默认给予读请求以优先的权利。2. 通过执⾏命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。3. 通过指定INSERT 、UPDATE 、DELETE 语句的LOW_PRIORITY 属性，降低该语句的优先级。
虽然上⾯3种⽅法都是要么更新优先，要么查询优先的⽅法，但还是可以⽤其来解决查询相对重要的应⽤（如⽤户登录系统）中，读锁等待严重的问题。 另外，MySQL 也提供了⼀种折中的办法来调节读写冲突，即给系统参数max_write_lock_count 设置⼀个合适的值，当⼀个表的读锁达到这个值后，MySQL 变暂时将写请求的优先级降低，给读进程⼀定获得锁的机会。 上⾯已经讨论了写优先调度机
制和解决办法。这⾥还要强调⼀点：⼀些需要长时间运⾏的查询操作，也会使写进程“饿死”！因此，应⽤中应尽量避免出现长时间运⾏的查询操作，不要总想⽤⼀条SELECT 语句来解决问题。因为这种看似巧妙的SQL 语句，往往⽐较复杂，执⾏时间较长，在可能的情况下可以通过使⽤中间表等措施对SQL 语句做⼀定的“分解”，使每⼀步查询都能在较短时间完成，从⽽减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执⾏，⽐如⼀些定期统计可以安排在夜间执⾏。
InnoDB 锁问题
InnoDB 与MyISAM 的最⼤不同有两点：⼀是⽀持事务（TRANSACTION ）；⼆是采⽤了⾏级锁。
1、事务（Transaction ）及其ACID 属性
事务是由⼀组SQL 语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID 属性。
原⼦性（Actomicity ）：事务是⼀个原⼦操作单元，其对数据的修改，要么全都执⾏，要么全都不执⾏。
⼀致性（Consistent ）：在事务开始和完成时，数据都必须保持⼀致状态。这意味着所有相关的数据规则都必须应⽤于事务的修改，以操持完整性；事务结束时，所有的内部数据结构（如B 树索引或双向链表）也都必须是正确的。
隔离性（Isolation ）：数据库系统提供⼀定的隔离机制，保证事务在不受外部并发操作影响的“独⽴”环境执⾏。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
持久性（Durable ）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。
2、并发事务带来的问题
相对于串⾏处理来说，并发事务处理能⼤⼤增加数据库资源的利⽤率，提⾼数据库系统的事务吞吐量，从⽽可以⽀持可以⽀持更多的⽤户。但并发事务处理也会带来⼀些问题，主要包括以下⼏种情况。
更新丢失（Lost Update ）：当两个或多个事务选择同⼀⾏，然后基于最初选定的值更新该⾏时，由于每个事务都不知道其他事务的存在，就会发⽣丢失更新问题——最后的更新覆盖了其他事务所做的更新。例如，两个编辑⼈员制作了同⼀⽂档的电⼦副本。每个编辑⼈员独⽴地更改其副本，然后保存更改后的副本，这样就覆盖了原始⽂档。最后保存其更改保存其更改副本的编辑⼈员覆盖另⼀个编辑⼈员所做的修改。如果在⼀个编辑⼈员完成并提交事务之前，另⼀个编辑⼈员不能访问同⼀⽂件，则可避免此问题
脏读（Dirty Reads ）：⼀个事务正在对⼀条记录做修改，在这个事务并提交前，这条记录的数据就处于不⼀致状态；这时，另⼀个事务也来读取同⼀条记录，如果不加控制，第⼆个事务读取了这些“脏”的数据，并据此做进⼀步的处理，就会产⽣未提交的数据依赖关系。这种现象被形象地叫做“脏读”。
不可重复读（Non-Repeatable Reads ）：⼀个事务在读取某些数据已经发⽣了改变、或某些记录已经被删除了！这种现象叫做“不可重复读”。
幻读（Phantom Reads ）：⼀个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插⼊了满⾜其查询条件的新数据，这种现象就称为“幻读”。
3.事务隔离级别
在并发事务处理带来的问题中，“更新丢失”通常应该是完全避免的。但防⽌更新丢失，并不能单靠数据库事务控制器来解决，需要应⽤程序对
要更新的数据加必要的锁来解决，因此，防⽌更新丢失应该是应⽤的责任。
“脏读”、“不可重复读”和“幻读”，其实都是数据库读⼀致性问题，必须由数据库提供⼀定的事务隔离机制来解决。数据库实现事务隔离的⽅式，基本可以分为以下两种。
⼀种是在读取数据前，对其加锁，阻⽌其他事务对数据进⾏修改。 另⼀种是不⽤加任何锁，通过⼀定机制⽣成⼀个数据请求时间点的⼀致性数据快照（Snapshot ），并⽤这个快照来提供⼀定级别（语句级或事务级）的⼀致性读取。
从⽤户的⾓度，好像是数据库可以提供同⼀数据的多个版本，因此，这种技术叫做数据多版本并发控制（ＭultiVersion Concurrency Control ，简称MVCC 或MCC ），也经常称为多版本数据库。
数据库的事务隔离级别越严格，并发副作⽤越⼩，但付出的代价也就越⼤，因为事务隔离实质上就是使事务在⼀定程度上“串⾏化”进⾏，这显然与“并发”是⽭盾的，同时，不同的
应⽤对读⼀致性和事务隔离程度的要求也是不同的，⽐如许多应⽤对“不可重复读”和“幻读”并不敏感，可能更关⼼数据并发访问的能⼒。为了解决“隔离”与“并发”的⽭
盾，ISO/ANSI SQL92定义了４个事务隔离级别，每个级别的隔离程度不同，允许出现的副作⽤也不同，应⽤可以根据⾃⼰业务逻辑要求，通过选择不同的隔离级别来平衡＂隔离＂与＂并发＂的⽭盾
事务４种隔离级别⽐较:
隔离级别/读数据⼀致性及允许的并发副作⽤读数据⼀致性脏读不可重复读幻读
未提交读（Read uncommitted）最低级别，只能保证不读取物理上损坏的数据是是是
已提交读（Read committed）语句级否是是
可重复读（Repeatable read）事务级否否是
可序列化（Serializable）最⾼级别，事务级否否否
获取InonoD⾏锁争⽤情况可以通过检查InnoDB_row_lock状态变量来分析系统上的⾏锁的争夺情况：
mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 725374 |
| Innodb_row_lock_time_avg | 3181 |
| Innodb_row_lock_time_max | 11045 |
| Innodb_row_lock_waits | 228 |
+-------------------------------+-------+
5 rows in set (0.00 sec)
如果发现争⽤⽐较严重，如Innodb_row_lock_waits和Innodb_row_lock_time_avg的值⽐较⾼，还可以通过设置InnoDB Monitors来进⼀步观察发⽣锁冲突的表、数据⾏等，并分析锁争⽤的原因。
InnoDB的⾏锁模式及加锁⽅法
InnoDB实现了以下两种类型的⾏锁。
共享锁（s）：允许⼀个事务去读⼀⾏，阻⽌其他事务获得相同数据集的排他锁。 --读锁排他锁（Ｘ）：允许获取排他锁的事务更新数据，阻⽌其他事务取得相同的数据集共享读锁和排他写锁。 --写锁
另外，为了允许⾏锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使⽤的意向锁（Intention Locks），这两种意向锁都是表锁。
意向共享锁（IS）：事务打算给数据⾏共享锁，事务在给⼀个数据⾏加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：事务打算给数据⾏加排他锁，事务在给⼀个数据⾏加排他锁前必须先取得该表的IX锁。
InnoDB⾏锁模式兼容性列表
当前锁模式/是否兼容/请求锁模式X IX S IS
X冲突冲突冲突冲突
IX冲突兼容冲突兼容
S冲突冲突兼容兼容
IS冲突兼容兼容兼容
如果⼀个事务请求的锁模式与当前的锁兼容，InnoDB就请求的锁授予该事务；反之，如果两者两者不兼容，该事务就要等待锁释放。意向锁是InnoDB⾃动加的，不需⽤户⼲预。对于UPDATE、DELETE
和INSERT语句，InnoDB会⾃动给涉及及数据集加排他锁（Ｘ）；对于普通SELECT语句，InnoDB会⾃动给涉及数据集加排他锁（Ｘ）；对于普通SELECT语句，InnoDB不会任何锁；事务可以通过以下语句显⽰给记录集加共享锁或排锁。
共享锁（Ｓ）：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⾏锁是通过索引上的索引项来实现的，这⼀点ＭySQL与Oracle不同，后者是通过在数据中对相应数据⾏加锁来实现的。InnoDB这种⾏锁实现特点意味者：只有通过索引条件检索数据，InnoDB才会使⽤⾏级锁，否则，InnoDB将使⽤表锁！在实际应⽤中，要特别注意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释放表锁，正确的⽅式见如下语句。例如，如果需要写表t1并从表t读，可以按如下做：SET AUTOCOMMIT=0; LOCAK TABLES t1 WRITE, t2 READ, ...; [do something with tables t1 and here]; COMMIT; UNLOCK TABLES;
关于死锁
ＭyISAM表锁是deadlock free的，这是因为ＭyISAM总是⼀次性获得所需的全部锁，要么全部满⾜，要么等待，因此不会出现死锁。但是在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，这就决定了InnoDB发⽣死锁是可能的。发⽣死锁后，InnoDB⼀般都能⾃动检测到，并使⼀个事务释放锁并退回，另⼀个事务获得锁，继续完成事务。但在涉及外部锁，或涉及锁的情况下，InnoDB并不能完全⾃动检测到死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。需要说明的是，这个参数并不是只⽤来解决死锁问题，在并发访问⽐较⾼的情况下，如果⼤量事务因⽆法⽴即获取所需的锁⽽挂起，会占⽤⼤量计算机资源，造成严重性能问题，甚⾄拖垮数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发⽣。通常来说，死锁都是应⽤设计的问题，通过调整业务流程、数据库对象设计、事务⼤⼩、以及访问数据库的SQL语句，绝⼤部分都可以避免。下⾯就通过实例来介绍⼏种死锁的常⽤⽅法。
（１）在应⽤中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序为访问表，这样可以⼤⼤降低产⽣死锁的机会。如果两个session访问两个表的顺序不同，发⽣死锁的机会就⾮常⾼！但如果以相同的顺序来访问，死锁就可能避免。
（２）在程序以批量⽅式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以⼤⼤降低死锁的可能。
（３）在事务中，如果要更新记录，应该直接申请⾜够级别的锁，即排他锁，⽽不应该先申请共享锁，更新时再申请排他锁，甚⾄死锁。
（４）在REPEATEABLE-READ隔离级别下，如果两个线程同时对相同条件记录⽤ROR UPDATE加排他锁，在没有符合该记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插⼊⼀条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可以避免问题。
（５）当隔离级别为READ COMMITED时，如果两个线程都先执⾏FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插⼊记录。此时，只有⼀个线程能插⼊成功，另⼀个线程会出现锁等待，当第１个线程提交后，第２个线程会因主键重出错，但虽然这个线程出错了，却会获得⼀个排他锁！这时如果有第３个线程⼜来申请排他
锁，也会出现死锁。对于这种情况，可以直接做插⼊操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执⾏ROLLBACK释放获得的排他锁。
尽管通过上⾯的设计和优化等措施，可以⼤减少死锁，但死锁很难完全避免。因此，在程序设计中总是捕获并处理死锁异常是⼀个很好的编程习惯。如果出现死锁，可以⽤SHOW INNODB STATUS命令来确定最后⼀个死锁产⽣的原因和改进措施。
实际场景中如何避免锁的资源竞争
1. 让 SELECT 速度尽量快，尽量减少⼤的复杂的Query，将复杂的Query分拆成⼏个⼩的Query分步进⾏；
2. 尽可能地建⽴⾜够⾼效的索引，让数据检索更迅速；
3. 使⽤EXPLAIN SELECT来确定对于你的查询,MySQL认为哪个索引是最适当的，优化你的SQL
4. 当在同⼀个表上同时有插⼊和删除操作时， INSERT DELAYED 可能会很有⽤；
5. 当 SELECT 和 DELETE ⼀起使⽤出现问题时， DELETE 的 LIMIT 参数可能会很有⽤；
6. 合理利⽤读写优先级（⽐如：⽤ LOW_PRIORITY 属性来降低 INSERT ， UPDATE ， DELETE 的优先级；⽤ HIGH_PRIORITY 来提⾼ SELECT 语句的优先级）Welcome to my world!

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。