一、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)
转载于:https://blog.51cto.com/9124573/1750376