MySQL事务与锁

事务处理

事务处理是数据库中的一个大块头，涉及到数据的完整性与一致性问题，由于mysql存在多种数据存储引擎提供给用户选择，但不是所有的引擎都支持事务处理，常见的引擎有：MyISAM和InnoDB，MyISAM是默认高速的引擎并不支持事务功能，InnoDB支持行锁定和事务处理，速度比MyISAM稍慢。事实上前面我们在创建表时都指明存储引擎为InnoDB，本篇中我们也将采用InnoDB引擎进行分析，毕竟InnoDB是支持事务功能的。

事务的概念

先看一个经典银行转账案例，A向B的银行卡转账1000元，这里分两个主要事件，一个是A向B转账1000，那么A的银行卡转账成功后必须在原来的数额上扣掉1000元，另一个是B收到了A的转款，B的银行卡上数额必须增加1000元，这两个步骤是必须都成功才算转账成功，总不能A转账B后，A的数额没有变化而B增加了1000元吧？这样银行不得亏死了？因此两个步骤只要有一个失败，此次转账的结果就是失败。但我们在执行sql语句时，两个动作是分两个语句执行的，万一执行完一个突然没电了另外一个没有执行，那岂不出问题了？此时就需要事务来解决这个问题了，所谓的事物就是保证以上的两个步骤在同一个环境中执行，只要其中一个失败，事务就会撤销之前的操作，回滚的没转账前的状态，如果两个都执行成功，那么事务就认为转成成功了。这就是事务的作用。

对事务有了初步理解后，进一步了解事务的官方概念，事务是DBMS的执行单位。它由有限个数据库操作语句组成。但不是任意的数据库操作序列都能成为事务。一般来说，事务是必须满足4个条件（ACID）

原子性（Autmic）：一个原子事务要么完整执行，要么干脆不执行。也就是说，工作单元中的每项任务都必须正确执行，如果有任一任务执行失败，则整个事务就会被终止并且此前对数据所作的任何修改都将被撤销。如果所有任务都被成功执行，事务就会被提交，那么对数据所作的修改将会是永久性的

一致性（Consistency）：一致性代表了底层数据存储的完整性。它是由事务系统和应用开发人员共同来保证。事务系统通过保证事务的原子性，隔离性和持久性来满足这一要求; 应用开发人员则需要保证数据库有适当的约束(主键，引用完整性等)，并且工作单元中所实现的业务逻辑不会导致数据的不一致(数据预期所表达的现实业务情况不相一致)。例如，在刚才的AB转账过程中，从A账户中扣除的金额必须与B账户中存入的金额相等。

隔离性（Isolation）：隔离性是指事务必须在不干扰其他事务的前提下独立执行，也就是说，在事务执行完毕之前，其所访问的数据不能受系统其他部分的影响。

持久性（Durability）：持久性指明当系统或介质发生故障时，确保已提交事务的更新数据不能丢失，也就意味着一旦事务提交，DBMS保证它对数据库中数据的改变应该是永久性的，耐得住任何系统故障，持久性可以通过数据库备份和恢复来保证。

事务控制流程实战

在使用事务处理可能涉及到以下命令：

-- 声明事务的开始
BEGIN(或START TRANSACTION);

-- 提交整个事务
COMMIT;

-- 回滚到事务初始状态
ROLLBACK;

下面通过删除user表中的用户数据，然后再回滚来演示上述命令的作用：

-- 先查看user表中的数据
mysql> select * from user;
+----+-----------+--------------+---------------------+------+--------------------+
| id | name      | pinyin       | birth               | sex  | address            |
+----+-----------+--------------+---------------------+------+--------------------+
|  1 | 王五      | wangwu       | NULL                | 1    | 北京市朝阳区       |
|  2 | 张曹宇    | zhangcaoyu   | 1990-08-05 00:00:00 | 1    | 广东省汕头市       |
|  3 | 李达康    | lidakang     | 1909-12-12 00:00:00 | 1    | 北京朝阳           |
| 10 | 张书记    | zhangshuji   | 1999-06-06 00:00:00 | 0    | 北京市朝阳区       |
| 16 | 任在明    | renzaiming   | 1996-12-01 00:00:00 | 1    | 广东省广州市       |
| 22 | 陈小明    | chenxiaoming | 1995-05-10 00:00:00 | 0    | 广东省深圳市       |
| 24 | 任传海    | renchuanhai  | 1992-03-08 00:00:00 | 1    | 海南三亚           |
+----+-----------+--------------+---------------------+------+--------------------+
7 rows in set (0.00 sec);

-- 开始事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec);
-- 删除ID为24的用户
mysql> delete from user where id =24;
Query OK, 1 row affected (0.00 sec);

-- 删除完成后再次查看user表数据，显然ID为24的数据已被删除
mysql> select * from user;
+----+-----------+--------------+---------------------+------+--------------------+
| id | name      | pinyin       | birth               | sex  | address            |
+----+-----------+--------------+---------------------+------+--------------------+
|  1 | 王五      | wangwu       | NULL                | 1    | 北京市朝阳区       |
|  2 | 张曹宇    | zhangcaoyu   | 1990-08-05 00:00:00 | 1    | 广东省汕头市       |
|  3 | 李达康    | lidakang     | 1909-12-12 00:00:00 | 1    | 北京朝阳           |
| 10 | 张书记    | zhangshuji   | 1999-06-06 00:00:00 | 0    | 北京市朝阳区       |
| 16 | 任在明    | renzaiming   | 1996-12-01 00:00:00 | 1    | 广东省广州市       |
| 22 | 陈小明    | chenxiaoming | 1995-05-10 00:00:00 | 0    | 广东省深圳市       |
+----+-----------+--------------+---------------------+------+--------------------+
6 rows in set (0.00 sec);

-- 执行回滚操作rollback
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

-- 再次查看数据，可见ID为24的用户数据已恢复
mysql> select * from user;
+----+-----------+--------------+---------------------+------+--------------------+
| id | name      | pinyin       | birth               | sex  | address            |
+----+-----------+--------------+---------------------+------+--------------------+
|  1 | 王五      | wangwu       | NULL                | 1    | 北京市朝阳区       |
|  2 | 张曹宇    | zhangcaoyu   | 1990-08-05 00:00:00 | 1    | 广东省汕头市       |
|  3 | 李达康    | lidakang     | 1909-12-12 00:00:00 | 1    | 北京朝阳           |
| 10 | 张书记    | zhangshuji   | 1999-06-06 00:00:00 | 0    | 北京市朝阳区       |
| 16 | 任在明    | renzaiming   | 1996-12-01 00:00:00 | 1    | 广东省广州市       |
| 22 | 陈小明    | chenxiaoming | 1995-05-10 00:00:00 | 0    | 广东省深圳市       |
| 24 | 任传海    | renchuanhai  | 1992-03-08 00:00:00 | 1    | 海南三亚           |
+----+-----------+--------------+---------------------+------+--------------------+
7 rows in set (0.00 sec)

从上述一系列操作中，从启动事务到删除用户数据，再到回滚数据，体现了事务控制的过程，这里我们还没使用COMMIT，如果刚才把rollback改成commit，那么事务就提交了，数据也就真的删除了。下面我们再次来演示删除数据的过程，并且这次使用commit提交事务.

-- 先添加一条要删除数据
mysql> insert into user values(30,'要被删除的数据',null,null,1,null);
Query OK, 1 row affected (0.01 sec);
-- 查看数据
mysql> select * from user;
+----+-----------------------+--------------+---------------------+------+--------------------+
| id | name                  | pinyin       | birth               | sex  | address            |
+----+-----------------------+--------------+---------------------+------+--------------------+
|  1 | 王五                  | wangwu       | NULL                | 1    | 北京市朝阳区       |
|  2 | 张曹宇                | zhangcaoyu   | 1990-08-05 00:00:00 | 1    | 广东省汕头市       |
|  3 | 李达康                | lidakang     | 1909-12-12 00:00:00 | 1    | 北京朝阳           |
| 10 | 张书记                | zhangshuji   | 1999-06-06 00:00:00 | 0    | 北京市朝阳区       |
| 16 | 任在明                | renzaiming   | 1996-12-01 00:00:00 | 1    | 广东省广州市       |
| 22 | 陈小明                | chenxiaoming | 1995-05-10 00:00:00 | 0    | 广东省深圳市       |
| 24 | 任传海                | renchuanhai  | 1992-03-08 00:00:00 | 1    | 海南三亚           |
| 30 | 要被删除的数据        | NULL         | NULL                | 1    | NULL               |
+----+-----------------------+--------------+---------------------+------+--------------------+
8 rows in set (0.00 sec);

-- 开启新事务
mysql> begin;
Query OK, 1 row affected (0.00 sec);
-- 删除数据
mysql> delete from user where id =30;
Query OK, 1 row affected (0.00 sec);
-- 提交事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec);
-- 回滚数据
mysql> rollback;
Query OK, 0 rows affected (0.00 sec);
-- 查看数据
mysql> select * from user;
+----+-----------+--------------+---------------------+------+--------------------+
| id | name      | pinyin       | birth               | sex  | address            |
+----+-----------+--------------+---------------------+------+--------------------+
|  1 | 王五      | wangwu       | NULL                | 1    | 北京市朝阳区       |
|  2 | 张曹宇    | zhangcaoyu   | 1990-08-05 00:00:00 | 1    | 广东省汕头市       |
|  3 | 李达康    | lidakang     | 1909-12-12 00:00:00 | 1    | 北京朝阳           |
| 10 | 张书记    | zhangshuji   | 1999-06-06 00:00:00 | 0    | 北京市朝阳区       |
| 16 | 任在明    | renzaiming   | 1996-12-01 00:00:00 | 1    | 广东省广州市       |
| 22 | 陈小明    | chenxiaoming | 1995-05-10 00:00:00 | 0    | 广东省深圳市       |
| 24 | 任传海    | renchuanhai  | 1992-03-08 00:00:00 | 1    | 海南三亚           |
+----+-----------+--------------+---------------------+------+--------------------+
7 rows in set (0.00 sec)

可以发现当删除完数据后，使用commit提交了事务，此时数据就会被真正更新到数据库了，即使使用rollback回滚也是没有办法恢复数据的。ok~，这就是事务控制最简化的流程，事实上除了上述的回滚到事务的初始状态外，还可以进行部分回滚，也就是我们可以自己控制事务发生错误时回滚到某个点，这需要利用以下命令来执行：

- 定义保存点（回滚点）
SAVEPOINT savepoint_name（名称）;

--回滚到指定保存点
ROLLBACK TO SAVEPOINT savepoint_name（名称）;

演示案例如下：

-- 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into user values(31,'保存点1',null,null,1,null);
Query OK, 1 row affected (0.00 sec);
-- 创建保存点
mysql> savepoint sp;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into user values(32,'保存点2',null,null,1,null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into user values(33,'保存点3',null,null,1,null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into user values(34,'保存点4',null,null,1,null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+------------+--------------+---------------------+------+--------------------+
| id | name       | pinyin       | birth               | sex  | address            |
+----+------------+--------------+---------------------+------+--------------------+
|  1 | 王五       | wangwu       | NULL                | 1    | 北京市朝阳区       |
|  2 | 张曹宇     | zhangcaoyu   | 1990-08-05 00:00:00 | 1    | 广东省汕头市       |
|  3 | 李达康     | lidakang     | 1909-12-12 00:00:00 | 1    | 北京朝阳           |
| 10 | 张书记     | zhangshuji   | 1999-06-06 00:00:00 | 0    | 北京市朝阳区       |
| 16 | 任在明     | renzaiming   | 1996-12-01 00:00:00 | 1    | 广东省广州市       |
| 22 | 陈小明     | chenxiaoming | 1995-05-10 00:00:00 | 0    | 广东省深圳市       |
| 24 | 任传海     | renchuanhai  | 1992-03-08 00:00:00 | 1    | 海南三亚           |
| 31 | 保存点1    | NULL         | NULL                | 1    | NULL               |
| 32 | 保存点2    | NULL         | NULL                | 1    | NULL               |
| 33 | 保存点3    | NULL         | NULL                | 1    | NULL               |
| 34 | 保存点4    | NULL         | NULL                | 1    | NULL               |
+----+------------+--------------+---------------------+------+--------------------+
11 rows in set (0.00 sec);
-- 回滚到保存点
mysql> rollback to savepoint sp;
Query OK, 0 rows affected (0.00 sec);
-- 查看数据
mysql> select * from user;
+----+------------+--------------+---------------------+------+--------------------+
| id | name       | pinyin       | birth               | sex  | address            |
+----+------------+--------------+---------------------+------+--------------------+
|  1 | 王五       | wangwu       | NULL                | 1    | 北京市朝阳区       |
|  2 | 张曹宇     | zhangcaoyu   | 1990-08-05 00:00:00 | 1    | 广东省汕头市       |
|  3 | 李达康     | lidakang     | 1909-12-12 00:00:00 | 1    | 北京朝阳           |
| 10 | 张书记     | zhangshuji   | 1999-06-06 00:00:00 | 0    | 北京市朝阳区       |
| 16 | 任在明     | renzaiming   | 1996-12-01 00:00:00 | 1    | 广东省广州市       |
| 22 | 陈小明     | chenxiaoming | 1995-05-10 00:00:00 | 0    | 广东省深圳市       |
| 24 | 任传海     | renchuanhai  | 1992-03-08 00:00:00 | 1    | 海南三亚           |
| 31 | 保存点1    | NULL         | NULL                | 1    | NULL               |
+----+------------+--------------+---------------------+------+--------------------+
8 rows in set (0.00 sec);
-- 提交事务
mysql> commit ;

关于commit有点需要知道的，在mysql中每条sql命令都会被自动commit，这种功能称为自动提交功能，是默认开启的。前面我们在执行事务使用了begin命令开启了事务，这时自动提交在事务中就关闭了直到事务被手动commit。当然我们也可以手动控制开启或者关闭此功能，语法如下：

- 关闭自动提交功能
SET AUTOCOMMIT=0;
-- 开启自动提交功能
SET AUTOCOMMIT=1;

事务隔离级别（分离水平）

在并发事务处理带来的问题中，更新丢失通常应该是完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。
脏读、不可重复读和幻读，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式，基本可以分为以下两种。

一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。
另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（ＭultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。
数据库的事务隔离级别越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的，同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。
为了解决“隔离”与“并发”的矛盾，ISO/ANSI SQL92定义了４个事务隔离级别，每个级别的隔离程度不同，允许出现的副作用也不同，应用可以根据自己业务逻辑要求，通过选择不同的隔离级别来平衡＂隔离＂与＂并发＂的矛盾
事务４种隔离级别比较

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

四种分离水平(隔离级别)

READ_UNCOMMITTED：这是事务最低的分离水平(隔离级别)，它充许别外一个事务可以看到这个事务未提交的数据，会出现脏读、不可重复读、幻读（分离水平最低，并发性能高）

READ_COMMITTED：保证一个事务修改的数据提交后才能被另外一个事务读取。另外一个事务不能读取该事务未提交的数据。可以避免脏读，但会出现不可重复读、幻读问题（锁定正在读取的行，mysql默认隔离级别）

REPEATABLE_READ：可以防止脏读、不可重复读，但会出幻读（锁定所读取的所有行）

SERIALIZABLE：这是花费最高代价但是最可靠的事务分离水平(隔离级别)，事务被处理为顺序执行。保证所有的情况不会发生（锁表,并发性及其低）

读未提交、不可重复读，幻读

读未提交，也称脏读，脏读发生在一个事务读取了另一个事务改写但尚未提交的数据时。如果改写在稍后被回滚了，那么第一个事务获取的数据就是无效的。

不可重复读：不可重复读发生在一个事务执行相同的查询两次或两次以上，但是每次都得到不同的数据时。这通常是因为另一个并发事务在两次查询期间进行了更新。请注意，不可重复读重点是修改数据导致的(修改数据时排他读)，例如：在事务1中，客户管理人员在读取了张曹宇的生日为1990-08-05,操作并没有完成

select birth from user where name ='张曹宇' ;

在事务2中，这时张曹宇自己修改生日为1990-06-05,并提交了事务.

begin;
-- 其他操作省略
update user set birth='1990-06-05' where  name ='张曹宇' ;
commit;

在事务1中，客户管理人员再次读取了张曹宇的生日时，生日变为1990-06-05,从而导致在一个事务中前后两次读取的结果并不一致，导致了不可重复读。

幻读：幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录。请注意，幻读重点是插入或者删除数据导致的（对满足条件的数据行集进行锁定），同样的道理，在事务1中，客户管理查询所有用户生日在1990-06-05的人只有20个，操作并没有完成，此时事务2中，刚好有一个新注册的用户，其生日也1990-06-05，在事务2中插入新用户并提交了事务，此时在事务1中再次查询时，所有用户生日在1990-06-05的人变为21个了，从也就导致了幻读。

在理解了读未提交、不可重复的、幻读后，再次看回表格，小结一下，可以发现在分离水平为READ UNCOMMITTED时，将会导致3种情况的出现，因此这样的分离水平一般是不建议使用的。在分离水平为READ COMMITTED时，不会导致脏读，但会导致不可重复读和幻读，要回避这样的现象，必须采用分离水平为REPEATABLE READ，这样就只会导致幻读，而当分离水平为SERIALIZABLE时，3种现象都不复存在。但请注意这并不意味着所有情况下采用分离水平为SERIALIZABLE都是合理的，就如前面所分析的分离水平越高，数据的完整性也就越高，但同时运行性下降。在大多数情况下，我们会在根据应用的实际情景选择分离水平为REPEATABLE READ或者READ COMMITTED(MySQL默认的事务分离水平为REPEATABLE READ)，这样既能一定程度上保证数据的完整性也同时提供了数据的同时运行性，在mysql中我们可以使用以下语法设置事务分离水平

-- 设置当前连接的事务分离水平
SET SESSION TRANSACTION ISOLATION LEVEL 事务分离水平;

--设置全部连接（包括新连接）的事务分离水平
SET GLOBAL TRANSACTION ISOLATION LEVEL 事务分离水平;

事务原理概要

最后我们来简单了解一下事务内部实现的原理概要，事实上事务的处理机制是通过记录更新日志而实现的，其中与事务处理相关的日志是UNDO日志和REDO日志。

UNDO日志亦称为回滚端，在进行数据插入、更新、删除的情景下，保存变更前的数据，原理图如下：

image.png

在表中保存了指向UNDO日志的指针，rollback执行时根据这个指针来获取旧数据并覆盖到表中，rollback执行完成后或者commit后UNDO日志将被删除。UNDO还有另外一种作用，当A用户正在更新数据时，还没提交，而B用户也需要使用该数据，这时不可能让B读取未提交的数据，因此会将存在UNDO表中的数据提供给B用户。这就是事务回滚的简单模型。

REDO日志主要是事务提交后由于错误或者断电停机等原因使数据无法更新到数据库中时，REDO日志将提供数据恢复作用。其原理是通过数据库中的一段缓冲的数据先实时更新到REDO日志再更新到数据库，也就是说平常的更新操作并非一步执行到位的，而是首选更新到REDO日志中，再更新到数据库文件的。所以REDO日志才能用户故障数据的恢复。

MySQL中的锁（表锁、行锁）

锁是计算机协调多个进程或纯线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。
MySQL大致可归纳为以下3种锁：

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

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

悲观锁和乐观锁的概念

悲观锁：假设会发生并发冲突，回避一切可能违反数据完整性的操作。

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性，注意乐观锁并不能解决脏读的问题(关于脏读稍后解析)。

在一般情况下，悲观锁依靠数据库的锁机制实现，以保证操作最大程度的排他性和独占性，因而会导致数据库性能的大量开销和并发性很低，特别是对长事务而言，这种开销往往过于巨大而无法承受。为了解决这样的问题，乐观锁机制便出现了。乐观锁，大多情况下是基于数据版本（ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个version 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则给予更新，否则认为是过期数据。

mysql中的共享锁与排他锁

在mysql中，为了保证数据一致性和防止数据处理冲突，引入了加锁和解锁的技术，这样可以使数据库中特定的数据在使用时不让其他用户(进程或事务)操作而为该数据加锁，直到该数据被处理完成后再进行解锁。根据使用目的不同把锁分为共享锁定(也称为读取锁定)和排他锁定(写入锁定)。InnoDB行锁的实现就是依靠共享锁和排他锁。

共享锁（s）：允许一个事务去读一行，将对象数据变为只读形式的锁定，这样就允许多方同时读取一个数据，此时数据将无法修改（阻止其他事务获得相同数据集的排他锁）。
排他锁（Ｘ）：允许获取排他锁的事务更新(insert/update/delete)数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。

以上两种锁都属于悲观锁的应用，还有一点，根据锁定粒度的不同，可分为行锁定(共享锁和排他锁使用应用的就是行锁定)，表锁定，数据库锁定，可见粒度的不同将影响用户(进程或事务)对数据操作的并发性，目前mysql支持行锁定和表锁定。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，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行锁是通过索引上的索引项来实现的，这一点ＭySQL与Oracle不同，后者是通过在数据中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味者：只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁！
在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

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

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

当然，应用中这两种事务不能太多，否则，就应该考虑使用ＭyISAＭ表。

例如，如果需要写表t1并从表t读，可以按如下做：

SET AUTOCOMMIT=0;
LOCAK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and here];
COMMIT;
UNLOCK TABLES;

锁释放时机

image.png

在事务执行过程中，如果有加锁操作，这个锁需要等事务提交或者回滚时释放。

时间线	事务1 (T1)	事务2(T2)
t1	BEGIN;	BEGIN;
t2	UPDATE lockdemo SET state = '666' WHERE id = 2;
t3		UPDATE lockdemo SET state = '22' WHERE id = 2;
t..	SELECT * FROM lockdemo;	SELECT * FROM lockdemo;
t..	commit;	commit;

事务1在t2时刻先执行更新操作，它就会一直持有id=2的锁直到commit；事务2在t3时刻获取会失败。

BEGIN;
UPDATE lockdemo SET state = '666' WHERE id = 2;
SELECT * FROM lockdemo;
COMMIT;

BEGIN;
UPDATE lockdemo SET state = '22' WHERE id = 2;
SELECT * FROM lockdemo;
COMMIT;

死锁

死锁是指两个或多个事务在同一个资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象.当多个事务试图以不同的顺序锁定资源时,就可能会产生死锁,多个事务同时锁定同一个资源时,也会产生死锁。
例如:
事务1:

start transaction;
update stock_price set close = 45.50 where stock_id = 4 and date = '2017-4-26';
update stock_price set close = 19.80 where stock_id = 3 and date = '2017-4-27';
commit;

事务2:

start transaction;
update stock_price set high = 20.10 where stock_id = 3 and date = '2017-4-27';
update stock_price set high = 47.20 where stock_id = 4 and date = '2017-4-26';
commit;

如果凑巧,两个事务均执行了第一条update语句,同时锁定了该资源,当尝试执行第二条update语句的时候,去发现资源已经被锁定,两个事务都等待对方释放锁,则陷入死循环,形成死锁。
为了解决这种问题,数据库系统实现了各种死锁检测和死锁超时机制.比如InnoDB存储引擎目前的处理方法是将持有最少级排他锁的事务进行回滚.

问题

这些问题我还没有时间证明
1.是不是所有的sql操作都需要事务？（我个人理解从事务的定义上来说不需要）
2.是不是只有在事务里才可能用到行锁和表锁（我个人理解不是，理由是事务的定义）

参考书籍

https://blog.csdn.net/javazejian/article/details/69857949
https://www.cnblogs.com/chenqionghe/p/4845693.html

最后编辑于：2019.01.18 11:11:58

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 212,185评论 6赞 493
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 90,445评论 3赞 385
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 157,684评论 0赞 348
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 56,564评论 1赞 284
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 65,681评论 6赞 386
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 49,874评论 1赞 290
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,025评论 3赞 408
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 37,761评论 0赞 268
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,217评论 1赞 303
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,545评论 2赞 327
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,694评论 1赞 341
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,351评论 4赞 332
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,988评论 3赞 315
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,778评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,007评论 1赞 266
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 46,427评论 2赞 360
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 43,580评论 2赞 349