锁
锁的存在是为了解决并发问题：在同一时间进行读写，可能会引起数据不一致的问题；为了解决这种并发的问题：Mysql使用锁机制去解决。
锁的分类
Mysql使用两种锁机制去决绝并发问题：共享锁（读锁）和排他锁（写锁）
1、共享锁：不影响其他session的读，但写会受影响
2、排他锁：会让其他的session不能进行读写
1 MySql的三种锁
1.1 表锁
- 开销小、加锁快；
- 不会出现死锁；
- 锁定粒度大，发生冲突的概率高，并发度最低
1.2 行锁
- 开销大，加锁慢；
- 会出现死锁；
- 锁定力度小，发生锁冲突的概率最低，并发度最高
1.3 页锁
- 开销和加锁时间介于表锁和行锁之间；
- 也会出现死锁；
- 锁定粒度介于表锁和行锁之间，并发度一般
1.4 锁引擎
- MyISAM和MEMORY支持表锁
- BDB支持页锁和表锁
- Innodb既支持行锁，也支持表锁，默认是行锁
1.5 查询表锁争用情况
SHOW status LIKE 'table%';
检查“Table_locks_immediate”和“Table_locks_waited”
- Table_locks_immediate：表示立即释放表锁数；
- Table_locks_waited：表示需要等待的表锁数
Table_locks_waited的数值越高，则说明存在越严重的表级锁的争用情况，如果“Table_locks_immediate”/“Table_locks_waited”的数值 > 5000，最好采用Innodb引擎，因为InnoDB默认采用行锁，而MyISAM采用表锁，对于高并发的情况使用InnoDB效果会更加的好
2 表锁模式（MyISAM）
MySql的表锁有两种模式
- 表共享读锁（Table Read Lock）
- 表独占写锁（Table Write Lock）
2.1 表锁兼容性
锁的兼容性如下
|是否兼容|请求none|请求读锁|请求写锁|
|-------|-------|-------|-------|
|当前处于读锁|是|是|否|
|当前处于写锁|是|否|否|
MyISAM的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
MyISAM的写操作，则会阻塞其他用户对同一表的读和写请求；
MyISAM的读和写操作之间，以及写和写操作之间采用的是串行的（当某一线程获得一个表的写锁之后，只有持有锁的线程可以对表进行更新操作，其他线程的读、写操作都会等待，直到该线程释放锁为止）；
2.2 如何加表锁
对于MyISAM引擎
- 执行 select 前，会自动给涉及的所有表加 读锁
- 执行更新前（update，delete，insert）会自动给涉及的所有表加 写锁
不需要用户显式的执行 lock table 命令
对于给MyISAM显式加锁，一般是为了在一定的程度上模拟事务，实现对某一个时间点多个表的一致性读取
3MyISAM的并发锁
在一定的条件下，MyISAM也支持并发插入和读取
3.1系统变量：concurrent_insert
控制其并发插入的行为，其值为：“0、1、2”
- 0：不允许并行插入，所有插入操作都要对表加互斥锁
- 1：只要表中无空洞，就运行并发插入，MyISAM允许在一个读表的同事，另一个进行从表尾插入记录（MySql的默认设置，插入数据时是从表尾插入）
- 2：无论MyISAM表中有无空洞，都强制在表尾并发插入记录，若无读线程，新行插入空洞中
可以利用MyISAM的并发插入特性，来解决应用中对同表查询和插入的锁竞争，例如：将“concurrent_insert”设置为2总允许并发插入；
同时，通过定期在系统空闲的时间段执行OPTIONMIZE TABLE语句来整理空间碎片，回收因删除记录而产生的中间空洞
“delete”这种删除操作不会重整整张表，只是把行标记为删除，在表中留下空洞，而使用OPTIONMIZE TABLE可将这些空洞进行回收
MyISAM倾向于在可能是填满这些空洞，插入式就会重用这些空洞，无空洞了就会在表尾插入新的行
3.2 MyISAM的读调度
MyISAM的读和写操作是互斥的，所有的读操作都是串行的
- 一个进程请求某个MyISAM表的读锁，同时另一个进程请求MyISAM表的写锁；这个时候MySql会让请求写锁的进程先获得锁，即便是读的进程先请求，先到锁的等待队列，写请求后到等待队列。写锁的请求会查到读锁请求的前面
这种机制是因为MySql认为写请求一般比读请求重要，这也是MyISAM不适合有大量更新/查询操作的原因
大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。
不过可以通过一些设置来调节MyISAM的调度行为
- 指定启动参数 low-priority-updates 使MyISAN引擎默认给与读请求优先权力
- 执行命名 SET LOW_PRIORITY_UPDATES=1 是连接发出的更新请求优先级降低
- 指定INSERT、UPDATE、DELETE 语句的 LOW_PRIORITY 属性，降低该语句的优先级
虽然上面的三种方法都是要么更新优先、要么查询优先，但还是可以用其来解决查询相对重要的应用（如系统登录）中，读锁等待严重的问题。
另外，MySql也提供了一中折中的方法来调剂读写冲突：
既给系统参数 max_write_lock_count 设置一个合适的值
当一个表的读锁达到这个值后，MySql便暂时将写请求的优先的降低，给读进程一定先获得锁的机会。
4 InnoDB锁
InnoDb与MySIAM最大的不同点在：
- InnoDB支持事务
- InnoDB是航级锁
4.1 事务
一组SQL语句组成的逻辑处理单元，ACID原则
- 原子性（Actomicity）：事务是原子操作，其对数据的修改，要么全部执行成功，要么都执行失败
- 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态，这意味着所有相关的数据规则都必须应用于事务的修改，以保持完整性，事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的
- 隔离性（Isolation）：一个事务所做的修改在最终提交前对其他的事务不可见
- 持久性（Durability）：一旦事务提交，它对于数据的修改会持久化到DB（数据库）
4.2 事务问题
相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多的用户
但并发事务处理也会带来一些问题：
- 更新丢失（Lost Update）当多个事务选择同一行，然后基于最初选定值更新该行时，由于事务隔离性，最后的更新覆盖了前面其他事务所做的更新。例如：两人同时修改同一个文档，然后保存修改后的文档。最后保存其修改文档的人会覆盖另一个人对文档所做的修改；如果在一个人完成修改并提交事务之前，另一个人无法访问同一文件，则可避免此问题。
- 脏读（Dirty Reads）：一个事务正对一条记录做修改，在该事务提交之前，这条记录数据就处于一个不一致的状态，这个时候另一个事务也来读取了这一记录数据，读取到是未提交的数据。例如：公司给一员工涨工资，人事正在数据中更新最新的工资状态，还未提交，这时另一个人正好查询了这个员工的工资情况，这时另一个人查询到数据就是不准确的。这就是脏读；
- 幻读（Phantom Reads）：一个事务按照相同的查询条件重新读取以前检索过的数据，却发现事务插入了满足条件的新数据
- 不可重复读（Non-Repeaable Reads）：一个事务在读取某些数据已经发生了改变
- 或某些记录已经被删除
4.3 事务隔离级别
在并发事务的问题中，“更新丢失”通常应该完成避免的。但是防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。
“脏读”、“幻读”、“不可重复读”其实都是数据库读一致性问题，必须有数据库提供一定的数据隔离机制来解决
数据库实现事务隔离的方式，基本可以分为以下两种
- 在读取数据之前，对其加锁，防止其他事务对数据进行修改
- 不加任何锁，通过一定的机制生成一个数据请求时间点的一致性数据快照，并用这个快照来提供一定级别（语句级或事务级）的一致性读取，从用户的角度，好像是数据库可以提供一个数据的多个版本，因此这种技术叫做数据多版本并发控制（MultiVersion Concurrenct Control,MVCC），也经常称为多版本数据库
数据库的事务隔离级别越严格，并发副作用越小，但付出的代价越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发矛盾”，ANSL SQL定义了4中隔离级别
| 隔离级别/读数据一致性及允许的并发副作用 | 读数据一致性 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|---|
| 未提交（Read uncommitted） | 最低级别，只能保证不读取物理上损坏的数据 | 是 | 是 | 是 |
| 已提交（Read committed） | 语句级 | 否 | 是 | 是 |
| 可重复读（Repeatable read） | 事务级 | 否 | 否 | 是 |
| 可序列化（Serializable） | 最高级别，事务级 | 否 | 否 | 否 |
查看InnoDB行锁竞争情况
SHOW status LIKE 'innodb_row_lock%';
发现“Innodb_row_lock_waits”和“Innodb_row_lock_time_avg”数值比较高，就表示行锁竞争比较严重
4.4 InnoDB行级锁
InnoDB支持两种行锁：共享锁和排他锁
- 共享锁（读锁S）：若事务T对数据对象A加了S(读)锁；则事务T可以读A但不能修改A；其他事务只能对A加S锁，不能加X(写)锁，直到T释放了A上的S锁；这就保证了在A被上锁的时候，其他的事务可以读取A，但是在T释放A的S锁之前，不能对A做任何的修改操作。
- 排他锁（写锁X）：若事务T对数据对象A加X(写)锁；事务T可以读取A也可以修改A；其他的事务在T释放A的X锁之前，不能对A加任何锁；这就保证了其他事务在T释放A上的X锁之前不能读取和修改A
MySQL InnoDB默认行级锁
行级锁都是基于索引的，若一条SQL语句用不到索引是不会使用行级锁的，会使用表锁将整张表锁定
为了允许行/表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁
- 意向共享锁（IS）事务打算给数据行加共享锁：事务在给一个数据行加共享锁前必须先取得该表的IS（意向共享）锁
- 意向排他锁（IX）事务打算给数据行加排他锁：事务在给一个数据行加排他锁前必须先取得该表的IX（意向排他）锁
| 当前锁/是否兼容/请求锁 | X | IX | S | IS |
|---|---|---|---|---|
| X | 冲突 | 冲突 | 冲突 | 冲突 |
| IX | 冲突 | 兼容 | 冲突 | 兼容 |
| S | 冲突 | 冲突 | 兼容 | 兼容 |
| IS | 冲突 | 兼容 | 兼容 | 兼容 |
如果一个事务请求的锁模式与当前锁模式兼容，InnoDB就请求的锁赋予该事务；反之，如果两者不兼容，该事物就要等待锁释放。
意向锁是InnoDB自动加的，不需要用户进行任何干预；
对于UPDATE、DELETE、INSERT语句，InnoDB对自动给涉及的数据集加排它锁（X）；
对于普通的SELECT语句，InnoDB不会加任何锁；
但是对于SELECT语句可以显示的给记录加读(S)/写(X)锁
加读锁（共享锁）
SELECT * FROM tableName WHERE ... lock in share mode;
加写锁（排他锁）
SELECT * FROM tableName WHERE ... FOR UPDATE;
共享索语句主要用在需要数据依存关系时确认某行记录是否存在；
并确保没有人对这个记录UPDATA或DELETE
但如果当前事务也需要对该记录进行更新，则很有可能造成死锁
对于锁定行记录后需要进行更新操作的应用，应该使用排他锁语句
4.5 实例
4.5.1 Innod共享锁
| session_1 | session_2 |
|---|---|
| set autocommit=0,select * from actor where id=1 | set autocommit=0,select * from actor where id=1 |
| session_1对id为1的记录加入共享锁，select * from actor where id=1 lock in share mode | session_2任然可以查询，并对该记录加入select * from actor where id=1 lock in share mode; |
| session_1对锁定的记录进行更新，等待锁UPDATE actor set xx=xx where id=1 | session_2对锁定的记录进行更新，则会导致死锁退出update actor set xx=xxx where id=1; |
| session_1获得锁更新成功 | session_2（Error Code: 2013. Lost connection to MySQL server during query） |
4.5.2 InnoDB排他锁
| session_1 | session_2 |
|---|---|
| set autocommit=0,select * from actor where id=1 | set autocommit=0,select * from actor where id=1 |
| session_1对id为1的记录加入for update排它锁，select * from actor where id-1 for update; | |
| session_2可查询该记录select * from actor where id=1，但是不能在记录共享锁，会等待获取锁select * from actor where id=1 for update | |
| 更新后释放锁 update ..... commit | |
| session_2获得锁，得到其他session提价的记录 |
4.6 行锁的实现
行锁是通过索引上的索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁
- Record Locks：对索引项加锁
- Gap lock：对索引项之间的“间隙”，第一条记录前的“间隙”，后最后一条记录后的“间隙”，加锁
- Next-Key lock：前两种的组合，对记录及其前面的间隙加锁
行锁实现特点意味着：如果不通过索引条件检索数据，那么InnoDB将对表的所有记录进行加锁，将和表锁一样
间隙锁（Next-Key锁）
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据的索引项加锁；
对于键值对在条件范围内但并不存在的记录，叫做“间隙(GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）
举例来说，假如emp表中只有101条记录，其id的值分别为“1，2，3....100，101”；
SELECT * FROM emp WHERE id>100 FOR UPDATE;
Innod不仅会对符合条件的id值为101的记录加锁，也会对id大于101（这些记录并不存在）的“间隙”加锁。
间隙锁的目的
- 防止幻读，以满足相关隔离级别的要求，对于上例，若不使用间隙锁，如果其他事务插入id大于100的任何记录；那么本事务在次执行上面的语句就会出现幻读。
- 满足其恢复和复制的需要，在使用范围条件检索并锁定记录时；Innod这种锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成验证的锁等待，因此在实际的开发之中，尤其是并发插入较多的应用；我们要尽量优化业务逻辑，尽量使用相等条件来访问数据更新。
4.7 when使用表锁
对应Innod，在绝大部分情况下都应该使用行锁，因为事务，行锁往往是我们选择InnoDB的理由，但是个别特殊事务中，也可以考虑使用表锁
- 事务需要更新大部分数据，表又比较大；这时使用默认的行锁，不仅该事物执行效率低(因为需要对较多的行进行加锁，而加锁是需要消耗时间的)；而且有可能造成其他事务长时间等待和锁冲突；这种情况下就可以考虑使用表锁来提高该事务的执行速度
- 事务涉及多个表，较复杂，很可能引起死锁，造成大量事务回滚；这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁，减少数据库因事务回滚带来的开销。
当然，应用中这两种事务不能太多，否则就应该考虑是MyISAM
在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不会给表加锁，事务结束前，不要用UNLICK TABLES释放锁，因为它会隐式的提交事务，COMMIT或ROLLBACK不能释放用LOCK TABLES加的表锁，必须用户UNLOCK TABLES释放表锁。
set sutocommit=0;
加锁
LOCK TABLES tableName WRITE(写锁),tableName READ(读锁),....;
释放锁
UNLOCK TABLES;
5 死锁
MyISAM表锁是deadlock free的，这是因为MyISAM总是一次性获取所需要的全部锁，要么全部满足，要么等待，因此不会出现死锁；
但在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，这就决定了INnoDB发送死锁是可能的；
InnoDB发送死锁后，一般都能自动检测到的，并使一个事务释放锁并回退。另一个事务获得锁，继续完成事务
- 但在涉及外部锁，或涉及锁的情况下InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数“innodb_lock_wait_timeout”来解决，需要说明的是这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获取所需要的锁而挂起，会占用大量的计算机资源，造成验证的问题，甚至拖垮数据库，我们通过设置合适的锁等待超时阈值，可以避免这种情况发送
通常来说死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小、以及访问数据库的SQL语句，绝大部分都是可以避免的。
介绍几种死锁的常见用法 - 应用中，不同的程序会并发存取多个表，尽量约定以相同的顺序访问表
- 程序批处理数据时，事先对数据排序，保证每个线程按固定的顺序来处理记录
- 在事务中要更新记录应直接申请排他锁，而不应该先申请共享锁
- 在可重复读下，如果两个线程同时对相同条件记录用SELECT ... FOR UPDATE加排它锁，在没有符合该记录情况下，如果两个线程都会加锁成功；程序发现记录尚不存在，就试图插入一条新纪录，如果两个线程都这么做了，那么就会出现死锁，这种情况下，将隔离级别改成READ COMMITTED，就可以避免问题
- 当隔离级别为READ COMMITTED时，如果两个线程都先执行SELECT ... FOR UPDATE判断是否存在符合条件的记录，没有 -> 插入记录；此时只有一个线程能插入成功，另一个线程会出现锁等待，当第一个线程提交后，第二个线程会因主键重复抛出异常，虽然这个线程出错了，但获得一排它锁！这个时候如果有第三个线程也来申请排它锁，也会出现死锁，对于这种情况，可以直接做插入操作，随后再捕获主键重复异常 ，或者遇到主键重复错误，总是执行ROLLBACK释放获得的排它锁
如果出现死锁，可以用SHOW INNODB STATUS命令开确定最后一个死锁产生的原因和改进措施。
6 总结
6.1 MyISAM的表锁
- 共享锁之间是兼容的，但共享读锁和排他写锁之间，以及其他排他写锁之间互斥，既读写串行
- 在一定条件下，MyISAM运行查询/插入并发，可利用这一点解决应用中对同一表查询/插入锁的竞争问题
- MyISAM默认的锁调度机制是写优先，这并不一定适合所有应用，用户可已通过设置“LOW_PRIORITY_UPDATES”参数在“INSERT、UPDATE、DELETE”语句中指定“LOW_PRIORITY”选项来调剂读写锁的竞争
- 由于表锁的粒度太大，读写又是串行的，因此如果更新操作比较多，MyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突
6.2 对于InnoDB表
- 行锁基于索引实现，如果不通过索引访问数据，InnoDB会使用表锁
- 间隙锁机制及使用间隙锁的原因
- 不同的隔离级别下，InnoDB的锁机制和一致性读策略不同
- MySQL的恢复和复制对InnoDB锁机制和一致性读策略也有较大影响
- 锁冲突甚至死锁很难完全避免
7 索引与锁
在了解了InnoDB的锁特征之后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁
- 尽量使用较低的隔离级别
- 精心设计索引，并尽量使用索引访问数据，使加锁更加精确，从而减少冲突的机会
7.1 利用索引优化锁
- 索引可以减少锁定的行数，可以更快的完成上锁
- 索引可以加快处理速度，同时也加快了锁的释放
- 选择合理的事务大小，小事务发生冲突的几率也更小
- 给记录集显示加锁时，最好一次请求足够级别的锁，比如修改数据的化，最好直接申请排他锁，而不是下申请共享锁，等待修改了才申请排他锁，这样容易产生死锁
- 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能固定的顺序存取表中的行。这样可以大幅减少死锁的机会
- 尽量用相同的条件访问数据，这样可以避免间隙锁对并发插入的影响
- 不要申请超过实际需要的锁级别，除非必须，查询时不要显示加锁。
- 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能
索引的维护和优化
- 删除重复和冗余的索引
- 创建联合索引
- 更新索引统计信息及减少索引碎片