mysql的锁机制(读锁，写锁，表锁，行锁，悲观锁，乐观锁，间隙锁)

读锁和写锁

介绍

MyISAM表锁中的读锁和写锁

1. 读锁（共享锁S）： 对同一个数据，多个读操作可以同时进行，互不干扰。加锁的会话只能对此表进行读操作,其他会话也只能进行读操作。MyISAM的读默认是加读锁。

2. 写锁（互斥锁X）： 如果当前写操作没有完毕，则无法进行其他的读操作、写操作。当前会话只能对此表进行读,写操作,其他会话无法进行任何操作。MyISAM的DML默认加写锁

InnoDB行锁中的读锁和写锁

1. 读锁（共享锁S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。InnoDB通过使用lock in share mode加读锁，但是注意只锁覆盖索引

2. 写锁（互斥锁X）：允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。InnoDB所有的DML操作默认加写锁。select可以通过for update加写锁，并且会锁住所有索引，不仅仅是索引覆盖的索引。

   注意：

   对于共享锁大家可能很好理解，就是多个事务只能读数据不能改数据。对于排他锁大家的理解可能就有些差别，我当初就犯了一个错误，以为排他锁锁住一行数据后，其他事务就不能读取和修改该行数据，其实不是这样的。排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。mysql InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。但是注意在MyISAM引擎中因为读也会自动加上读锁，所以使用排它锁的时候是不能去读的,这点要和InnoDB区别开

此外，InnoDB对于共享锁和互斥锁，还拥有另外的两个相关锁

操作(以表锁为例子)

加锁：locak table 表1 read/write ,表2 read/write ,...

释放锁(所有)：unlock tables ;

查看哪些表加了锁：show open tables ; 1代表被加了锁

加读锁

##会话0加读锁
lock table  tablelock read ;
#操作
select * from tablelock; --读（查），可以
delete from tablelock where id =1 ; --写（增删改），不可以

select * from emp ; --读，不可以(emp为非锁表)
delete from emp where eid = 1; --写，不可以

#会话1(非加锁会话)
select * from tablelock;   --读（查），可以
delete from tablelock where id =1 ; --写，会“等待”会话0将锁释放

select * from emp ;  --读（查），可以 (emp为非锁表)
delete from emp where eno = 1; --写，可以

结论：

当前会话(会话0):

一个会话给A表加了锁，则当前会话只能对A表进行读操作；

其他会话(会话1)：

1. 可以对其他表（A表以外的表）进行读、写操作

2. 对A表：读-可以； 写-需要等待释放锁。

加写锁

#会话0加写锁
lock table tablelock write ;
#其他大体与读锁相似，这里直接讲结论

结论:

当前会话（会话0）:

1. 可以对加了写锁的表 进行任何操作（增删改查）；

2. 但是不能 操作（增删改查）其他表

其他会话(会话1)：

   对会话0中加写锁的表 可以进行增删改查的前提是：等待会话0释放写锁

行锁和表锁和页面锁

介绍

1. 表锁 ：一次性对一张表整体加锁，是MySQL各存储引擎中最大颗粒度的锁定机制。如MyISAM存储引擎使用表锁，开销小、实现逻辑非常简单，带来的系统负面影响最小，加锁和释放锁快；无死锁；但锁的范围大，容易发生锁冲突，并发度低。在MyISAM中,因为不支持事务,因此只能用lock和unlock控制.InnoDB中，表锁还可以通过事务控制

2. 行锁 ：一次性对一条数据加锁，锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。如InnoDB存储引擎使用行锁，开销大，加锁慢；容易出现死锁；锁的范围较小，不易发生锁冲突，并发度高（很小概 率 发生高并发问题：脏读、幻读、不可重复度、丢失更新等问题）针对事务(commit和rollbcak)。注意当索引失效或者压根就没有索引时，会怎么办呢？我看过好多文章，都是说会变为表锁，但实际并不是这样，其实还是行锁。具体请看这里

3. 页面锁：锁定粒度界于表锁和行锁之间；开销和加锁时间界于表锁和行锁之间；会出现死锁；并发度一般 。使用页级锁定的主要是BerkeleyDB存储引擎。

   从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度 来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有 并发查询的应用，如一些在线事务处理（OLTP）系统。

MyISAM的表锁

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（DML）前，会自动给涉及的表加写锁。

所以对MyISAM表进行操作，会有以下情况：

     a、对MyISAM表的读操作（加读锁），不会阻塞其他进程（会话）对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。

      b、对MyISAM表的写操作（加写锁），会阻塞其他进程（会话）对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

1. 操作

加锁：locak table 表1 read/write ,表2 read/write ,...

释放锁(所有)：unlock tables ;

查看哪些表加了锁：show open tables ; 1代表被加了锁

分析表锁定的严重程度： show status like ‘Table_locks%’ ;

               Table_locks_immediate :即可能获取到的锁数,即立刻能加锁的表数

               Table_locks_waited：需要等待的表锁数(如果该值越大，说明存在越大的 锁竞争)

            一般建议：

​                      Table_locks_immediate/Table_locks_waited > 5000， 建议采用InnoDB引擎，否则MyISAM引擎

2. 注意别名问题

#对temp表进行加读锁，temp有两个字段（id,tid）
lock table temp read;
#通过别名对temp表进行查询操作
select t.* from temp  t where t.id=1;

报错:ERROR 1100 (HY000): Table ‘t’ was not locked with LOCK TABLES

#加锁的时候加上别名
lock table temp as t read;
#还是同样的查询操作
select t.* from temp  t where t.id=1;

±-----±-----+
| id | tid |
±-----±-----+
| 1 | 2 |
±-----±-----+
1 row in set (0.05 sec)

#注意此刻继续执行这个查询语句,原来的名字没用了
select * from temp;

ERROR 1100 (HY000): Table ‘temp’ was not locked with LOCK TABLES

3.锁的调度问题

MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。那么，一个进程请求某个 MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后 到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节MyISAM 的调度行为。

• 通过指定启动参数low-priority-updates(默认是off/0)，使MyISAM引擎默认给予读请求以优先的权利。
• 通过执行命令SET LOW_PRIORITY_UPDATES=1/on，使该连接发出的更新请求优先级降低。
• 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

虽然上面3种方法都是要么更新优先，要么查询优先的方法，但还是可以用其来解决查询相对重要的应用（如用户登录系统）中，读锁等待严重的问题。
另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

上面已经讨论了写优先调度机制带来的问题和解决办法。这里还要强调一点：一些需要长时间运行的查询操作，也会使写进程“饿死”！因此，应用中应尽量避免出现长时间运行的查询操作，不要总想用一条SELECT语 句来解决问题，因为这种看似巧妙的SQL语句，往往比较复杂，执行时间较长，在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”，使每 一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行。

中间表的一个实例

4.高并发问题

MyISAM表的读和写是串行的，但这是就总体而言的。 当对同一个表进行查询和插入操作时，为了降低锁竞争的频率，在一定条件下，MyISAM表也支持查询和插入操作的并发进行。根据concurrent_insert的设置，MyISAM是可以并行处理查询和插入的： MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。

• 当concurrent_insert设置为NEVER(0)时，不允许并发插入。

• 当concurrent_insert设置为AUTO(1)时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。

• 当concurrent_insert设置为ALWAYS(2)时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

我们来查看一下默认的的concurrent_insert

±------------------±------+
| Variable_name | Value |
±------------------±------+
| concurrent_insert | AUTO |
±------------------±------+

综合来看，concurrent_insert=2是绝对推荐的，至于max_write_lock_count=1和low-priority-updates=1，则视情况而定，如果可以降低写操作的优先级，则使用low-priority-updates=1，否则使用 max_write_lock_count=1。

InnoDB的行锁

InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION),并且事务是默认自动进行提交的(可修改autocommit变量)；二是采用了行级锁。行级锁与表级锁本来就有许多不同之处，另外，事务的引入也带来了一些新问题。

这里注意一个问题，和锁没什么关系，可以跳过：InnoDB没设置主键使用隐式ROW_ID

1. 查看行锁的争用情况

show status like 'innodb_row_lock%';

±------------------------------±------+
| Variable_name | Value |
±------------------------------±------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
±------------------------------±------+

如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，还可以通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。

2. 事务（Transaction）及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性。

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

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

     eg：有3个人进行转账操作，为了保证一致性（即3个人 的账号金额总数不变），那在我写代码的时候，如果写了代码：A=A-5000;此时数据时不一致的。那就必须要写上，B=B+5000，或者是C=C+5000，这样的代码才能保证了数据库的一致性状态。

• 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。具体看下面的几个隔离级别和并发问题。

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

3. 并发事务带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况。

• 脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”。简单来说就是读取了未提交的数据。
• 不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了！这种现象叫做“不可重复读”。简单来说就是两次读取时数据不一样。
• 幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。简单来说就是两次读取的数据总数不一样。
• 更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题——最后的更新覆盖了其他事务所做的更新。例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改保存其更改副本的编辑人员覆盖另一个编辑人员所做的修改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题。一般有两类：回滚丢失和覆盖丢失(具体看下方链接)

脏读、不可重复读、幻读三个问题都是由事务A对数据进行修改、增加，事务B总是在做读操作。如果两事务都在对数据进行修改则会导致更新丢失。

脏读，不可重复度，幻读

更新丢失

4.事务的隔离级别

在并发事务处理带来的问题中，“更新丢失”通常应该是完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。

“脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

1. 读未提交（READ UNCOMMITTED）

2. 读提交 （READ COMMITTED）（oracle默认的隔离级别）

3. 可重复读 （REPEATABLE READ）(mysql默认的隔离级别)

4. 串行化 （SERIALIZABLE）

从上往下，隔离强度逐渐增强，性能逐渐变差。

隔离级别	脏读	不可重复读	幻读
读未提交	可能	可能	可能
读提交	不可能	可能	可能
可重复读	不可能	不可能	可能
串行化	不可能	不可能	不可能

命令	作用
SHOW GLOBAL VARIABLES LIKE ‘tx_isolation’;	查看当前全局的事务隔离级别
select @@global.tx_isolation;	使用系统变量查询当前全局事务隔离级别
select @@session.tx_isolation;	使用系统变量查询当前会话的事务隔离级别
set global tx_isolation=‘REPEATABLE-READ’;	设置当前的全局隔离级别
set session tx_isolation=‘REPEATABLE-READ’;	设置当前会话的隔离级别

读未提交

MySQL 事务隔离其实是依靠锁来实现的，加锁自然会带来性能的损失。而读未提交隔离级别是不加锁的，所以它的性能是最好的，没有加锁、解锁带来的性能开销。但有利就有弊，它连脏读的问题都没办法解决。

读已提交

既然读未提交没办法解决脏数据问题，那么就有了读提交。读提交就是一个事务只能读到其他事务已经提交过的数据，也就是其他事务调用 commit 命令之后的数据。那脏数据问题迎刃而解了。

读提交事务隔离级别是大多数流行数据库的默认事务隔离界别，比如 Oracle，但是不是 MySQL 的默认隔离界别。

每个 select 语句都有自己的一份快照，而不是一个事务一份，所以在不同的时刻，查询出来的数据可能是不一致的。

读提交解决了脏读的问题，但是无法做到可重复读，也没办法解决幻读。

可重复读

可重复是对比不可重复而言的，上面说不可重复读是指同一事物不同时刻读到的数据值可能不一致。而可重复读是指，事务不会读到其他事务对已有数据的修改，及时其他事务已提交，也就是说，事务开始时读到的已有数据是什么，在事务提交前的任意时刻，这些数据的值都是一样的。但是，对于其他事务新插入的数据是可以读到的，这也就引发了幻读问题。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2YbksJWM-1588322570343)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20200327151901383.png)]

只针对已有行的修改有效，当出现插入和删除时，还是会出现幻读。看下图：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WmtId7ZP-1588322570351)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20200327151959789.png)]

要说明的是，当你在 MySQL 中测试幻读的时候，并不会出现上图的结果，幻读并没有发生，MySQL 的可重复读隔离级别其实解决了幻读问题

mysql解决不可重复读和幻读的措施:

• 为了解决不可重复读，或者为了实现可重复读，MySQL 采用了 MVVC (多版本并发控制) 的方式，并且该种方式也解决了一定的幻读。

我们在数据库表中看到的一行记录可能实际上有多个版本，每个版本的记录除了有数据本身外，还要有一个表示版本的字段，记为 row trx_id，而这个字段就是使其产生的事务的 id，事务 ID 记为 transaction id，它在事务开始的时候向事务系统申请，按时间先后顺序递增。

按照上面这张图理解，一行记录现在有 3 个版本，每一个版本都记录这使其产生的事务 ID，比如事务A的transaction id 是100，那么版本1的row trx_id 就是 100，同理版本2和版本3。

在上面介绍读提交和可重复读的时候都提到了一个词，叫做快照，学名叫做一致性视图，这也是可重复读和不可重复读的关键，可重复读是在事务开始的时候生成一个当前事务全局性的快照，而读已提交则是每次执行语句的时候都重新生成一次快照。

对于一个快照来说，它能够读到那些版本数据，要遵循以下规则：

1. 当前事务内的更新，可以读到；
2. 版本未提交，不能读到；
3. 版本已提交，但是却在快照创建后提交的，不能读到；
4. 版本已提交，且是在快照创建前提交的，可以读到；

利用上面的规则，再返回去套用到读提交和可重复读的那两张图上就很清晰了。还是要强调，两者主要的区别就是在快照的创建上，可重复读仅在事务开始是创建一次，而读提交每次执行语句的时候都要重新创建一次。

• 前面的MVVC只能控制部分的幻读。细想这一种情况：A会话进行更新操作，执行 update testInno set name =“jiang” where id>10;。在提交之前B会话执行insert into testInno value(13,“dsa”);并且commit，然后A会话再进行一次更新会发生什么？

  如果没有解决幻读，那么结果是两次更新返回的的改变行数是不一样的！

  针对这种情况引入了Next-Key锁。

  通过Next-Key锁，使得第一次update后会直接锁住>10这个区间，使得在当前事务内不可能出现幻读。

串行化

串行化是4种事务隔离级别中隔离效果最好的，解决了脏读、可重复读、幻读的问题，但是效果最差，它将事务的执行变为顺序执行，与其他三个隔离级别相比，它就相当于单线程，后一个事务的执行必须等待前一个事务结束。

实现:读的时候加共享锁，也就是其他事务可以并发读，但是不能写。写的时候加排它锁，其他事务不能并发写也不能并发读。

5. 并发写问题

并发写这里先引出两个概念，快照读，当前读

快照读：就是从当前版本的快照中进行读取，在mysql的可重复读隔离级别下，其他事务所有的DML操作都不会影响此种               读法的结果。

#简单的查询
select * from table;

当前读：读取的是记录数据的最新版本，并且当前读返回的记录都会加上锁，保证其他事务不会再并发的修改这条记录

select * from table for update 
select * from table lock in share mode
#所有的DML操作
insert into table value(1,"dsa");
delete from table where id=1;
update table set name="dsa" where id=1;

以update的过程为例，首先会执行当前读，然后把返回的数据加锁，之后执行update。加锁是防止别的事务在这个时候对这条记录做什么，默认加的是排他锁，这样就可以保证数据不会出错了。当然依然会出现更新丢失的问题。

6. 无索引问题

加锁的过程要分有索引和无索引两种情况，比如下面这条语句

update user set age=11 where id = 1

id 是这张表的主键，是有索引的情况，那么 MySQL 直接就在索引数中找到了这行数据，然后干净利落的加上行锁就可以了。

而下面这条语句

update user set age=11 where age=10

表中并没有为 age 字段设置索引，所以， MySQL 无法直接定位到这行数据。那怎么办呢，当然也不是加表锁了。MySQL 会为这张表中所有行加行锁，没错，是所有行。但是呢，在加上行锁后，MySQL 会进行一遍过滤，发现不满足的行就释放锁，最终只留下符合条件的行。虽然最终只为符合条件的行加了锁，但是这一锁一释放的过程对性能也是影响极大的。所以，如果是大表的话，建议合理设计索引，如果真的出现这种情况，那很难保证并发度。

7. 意向共享锁和意向排它锁

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

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

InnoDB行锁模式兼容性列表:

	S	IS	X	IX
S	兼容	兼容	冲突	冲突
IS	兼容	兼容	冲突	兼容
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就请求的锁授予该事务；反之，如果两者两者不兼容，该事务就要等待锁释放。
意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁。
事务可以通过以下语句显式给记录集加共享锁或排他锁：

• 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
• 排他锁（X）：SELECT * FROM table_name WHERE ... FOR UPDATE。

用SELECT ... LOCK IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁。

具体关于共享锁和排他锁的内容，可以参考间隙锁，这里就不多加赘述

8.什么时候使用表锁

对于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;

9.关于死锁

ＭyISAM表锁是deadlock free的，这是因为ＭyISAM总是一次性获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但是在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，这就决定了InnoDB发生死锁是可能的。

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

通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小、以及访问数据库的SQL语句，绝大部分都可以避免。下面就通过实例来介绍几种死锁的常用方法。

（１）在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序为访问表，这样可以大大降低产生死锁的机会。如果两个session访问两个表的顺序不同，发生死锁的机会就非常高！但如果以相同的顺序来访问，死锁就可能避免。

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

（３）在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应该先申请共享锁，更新时再申请排他锁，甚至死锁。

（４）在REPEATEABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁，在没有符合该记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可以避免问题。

（５）当隔离级别为READ COMMITED时，如果两个线程都先执行SELECT…FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第１个线程提交后，第２个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第３个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行ROLLBACK释放获得的排他锁。

尽管通过上面的设计和优化等措施，可以大减少死锁，但死锁很难完全避免。因此，在程序设计中总是捕获并处理死锁异常是一个很好的编程习惯。

如果出现死锁，可以用SHOW INNODB STATUS命令来确定最后一个死锁产生的原因和改进措施。

对于表锁和行锁的总结

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

对于InnoDB表，本文主要讨论了以下几项内容：
（1）InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁。
（2）介绍了InnoDB间隙锁（Next-key)机制，以及InnoDB使用间隙锁的原因。
在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。

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

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

悲观锁和乐观锁

1. 乐观锁不是数据库自带的，需要我们自己去实现。乐观锁是指操作数据库时(更新操作)，想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。

   • CAS

     CAS是一种乐观锁实现方式，顾名思义就是先比较后更新。在对一个数据进行更新前，先持有对这个数据原有值的备份。比如，要将a=2更新为a=3，在进行更新前会比较此刻a是否为2.如果是2，才会进行更新操作。当多个线程尝试使用CAS同时更新一个变量时，只有一个线程能够成功，其余都是失败。失败的线程不会被挂起，而是被告知这次竞争失败，并且可以再次尝试。
     比如前面的扣减库存问题，乐观锁方式实现如下：

     //查询出商品库存信息，quantity = 3
     select quantity from items where id=1
     //修改商品库存为2
     update items set quantity=2 where id=1 and quantity = 3;
     

     在更新之前，先查询库存表中当前库存数，然后在做update时，以库存数作为一个修改条件。当进行提交更新的时候，判断数据库的当前库存数与第一次取出来的库存数进行比对，相等则更新，否则认为是过期数据。
     但是这种更新存在一个比较严重的问题，即ABA问题。

   • ABA问题

     A线程去除库存数3，B线程取出库存数3，B线程先将库存数变为2，又将库存数变为3，A线程在进行更新操作时发现库存是仍然是3，然后操作成功。尽管A线程操作是成功的，但是不能代表这个过程就是没问题的

   • 增加版本字段

     解决ABA问题的一个方法是通过一个顺序递增的version字段，这也是实现乐观锁的常用方式

     通常实现是这样的：在表中的数据进行操作时(更新)，先给数据表加一个版本(version)字段，每操作一次，将那条记录的版本号加1。也就是先查询出那条记录，获取出version字段,如果要对那条记录进行操作(更新),则先判断此刻version的值是否与刚刚查询出来时的version的值相等，如果相等，则说明这段期间，没有其他程序对其进行操作，则可以执行更新，将version字段的值加1；如果更新时发现此刻的version值与刚刚获取出来的version的值不相等，则说明这段期间已经有其他程序对其进行操作了，则不进行更新操作。

     //查询出商品信息，version = 1
     select version from items where id=1
     //修改商品库存为2
     update items set quantity=2,version = 2 where id=1 and version = 1;
     

   除了自己手动实现乐观锁之外，现在网上许多框架已经封装好了乐观锁的实现，如hibernate，需要时，可能自行搜索"hiberate 乐观锁"试试看。

2. 与乐观锁相对应的就是悲观锁了。悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

#开始事务
start transaction
#查询出商品库存信息
select quantity from items where id=1 for update;
#修改商品库存为2
update items set quantity=2 where id = 1;
#提交事务
commit;

间隙锁(我这里不单单说间隙锁，所得是next-key)

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（GAP LOCK），间隙锁和行锁合称Next-Key Lock。间隙锁（Gap Lock）是Innodb在[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AmhU34Ao-1588322570356)(https://math.jianshu.com/math?formula=%5Ccolor%7Bred%7D%7B%E5%8F%AF%E9%87%8D%E5%A4%8D%E8%AF%BB%7D)]提交下为了解决幻读问题时引入的锁机制，并不是所有事务隔离级别都使用间隙锁。

看下方例子

#创建表
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

原则和优化：

原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。

原则 2：查找过程中访问到的对象才会加锁。

优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

案例一：等值查询间隙锁

• 根据原则 1，加锁单位是 next-key lock，session A 加锁范围就是 (5,10]；

• 同时根据优化 2，这是一个等值查询 (id=7)，而 id=10 不满足查询条件，next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)。

案例二：非唯一索引等值锁

• 根据原则1，优化2，锁的范围是(0,5],(5,10)，但是根据原则2，只有访问到的对象才加锁，这个查询使用了覆盖索引，并不访问主键索引，所以主键上没加锁。
• 需要注意，在这个例子中，lock in share mode 只锁覆盖索引，但是如果是 for update 就不一样了。 执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。

案例三：主键索引范围锁

• 开始执行的时候，要找到第一个 id=10 的行，因此本该是 next-key lock(5,10]。 根据优化 1， 主键 id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁。
• 范围查找就往后继续找，找到 id=15 这一行停下来，因此需要加 next-key lock(10,15]。首次 session A 定位查找 id=10 的行的时候，是当做等值查询来判断的，而向右扫描到 id=15 的时候，用的是范围查询判断

案例四：非唯一索引范围锁

这次 session A 用字段 c 来判断，加锁规则跟案例三唯一的不同是：在第一次用 c=10 定位记录的时候，索引 c 上加了 (5,10] 这个 next-key lock 后，由于索引 c 是非唯一索引，没有优化规则，也就是说不会蜕变为行锁，因此最终 sesion A 加的锁是，索引 c 上的 (5,10] 和 (10,15] 这两个 next-key lock。

案例五：唯一索引范围锁 bug

• session A 是一个范围查询，按照原则 1 的话，应该是索引 id 上只加 (10,15] 这个 next-key lock，并且因为 id 是唯一键，所以循环判断到 id=15 这一行就应该停止了。
• 但是实现上，InnoDB 会往前扫描到第一个不满足条件的行为止，也就是 id=20。而且由于这是个范围扫描，因此索引 id 上的 (15,20] 这个 next-key lock 也会被锁上。改成select * from t where id>10 and id<15 for update;则只会加new-key lock(10,15]

案例六：非唯一索引上存在"等值"的例子
表中插入一条mysql> insert into t values(30,10,30);

这时，session A 在遍历的时候，先访问第一个 c=10 的记录。同样地，根据原则 1，这里加的是 (c=5,id=5) 到 (c=10,id=10) 这个 next-key lock。
然后，session A 向右查找，直到碰到 (c=15,id=15) 这一行，循环才结束。根据优化 2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成 (c=10,id=10) 到 (c=15,id=15) 的间隙锁。

案例七：limit 语句加锁

上面的数据增加一行（30,10,30），这样在数据库中存在的c=10的就有两条记录

索引 c 上的加锁范围变成了从（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间，如下图所示：

案例八：一个死锁的例子

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VrzshAtt-1588322570381)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20200327195428797.png)]

• ession A 启动事务后执行查询语句加 lock in share mode，在索引 c 上加了 next-key lock(5,10] 和间隙锁 (10,15)；
• session B 的 update 语句也要在索引 c 上加 next-key lock(5,10] ，进入锁等待；
• 然后 session A 要再插入 (8,8,8) 这一行，被 session B 的间隙锁锁住。由于出现了死锁，InnoDB 让 session B 回滚。
  session B的行锁c=10和session A有冲突，所以死锁

在读提交情况下，语句执行过程中加上的行锁，在语句执行完成后，就要把“不满足条件的行”上的行锁直接释放了，不需要等到事务提交。

费解的问题

• 由于是 order by c desc，第一个要定位的是索引 c 上“最右边的”c=20 的行，所以会加上间隙锁 (20,25]和 next-key lock (15,20]。
• 在索引 c 上向左遍历，要扫描到 c=10 才停下来，所以 next-key lock 会加到 (5,10]，这正是阻塞 session B 的 insert 语句的原因。
• 在扫描过程中，c=20、c=15、c=10 这三行都存在值，由于是 select *，所以会在主键 id 上加三个行锁。

参考：

https://blog.csdn.net/sherry_y_fan/article/details/80548560

https://blog.csdn.net/u014590757/article/details/79612858