数据库——MySQL——事务

数据的事务是指作为单个逻辑工作单元执行的一系列操作，要么完全执行，要么完全不执行。

事务必须具备四个特性：

  • 原子性
    • 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚
  • 一致性
    • 在事务T开始时，此时数据库有一种状态，这个状态是所有的MySQL对象处于一致的状态，例如数据库完整性约束正确，日志状态一致等，当事务T提交后，这时数据库又有了一个新的状态，不同的数据，不同的索引，不同的日志等，但此时，约束，数据，索引，日志等MySQL各种对象还是要保持一致性（正确性）。 这就是 从一个一致性的状态，变到另一个一致性的状态。也就是事务执行后，并没有破坏数据库的完整性约束（一切都是对的）。
  • 隔离性
    • 隔离性是指当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。换句话来说就是每个事务都感觉不到有其他事务在并发地执行。
  • 持久性
    • 持久性是指一个事务一旦被提交了，那么对于数据库中的数据改变就是永久性的。

MySQL使用事务

提交事务

>set autocommit = 0 禁止自动提交
>start transaction;
>update accout set money=money+100 where name="Jason";
>commit;

回滚事务

>set autocommit = 0 禁止自动提交
>start transaction;
>update account set money=money-100 where name="justin";
>rollback;

关于事务隔离

如果没有隔离会发生这样几个问题：

脏读

一个事务处理过程里读取了另一个未提及的的事务中的数据。

不可重复读

对于数据库中的某个数据，一个事务范围内多次查询却可以返回不同的数据值，这是由于在查询的间隔期间，另外一个事务修改并提交了该数据。

幻读

在一个事务中读取到了别的事务插入的数据，导致前后不一样。比如：第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

关于隔离级别分为四个等级

查看当前事务级别

select @@tx_isolation;

修改事务的隔离级别

语法

set  [global | session]  transaction isolation level 隔离级别名称;

 例如：

set global transaction isolation level Repeatable read;
# 修改全局的之后要重新建立会话才有效

 隔离级别：Serializable | Repeatable read | Read committed |Read uncommitted

注意：
设置默认级别是指当前session的下一个事务
设置session级别是指当前session以后的所有事务
设置global级别是指对之后的所有session，不包括当前session

推荐博客：http://blog.chinaunix.net/uid-14010457-id-3956842.html

 MySQL中的锁机制

数据库为了保证四个特性，特别是一致性和隔离性，采用了加锁的方式。由于数据库是一个高并发的应用，同一时间有大量的并发访问。如果加锁过度的话，会极大地降低并发处理能力，所以对于加锁的处理，是数据库对于事务处理的精髓所在。

锁方案

一次封锁

因为有大量的并发访问，为了预防死锁，一般应用中采用的是一次封锁的方案：就是在方法的开始阶段，已经预先知道需要用到那些数据，然后全部锁住，在方法执行之后，再全部解锁。
这种方案可以有效避免死锁发生，当时由于数据库操作在事务开始阶段并不知道具体会用到哪些数据，所以该方案不合适在数据库中使用。

两段锁

两段锁协议将事务分成两个阶段：加锁阶段和解锁阶段

  • 加锁阶段：在该阶段可以进行加锁操作。读数据前需要申请获取S锁（共享锁：其他事务可以继续加共享锁，但不能加排他锁）；写数据前需要申请获取X锁（排他锁：其他事务不能获取任何锁）。加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。

  • 解锁阶段：当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能进行加锁操作。

注：这个方案无法避免死锁，但是可以保证事务调度的串行化（串行化在数据库恢复和备份时候很重要）。

MySQL的锁类型

表锁

对一整张表加锁，并发能力低下（即使有分读锁、写锁），一般在DDL处理时使用

innodb中的锁

InnoDB是一个支持行锁的存储引擎，锁的类型有：共享锁（S）、排他锁（X）、意向共享（IS）、意向排他（IX）。为了提供更好的并发，InnoDB提供了非锁定读：不需要等待访问行上的锁释放，读取行的一个快照。该方法是通过InnoDB的一个特性：MVCC来实现的。
innodb有三种行锁的算法：
  • 行锁（record lock）：单个记录上锁。
  • 间隙锁（又叫GAP锁或者Gap Lock）：锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。
  • Next-Key锁（）：行锁和间隙锁的结合体。锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

下图时三个锁的位置关系图：

 

行锁

只锁住特定行的数据，并发能力强，MySQL一般都是用行锁来处理并发事务。

如果用到无索引的字段，那么MySQL会在存储引擎层面将所有的记录加锁，然后由MySQL Server过滤，如果不满足会调用unlock_row把不满足条件的记录释放锁（这里违背了二段锁协议）。
这种情况同样适用于MySQL的默认隔离级别RR。所以对一个数据量很大的表做批量修改的时候，如果无法使用相应的索引，MySQL Server过滤数据的的时候特别慢，就会出现虽然没有修改某些行的数据，但是它们还是被锁住了的现象。

GAP锁和Next-Key锁

# -----------------会话1---------------------------------------------------
mysql> create table t(id int,name varchar(10),key idx_id(id),primary key(name))engine =innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t values(1,'a'),(3,'c'),(5,'e'),(8,'g'),(11,'j');   
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select @@global.tx_isolation, @@tx_isolation;    
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set, 2 warnings (0.01 sec)

mysql> select * from t;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    3 | c    |
|    5 | e    |
|    8 | g    |
|   11 | j    |
+------+------+
5 rows in set (0.01 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t where id=8;
Query OK, 1 row affected (0.00 sec)
"""
可以看到最后加了一个事务，对id=8的数据进行处理，然而没有提交
"""



# -----------------会话2---------------------------------------------------
mysql> select @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set, 2 warnings (0.00 sec)

mysql> start transaction; 
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t(id,name) values(6,'f');
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into t(id,name) values(5,'e1');
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into t(id,name) values(7,'h');
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into t(id,name) values(8,'gg');
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into t(id,name) values(9,'k');
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into t(id,name) values(10,'p');
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into t(id,name) values(11,'iz');
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into t(id,name) values(5,'cz');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t(id,name) values(11,'ja');
Query OK, 1 row affected (0.00 sec)
"""
分析：因为会话1已经对id=8的记录加了一个X锁，由于是RR隔离级别，INNODB要防止幻读需要加GAP锁：即id=5（8的左边），id=11（8的右边）之间需要加间隙锁（GAP）。
这样[5,e]和[8,g]，[8,g]和[11,j]之间的数据都要被锁。上面测试已经验证了这一点，根据索引的有序性，数据按照主键（name）排序，后面写入的[5,cz]（[5,e]的左边）
和[11,ja]（[11,j]的右边）不属于上面的范围从而可以写入。

另外一种情况，把name主键去掉会是怎么样的情况？有兴趣的可以测试一下。
"""
例1

上面的例子中，当插入被会话1锁住的内容的时候，会有一个超时间，我用ctrl+c强制终止了。如果是在一个事务中，插入数据的时候超时了，会怎么办呢。

超时时间的参数：innodb_lock_wait_timeout ，默认是50秒。
超时是否回滚参数：innodb_rollback_on_timeout 默认是OFF。

 在默认请求下，不会因为超时引发的异常而回滚，当参数innodb_rollback_on_timeout设置为ON时，则会。

 接下来再看一个例子

# -----------------会话1---------------------------------------------------
mysql> create table tt(a int primary key)engine =innodb;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into tt values(1),(3),(5),(8),(11);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    3 | c    |
|    5 | e    |
|   11 | j    |
+------+------+
4 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tt where a = 8 for update;
+---+
| a |
+---+
| 8 |
+---+
1 row in set (0.00 sec)
# -----------------会话2---------------------------------------------------
mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tt values(6);
Query OK, 1 row affected (0.01 sec)

mysql> insert into tt values(7);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt values(9);
Query OK, 1 row affected (0.00 sec)
例2

 到这里应该会有些人有问题了，为啥这个例2和例1不一样。

 解释：

因为InnoDB对于行的查询都是采用了Next-Key Lock的算法，锁定的不是单个值，而是一个范围，按照这个方法是会和第一次测试结果一样。但是，当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。

注意：通过主键或则唯一索引来锁定不存在的值，也会产生GAP锁定。

 

注

如果不想出现那种阻塞的现象，可以显示的关闭GAP锁

1：把事务隔离级别改成：Read Committed，提交读、不可重复读。SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2：修改参数：innodb_locks_unsafe_for_binlog 设置为1。

注：innodb_locks_unsafe_for_binlog最主要的作用就是控制innodb是否对gap加锁。该参数如果是enable的，则是unsafe的，此时gap不会加锁；反之，如果disable掉该参数，则gap会加锁。当然对于一些和数据完整性相关的定义，如外键和唯一索引（含主键）需要对gap进行加锁，那么innodb_locks_unsafe_for_binlog的设置并不会影响gap是否加锁。

 

牛人博客推荐：http://hedengcheng.com/?p=771

 

MVVC（多版本并发控制）

关于这方面的知识推荐一个思否的文章：https://segmentfault.com/a/1190000012650596

 

posted @ 2018-08-29 17:55  苦行僧95  阅读(337)  评论(0)  编辑  收藏  举报