mysql并发提交事务,MySQL学习笔记之四：并发控制和事务机制

一、mysql的并发控制

当有多个查询需要同时修改同一个数据，就会产生并发控制的问题。mysql可以在两个层面进行并发控制：服务器层和存储引擎层。

mysql通过加锁实现并发控制：

⑴锁有两类：

读锁：共享锁，即一个读锁不会阻塞其它读锁(但会阻塞其它写锁)，多个用户可同时读取同一个资源，而不互相干扰。

写锁：排他锁，即一个写锁会阻塞其它读写锁，在给定时间内，只有一个用户能执行写入。

⑵锁粒度：

表级锁：锁定整张表

行级锁：并发程度更高，但维护较麻烦，会增加系统开销，易产生死锁。行级锁只能在存储引擎级别实现，MyISAM存储引擎不支持行级锁

⑶锁分类：

隐式锁：由存储引擎自动完成

显式锁：用户可手动施加锁(表级锁)

⑷手动加解锁：服务器级别

LOCK TABLES tb_name {READ|WRITE},...;

例：lock tables students write;

UNLOCK TABLES;

FLUSH TABLES WITH READ LOCK;  #关闭所有已打开的表并全局施加读锁

InnoDB存储引擎也支持另外一种显式锁(只锁定挑选出的行)：

SELECT ... LOCK IN SHARE MODE;

SELECT ... FOR UPDATE;

二、事务

事务(Transaction)是访问并可能更新数据库中各种数据项的一个程序执行单元(unit);

设想一个场景，A要向B转账500元，要经过两个步骤：A从自己的账户中减去500元；向B的账户增加500元。显而易见，只完成第一步或者只完成第二步都不合理，这两个步骤必须放入一个事务中，当作一个不可分割的工作单位，要么都执行，要么都不执行。

⑴事务是恢复和并发控制的基本单位，要符合事务的概念，必须通过ACID测试：

原子性(atomicity)：一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。

一致性(consistency)：事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

隔离性(isolation)：一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性(durability)：持久性也称永久性(permanence)，指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的

⑵事务之间的隔离级别：

READ-UNCOMMITTED(读未提交)：事务中的修改，即使没有提交，对其它事务也是可见的。事务可以读取未提交的数据，这也称为“脏读”；最低的隔离级别

READ-COMMITTED(读提交)：一个事务开始时，只能“看见”已提交的事务所做的修改，因此，再次执行同样的查询，可能得到不一样的结果，称为“不可重复读”。

REPEATABLE-READ(可重读)：保证了在同一个事务中多次读取同样记录的结果是一致的，可能引起“幻读”；mysql默认隔离级别

SERIALIZABILE(可串行化)：加锁读，即事务请求不到锁就无法读，必须等到其它事务释放锁(提交或回滚)后才行。

⑶查看mysql的隔离级别：SELECT @@global.tx_isolation;

⑷事务的启动、提交和加滚：

启动：START TRANSACTION|BEGIN;

提交：COMMIT;

回滚：ROLLBACK;

保存点：SAVEPOINT identifier;

回滚至某个保存点：ROLLBACK [WORK] TO [SAVEPOINT] identifier;

删除某保存点：RELEASE SAVEPOINT identifier;

⑸MySQL的自动提交功能：SELECT @@GLOBAL.autocommit;

默认是启用的，修改autocommit仅对支持事务的存储引擎产生影响；

自动提交能保证数据及时写入磁盘，但会造成频繁I/O，降低系统性能。

⑹MVCC：多版本并发控制；通过保存数据在某个时间点的快照实现。无论事务执行多长时间，其看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表、同一时刻看到的数据可能是不一样的。MVCC仅在第二、第三隔离级别下有效；

⑺事务日志：将随机I/O转换为顺序I/O，以提升事务操作效率；事务日志也称为Write-Ahead Logging。

数据从内存->事务日志->数据文件

为减轻磁盘压力，宜将事务日志和数据文件放于不同的磁盘上，事务日志不宜太大；

⑻InnoDB支持事务，而MyISAM不支持

会话1：这里要注意：修改全局变量针对新建立的会话生效，而修改局部变量则立即生效

MariaDB [testdb]> set session tx_isolation = 'REPEATABLE-READ';   #将会话级的事务隔离级别改为“读提交”

Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]> start transaction;    #启动一个事务

Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]> select * from students;

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

| ID | Name  | Age  | Gender | Class |

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

|  1 | jerry | NULL | f      | NULL  |

|  2 | tom   | NULL | m      | NULL  |

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

2 rows in set (0.08 sec)

会话2：MariaDB [testdb]> set session tx_isolation = 'REPEATABLE-READ';

Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]> start transaction;   #在会话2中也启动一个事务

Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]> insert students (Name,Gender) value ('rose','f');   #插入数据

Query OK, 1 row affected (0.08 sec)

会话1：MariaDB [testdb]> select * from students;   #会话1的事务看到的数据仍是原来的值

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

| ID | Name  | Age  | Gender | Class |

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

|  1 | jerry | NULL | f      | NULL  |

|  2 | tom   | NULL | m      | NULL  |

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

2 rows in set (0.00 sec)

会话2：MariaDB [testdb]> commit;   #事务2提交

Query OK, 0 rows affected (0.01 sec)

会话1：MariaDB [testdb]> select * from students;   #事务2可以看到事务1提交后的数据了

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

| ID | Name  | Age  | Gender | Class |

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

|  1 | jerry | NULL | f      | NULL  |

|  2 | tom   | NULL | m      | NULL  |

|  6 | rose  | NULL | f      | NULL  |

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

3 rows in set (0.00 sec)