mysql锁机制隔离级别_mysql的锁机制、事务和隔离级别详解

一、mysql的锁机制

mysql的锁机制用来实现mysql的并发访问控制的，mysql的锁类型可以分为如下几种：

1、锁的操作模式

根据执行操作时施加锁的模式，可以将锁分为读锁和写锁。

读锁：用户在读的时候施加该锁，不允许其他用户和当前用户执行写操作，即当前用户和其他用户的写操作处于阻塞状态。但是仍然允许其他用户和当前执行读操作，因此，读锁也称为共享锁。

写锁：用户在执行写操作时施加该锁，不允许其他用户执行读和写操作，即其他用户的读写操作将会处于阻塞状态。当前用户也不可以执行读操作，即当前用户的读操作处于阻塞状态(可以在当前会发下执行读操作，全局级别不能执行读操作)。因此，写锁也称为独占锁或排它锁。

2、锁的粒度

根据锁的粒度，可以将锁分为表锁和行锁。

表锁：即对某个表施加锁，该锁可以为读锁和写锁。

行锁：即对某个行或某些行施加锁，该锁可以分为读锁和写锁。

需要说明的是，锁的粒度越大，其并发性越差，但是系统开销越小；反之，锁的粒度越细，其并发性越好，但是系统开销越大。

举例说明：当用户A在对表tb1执行写操作时，如果用户A对tb1整张表施加锁的话，那么其他用户就无法访问表tb1了，就更别谈修改表tb1中的某些字段了。但是如果用户A在对表tb1只施加了行锁(如只对自己要修改的行加上行锁)，那么其他用户仍然可以访问这个表中的其他行(除了施加行锁的行除外)，也可以修改其他行的数据，因此，在这种情况下，行锁的并发性比表锁的并发性要好的多，但是给系统带来的开销就比较大。

3、锁的实现位置

根据锁的实现位置可以将锁分为mysql锁和存储引擎锁。

mysql锁：即mysql服务器级别的锁，该锁可以手动使用，手动使用的锁称为显式锁。

存储引擎锁：即存储引擎操作时施加的锁，该锁自动施加的，因此该所也称为隐式锁。

需要说明的是，mysql的锁机制一般都是系统自动进行的，当然也可以手动施加锁。

手动施加锁的命令

加锁命令：LOCK  TABLE  tb_name {READ|WRIITE};

解锁命令：UNLOCK TABLES；

二、mysql中的事务机制

事务是DBMS中的执行单位，它是有限的数据库操作序列组成的。但是并不是所有的数据库操作序列都能成为事务。简单的说就是由一组查询语句组成，这些查询语句将组成一个不隔分隔的独立单元。

事务的ACID属性

一般说来，事务具有如下4个特性(ACID特性)：

1、原子性(Automicity)：该特性引起的数据库操作"要么全部执行，要么全部不执行"。

2、一致性(Consistency)：该特性表示数据库操作之前和操作之后的最终状态是一致的。比如，两个用户a,b之间相互转账，但是最终两个用户的总金额是不变的。

3、隔离性(Isolation)：一个事务所做的修改在提交前对其他事务是不可见的。

4、持久性(Durability)：一旦事务提交执行成功，则系统保证在任何故障下，所做的修改永久生效。 事务的持久×××需要借助事务日志来完成。内存中的操作首先同步到事务日志中，再由后台进程将事务日志中的数据同步到磁盘数据文件中。由于事务日志的数据是顺序IO操作，而数据文件则是随机IO操作，因此有了事务日志性能更佳。

mysql中的脏读、不可重复读和幻读的概念

脏读：一个事务读取另一个事务未提交的数据。在Read-Uncommitted隔离级别下，当前事务中可以读到其他事务修改且还没有提交的数据。

不可重复读(non-repeatable reads)：一个事务读取到另一个事务提交的更新数据。在某个事务进程下，多次读取另一个事务下的相同数据，由于在另一个事务下修改了改数据，导致在当前事务下多次读取该数据的结果不一样，这就是不可重复读现象。比如：事务A第一次读取表tb的第2行name字段为'hello'，由于事务B修改了表tb的第2行name字段为'good'，因此当事务B提交时，导致事务A第二次读取表tb的第2行name字段为'good'。注意：读取的一定要是和之前一样的字段，且该字段每次被读取时不一样，由于数据不一样，因此读取的数据当然是不重复的，这就是所谓的不可重复现象。

幻读：一个事务读取到另一个事务已经提交的新插入数据。

一个事务多次读取另一个事务下的数据的时，由于另一个事务新增了一行或是删除了一行，当另一个事务提交时，导致在第一个事务下每次读取的数据不一样，这种数据的不一样，主要体现在读取的数据新增了几行或者减少了几行，因此给人的感觉就是想幻像一样。比如：在事务A下第一次读取表tb的内容为5行，由于事务B删除了tb表中的一行，当事务B提交时，导致事务A第二次读取表tb的内容为4行。因此，给人的感觉就是刚刚明明是5行，现在怎么变成4行了，这种现象向幻像一样，因此叫做幻读。

不可重复读和幻读的区别

不可重复读强调的是每次读取的是相同位置的数据，且该数据在另一个事务下被修改。注重的是修改。这个位置指的是哪一行、哪一个字段的数据。

幻读强调的是第二次读比第一次读取时，内容多了或者少了几行，注重的是新增和删除。

mysql处理事务有2种方法

1、手动执行事务

mysql>begin; 或mysql>start  transaction;   ##表示启动事务

mysql>rollback；                           ##撤销之前所做的修改(回滚)

mysql>commit;                              ##提交事务

2、设置事务的自动提交模式

##查看当前事务是否是自动提交的。0或off表示关闭自动提交；1或on表示开启自动提交。默认自动提交功能开启。

mysql>select @@autocommit;

set  {global|session} autocommit=0    ##关闭自动提交功能

set  {global|session} autocommit=1    ##开启自动提交功能

建议：明确使用事务，关闭自动提交功能。

事务的执行状态：共有5种状态

1、active：表示当前事务正在进行当中

2、部分提交的：表示语句在执行过程中，由于某种原因(如宕机)导致只执行了一部分。因此事务就处于该状态下。

3、失败：表示事务没有执行成功。

4、终止：由于事务执行失败，因此系统会终止该事务

5、提交成功：表示提交后，事务执行成功。

三、mysql的隔离级别详解

mysql中隔离级别分为4种：

1、READ-UNCOMMITTED：读未提交。该隔离级别下的当前事务可以看到其他未提交事务的执行结果。在该级别下会出现脏读、不可重复读、幻读现象。如有两个事务进程A和B，在事务A和B都开启的情况下，当事务A修改了某些数据时，即使事务A没有被提交，则事务B仍然可以看到被修改的数据，因此会出现脏读现象。当事务A进行事务回滚时，则事务B此时看到的数据就和此前不一样的，两次看到的结果不一样，那么就会出现不可重复读现象。在READ-UNCOMMITTED隔离级别下，也会出现幻读的场景，即事务B在提交前和提交后，查看到的内容是不一致的。

下面用一个例子来说明一下READ-UNCOMMITTED隔离级别的特性：

首先设置隔离级别为READ-UNCOMMITTED，默认为REPEATABLE-READ。

mysql> set global tx_isoaltion="READ-UNCOMMITTED";

启动两个新的会话窗口(全局变量生效时间需要在新建会话上)，并启动两个事务。这里及其后续所有的例子都以表teachers来说明，该表的内容如下：

mysql> select * from teachers;

+-----+---------------+-----+--------+

| TID | Name          | Age | Gender |

+-----+---------------+-----+--------+

|   1 | Song Jiang    |  45 | M      |

|   2 | Zhang Sanfeng |  94 | M      |

|   3 | Miejue Shitai |  77 | F      |

|   4 | Lin Chaoying  |  93 | F      |

+-----+---------------+-----+--------+

4 rows in set (0.00 sec)

启动事务A，并对表teachers进行修改操作

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

修改第4行的TID为100

mysql> update teachers set TID=100 where TID=4;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

启动事务B，并在事务B下查看teachers表的内容

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from teachers;

+-----+---------------+-----+--------+

| TID | Name          | Age | Gender |

+-----+---------------+-----+--------+

|   1 | Song Jiang    |  45 | M      |

|   2 | Zhang Sanfeng |  94 | M      |

|   3 | Miejue Shitai |  77 | F      |

| 100 | Lin Chaoying  |  93 | F      |

+-----+---------------+-----+--------+

4 rows in set (0.00 sec)

从上面显示的内容可以看出，事务B可以看到事务A没有提交且被修改的数据，这就是所谓的脏读。

此时第4行的TID字段为100。

当事务A回滚时，此时事务A的内容为：

mysql> rollback;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from teachers;

+-----+---------------+-----+--------+

| TID | Name          | Age | Gender |

+-----+---------------+-----+--------+

|   1 | Song Jiang    |  45 | M      |

|   2 | Zhang Sanfeng |  94 | M      |

|   3 | Miejue Shitai |  77 | F      |

|   4 | Lin Chaoying  |  93 | F      |

+-----+---------------+-----+--------+

4 rows in set (0.00 sec)

进行回滚操作时，即之前所有的修改都无效，此时表teachers的内容仍然不变。

在事务B进程下，再次查看表teachers的内容

mysql> select * from teachers;

+-----+---------------+-----+--------+

| TID | Name          | Age | Gender |

+-----+---------------+-----+--------+

|   1 | Song Jiang    |  45 | M      |

|   2 | Zhang Sanfeng |  94 | M      |

|   3 | Miejue Shitai |  77 | F      |

|   4 | Lin Chaoying  |  93 | F      |

+-----+---------------+-----+--------+

4 rows in set (0.01 sec)

因此，此时在事务B下，两次查看到第4行的TID是不一样的，因此会出现不可重复读的现象。

2、READ-COMMITED：读提交，只有事务在提交后才可以读到被修改的数据。READ-COMMITED解决了脏读的问题。在该隔离级别下，当前事务只能看到其他事务提交后的执行结果。该隔离级别支持不可重复读、幻读。大多数数据库的的默认隔离级别为Read-Committed,但是mysql不是的。

建议，当对事务不是特别严格的场景下，使用READ-COMMITTED这种隔离级别性能更好。

下面仍然以一个例子来说明READ-COMMITTED隔离级别的特性

首先仍然设置下隔离级别为READ-COMMITTED

mysql> set global tx_isolation='READ-COMMITTED';

Query OK, 0 rows affected (0.00 sec)

启动两个新的会话窗口，并启动两个事务。

启动事务A

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

修改表teachers中的第4行TID字段为200

mysql> update teachers set TID=200 where TID=4;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

修改后的结果

mysql> select * from teachers;

+-----+---------------+-----+--------+

| TID | Name          | Age | Gender |

+-----+---------------+-----+--------+

|   1 | Song Jiang    |  45 | M      |

|   2 | Zhang Sanfeng |  94 | M      |

|   3 | Miejue Shitai |  77 | F      |

| 200 | Lin Chaoying  |  93 | F      |

+-----+---------------+-----+--------+

4 rows in set (0.00 sec)

注意：此时事务A尚未提交，因此，在事务B中，是无法看到被修改的数据的。

启动事务B

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

在事务B进程下，查看表teachers的内容

mysql> select * from teachers;

+-----+---------------+-----+--------+

| TID | Name          | Age | Gender |

+-----+---------------+-----+--------+

|   1 | Song Jiang    |  45 | M      |

|   2 | Zhang Sanfeng |  94 | M      |

|   3 | Miejue Shitai |  77 | F      |

|   4 | Lin Chaoying  |  93 | F      |

+-----+---------------+-----+--------+

4 rows in set (0.00 sec)

结果显示的仍然是原来的内容，因为事务A还没有提交，因此在事务B下，看不到被修改的数据。

当事务A提交时

mysql> commit;

Query OK, 0 rows affected (0.01 sec)

在事务B下，再次查看表teachers的内容

mysql> select * from teachers;

+-----+---------------+-----+--------+

| TID | Name          | Age | Gender |

+-----+---------------+-----+--------+

|   1 | Song Jiang    |  45 | M      |

|   2 | Zhang Sanfeng |  94 | M      |

|   3 | Miejue Shitai |  77 | F      |

| 200 | Lin Chaoying  |  93 | F      |

+-----+---------------+-----+--------+

4 rows in set (0.00 sec)

会发现，两次读到第4行TID的内容不一致，这就是所谓的不可重复现象。

3、Repeatable-Read：重复读。解决了不可重复读的问题，但是仍然会出现幻读现象。在该级别且当前事务没有提交的前提下，不管其他事务提交的数据如何被修改，每次查询的结果都是一样的。一旦当前事务提交，则查询的结果就是最新的结果。在该级别下，为了提高并发能力，需要借助MVCC(多版本并发控制)机制来完成。这是mysql的默认隔离级别。

下面以一个例子来说明REPEATABLE-READ隔离级别的特性

首选设置隔离级别为REPEATABLE-READ

mysql> SET GLOBAL TX_ISOLATION="REPEATABLE-READ";

Query OK, 0 rows affected (0.00 sec)

启动两个新的会话窗口，并启动两个事务。

启动事务A

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

修改表teachers中的第4行数据

mysql> update teachers set TID=300 where TID=4;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

修改后的内容为：

mysql> select * from teachers;

+-----+---------------+-----+--------+

| TID | Name          | Age | Gender |

+-----+---------------+-----+--------+

|   1 | Song Jiang    |  45 | M      |

|   2 | Zhang Sanfeng |  94 | M      |

|   3 | Miejue Shitai |  77 | F      |

| 300 | Lin Chaoying  |  93 | F      |

+-----+---------------+-----+--------+

4 rows in set (0.00 sec)

启动事务B

mysql> start transaction;

Query OK, 0 rows affected (0.01 sec)

查看表teacher的内容

mysql> select * from teachers;

+-----+---------------+-----+--------+

| TID | Name          | Age | Gender |

+-----+---------------+-----+--------+

|   1 | Song Jiang    |  45 | M      |

|   2 | Zhang Sanfeng |  94 | M      |

|   3 | Miejue Shitai |  77 | F      |

|   4 | Lin Chaoying  |  93 | F      |

+-----+---------------+-----+--------+

4 rows in set (0.00 sec)

此时显示仍然是表teachers的原有内容。

提交事务A

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

在事务B下，再次查看表teachers的内容

mysql> select * from teachers;

+-----+---------------+-----+--------+

| TID | Name          | Age | Gender |

+-----+---------------+-----+--------+

|   1 | Song Jiang    |  45 | M      |

|   2 | Zhang Sanfeng |  94 | M      |

|   3 | Miejue Shitai |  77 | F      |

|   4 | Lin Chaoying  |  93 | F      |

+-----+---------------+-----+--------+

4 rows in set (0.00 sec)

此时显示仍然还是表teachers的原有内容。

接着，提交事务B

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

最后，查看提交后teachers表中的数据

mysql> select * from teachers;

+-----+---------------+-----+--------+

| TID | Name          | Age | Gender |

+-----+---------------+-----+--------+

|   1 | Song Jiang    |  45 | M      |

|   2 | Zhang Sanfeng |  94 | M      |

|   3 | Miejue Shitai |  77 | F      |

| 300 | Lin Chaoying  |  93 | F      |

+-----+---------------+-----+--------+

4 rows in set (0.01 sec)

此处，当事务B提交后，就可以查看表teachers的最终数据了。

在REPEATABLE-READ隔离级别下，只有当相关联的所有事务提交后，才可以看到最终的数据。(这里的相关联的事务指的是跟该张表操作相关的事务)

4、SERIALIZABLE ：可串行化，就是事务接着一个一个的串行执行，只有同一个实例下的其他事务结束后，才可以执行同一个实例下的另一个事务。这样就解决了幻读的问题。但是这种隔离级别的并发性不好，当某个事务没有提交时，其他事务仍然处于阻塞状态。SERIALIZABLE解决了幻读现象。

下面以一个例子来说明REPEATABLE-READ隔离级别的特性

首选设置隔离级别为SERIALIZABLE

mysql> SET GLOBAL TX_ISOLATION="SERIALIZABLE";

Query OK, 0 rows affected (0.00 sec)

启动两个新的会话窗口，并启动两个事务。

启动事务A

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

修改表teachers中的第4行数据

mysql> update teachers set TID=400 where TID=4;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

查看修改后的数据

mysql> select * from teachers;

+-----+---------------+-----+--------+

| TID | Name          | Age | Gender |

+-----+---------------+-----+--------+

|   1 | Song Jiang    |  45 | M      |

|   2 | Zhang Sanfeng |  94 | M      |

|   3 | Miejue Shitai |  77 | F      |

| 400 | Lin Chaoying  |  93 | F      |

+-----+---------------+-----+--------+

4 rows in set (0.00 sec)

此时事务A没有提交，那么如果在开启其他的事务时，其他事务是无法访问teachers表的。

启动事务B

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

查看表teachers的内容

mysql> select * from teachers;

执行查询操作后，你会发现此时事务B是处于阻塞状态的，只有当事务A提交后，则事务B才可以被执行。

提交事务A

mysql> commit;

Query OK, 0 rows affected (0.03 sec)

然后再在事务B下，查看表teachers的内容

mysql> select * from teachers;

+-----+---------------+-----+--------+

| TID | Name          | Age | Gender |

+-----+---------------+-----+--------+

|   1 | Song Jiang    |  45 | M      |

|   2 | Zhang Sanfeng |  94 | M      |

|   3 | Miejue Shitai |  77 | F      |

| 400 | Lin Chaoying  |  93 | F      |

+-----+---------------+-----+--------+

4 rows in set (0.00 sec)

当事务A提交后，此时事务B就可以发起查询操作了。由此看出，SERIALIZABLE隔离级别虽然隔离性能比较好，但是并发能力太差。

总结：mysql的四种默认隔离级别所出现的读取方式：

1、READ-COMMITTED：会出现脏读、不可重复读、幻读等现象。

2、READ-COMMITED：会出现不可重复现象、幻读。

3、ReEPEATABLE-READ：会出现幻读现象。

4、SERIALIZABLE：不会出现脏读、不可重复读、幻读等现象。

由此可见，隔离级别越高，所受到的干扰越小，消耗系统的资源越多。且最重要的是它们的并发性能越差。

mysql中事务隔离级别的设定

mysql>select @@tx_isolation;        ##显示当前的隔离级别

mysql>set   [global}session] tx_isolation='VALUE'    ##用来设定隔离级别