下表展示了本人安装的 MariaDB(10.1.19,MySQL 的分支)所支持的所有存储引擎概况,其中支持事务的有 InnoDB、SEQUENCE,另外 InnoDB 还支持 XA 事务,MyISAM 不支持事务。锁可以通过 SQL 语句 (如 LOCK TABLES) 显式申请,也可以由 InnoDB 引擎自动为你获取。下文将讨论 InnoDB 和 MyISAM 在事务与锁定方面的相关话题
ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS |
---|---|---|---|---|---|
CSV | YES | CSV storage engine | NO | NO | NO |
MyISAM | YES | MyISAM storage engine | NO | NO | NO |
MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
MEMORY | YES | Hash based, stored in memory, useful for temporary... | NO | NO | NO |
PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES |
InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level l... | YES | YES | YES |
一. 事务四要素
数据库事务正确执行的四个基本要素包括原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability),简称 ACID。目前要实现 ACID 主要有两种方式:一种是 Write ahead logging,也就是日志式的方式 (现代数据库均基于这种方式);另一种是 Shadow paging。
二. MyISAM 表锁定
所有用户行为最后执行的基本单位是单条操作命令 (SELECT、INSERT、UPDATE、DELETE 等),它们都是原子操作。按照事务术语,MyISAM 表总能高效地工作在 AUTOCOMMIT=1 模式下,原子操作通常能提供可比较的完整性以及更好的性能。这意味着,你能确信在每个特性更新运行的同时,其他用户不能干涉它,而且不会出现自动回滚(如果你不小心,对于事务性表,这种情况可能发生),MySQL 服务器还能保证不存在脏读。
一般而言,所有由事务解决的完整性问题均能用 LOCK TABLES 或原子更新解决,从而确保了服务器不会自动中断,后者是事务性数据库系统的常见问题。MyISAM 只支持表级锁定,允许多重读或一次写。LOCK TABLES 可以锁定用于当前线程的表,如果表被其它线程锁定,则造成堵塞,直到可以获取所有锁定为止。UNLOCK TABLES 可以释放被当前线程保持的任何锁定。当线程发布另一个 LOCK TABLES 时,或当与服务器的连接被关闭时,所有由当前线程锁定的表被隐含地解锁。如果您想要确保在同一业务流中的 SELECT 和 UPDATE 之间没有其它线程访问修改数据,就必须使用 LOCK TABLES。如果你获得了对某一表的 READ LOCAL 锁定(与写锁定相对),该表允许在表尾执行并行插入,当其他客户端执行插入操作时,允许执行读操作。新插入的记录不会被有读锁定属性的客户端看到,直至解除了该锁定为止。使用 INSERT DELAYED,能够将插入项置于本地队列中,直至锁定解除,不会让客户端等待插入完成。
三. InnoDB 事务与锁定
在 InnoDB 中,所有用户行为都在事务内发生,事务是其执行的基本单位,默认运行查询为非锁定持续读。如果自动提交模式被允许,即 AUTOCOMMIT = 1,每个 SQL 语句都将以一个单独的事务来运行;如果自动提交模式被用 SET AUTOCOMMIT = 0 关闭,那么我们可以认为一个用户总是有一个事务打开着,一个 SQL COMMIT 或 ROLLBACK 语句结束当前事务并且一个新事务开始,两个语句都释放所有在当前事务中被设置的 InnoDB 锁定。
InnoDB 除了表级锁定之外,还支持更细粒度的行级锁定,且行锁和表锁多重粒度可共存。下面是 InnoDB 中与锁定有关的几个重要概念:
一个事务必须等待直到冲突的锁被释放。如果一个锁请求和一个已经存在的锁冲突, 并且一直不能被授权, 就会造成死锁。一旦死锁发生,InnoDB 会选择其中一个报错并释放其持有的锁,直至解除死锁。意向锁并不会阻塞任何事情,除非是对全表的请求 (例如, LOCK TABLES ... WRITE), IX 和 IS 锁的主要目的是表示有人正在或者准备锁定一行
- 1. 在一个事务获取表t的某行的S锁之前, 他必须获取表t的一个IS锁或更强的锁
- 2. 在一个事务获取表t某行的X锁之前, 他必须获取一个t的IX锁
- 3. 这些规则可以总结为如下锁类型兼容矩阵:
- X IX S IS
- X Conflict Conflict Conflict Conflict
- IX Conflict Compatible Conflict Compatible
- S Conflict Conflict Compatible Compatible
- IS Conflict Compatible Compatible Compatible
- 4. 一个锁如果和已经存在的锁兼容, 就可以授权给请求他的事务, 但如果和已存在的锁不兼容则不行
- 假设你想要从有一个标识符值大于100的child读并锁定所有某些记录,并想着随后在选定行中更新一些列:
- SELECT * FROM child WHERE id > 100 FOR UPDATE;
- 假设在id列有一个索引,查询从id大于100的第一个记录开始扫描。如果设置在索引记录上的锁定不把在间隙生成的插入排除在外,一个新行可能与此同时被插进表中。如果你在同一事务内执行同样的SELECT,你可能会在该查询返回的结果包里看到一个新行。这与事务的隔离原则是相反的:一个事务应该能够运行,以便它已经读的数据在事务过程中不改变。如果我们把结果集视为一个数据项,新的"幽灵"记录出现会违反这一隔离原则。
- 当InnoDB扫描一个索引之时,它也锁定范围中最后一个记录之后的间隙。刚才前一个例子中:InnoDB设置的锁定防止任何表中出现id大过100的记录
从上可知,READ UNCOMMITTED 隔离约束最弱,SERIALIZABLE 隔离约束最强,隔离约束逐级提高。
- · READ UNCOMMITTED
- SELECT语句以非锁定方式被执行,但是一个可能更早期版本的记录会被用到,读是不连贯的,
- 也被称为"脏读"(dirty read)。另外,这个隔离级别象READ COMMITTED一样作用。
- · READ COMMITTED
- 所有SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MOD语句仅锁定索引记录,而不锁定记录前的间隙,因而允许随意紧挨着已锁定的记录插入新记录。UPDATE和DELETE语句使用一个带唯一搜索条件的唯一的索引仅锁定找到的索引记录,而不包括记录前的间隙。在范围类型UPDATE和DELETE语句,InnoDB必须对范围覆盖的间隙设置next - key锁定或间隙锁定其它用户做的块插入,因为要让MySQL复制和恢复起作用,"幽灵行"必须被阻止掉。
- · REPEATABLE READ
- 这是InnoDB的默认隔离级别。带唯一搜索条件使用唯一索引的SELECT ... FOR UPDATE,
- SELECT ... LOCK IN SHARE MODE,
- UPDATE 和DELETE语句只锁定找到的索引记录,而不锁定记录前的间隙。用其它搜索条件,这些操作采用next - key锁定,用next - key锁定或者间隙锁定锁住搜索的索引范围,并且阻止其它用户的新插入。在持续读中,有一个与之前隔离级别重要的差别:在这个级别,在同一事务内所有持续读读取由第一次读所确定的同一快照。这个惯例意味着如果你在同一事务内发出数个无格式SELECT语句,这些SELECT语句对相互之间也是持续的。
- · SERIALIZABLE
- 这个级别类似REPEATABLE READ,但是所有无格式SELECT语句被 隐式转换成SELECT ... LOCK IN SHARE MODE。
四. 行锁与表锁优劣对比
行级锁定的优点:
行级锁定的缺点:
MySQL 表锁定机制:当一个锁定被释放时,锁定可被写锁定队列中的线程得到,然后是读锁定队列中的线程。这意味着,如果你在一个表上有许多更新,SELECT 语句将等待直到没有更多的更新。表更新通常情况认为比表检索更重要,因此给予它们更高的优先级,但这应确保更新一个表的活动不能 "饿死",即使该表上有很繁重的 SELECT 活动
对 WRITE,MySQL 使用的表锁定方法原理如下:
对 READ,MySQL 使用的表锁定方法原理如下:
在以下情况下,表锁定优于行级锁定:
- UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
- DELETE FROM tbl_name WHERE unique_key_col=key_value;
表锁定的有关事项:
五. 选择 MyISAM
一般而言,若要使用 MyISAM 存储引擎,应看看应用程序做什么并且混合使用什么样的读和写。例如,大多数 web 应用程序执行许多 select,而很少进行 delete,只对 key 的值进行更新,并且只插入少量行,此时使用 MyISAM 会更佳,而且 MyISAM 支持并发 select 和 insert。在 MySQL 中对于使用表级锁定的存储引擎,表锁定时不会死锁的,这通过总是在一个查询开始时立即请求所有必要的锁定并且总是以同样的顺序锁定表来管理。
可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁定争夺:
- mysql> SHOW STATUS LIKE 'Table%';
- +-----------------------+---------+
- | Variable_name | Value |
- +-----------------------+---------+
- | Table_locks_immediate | 1151552 |
- | Table_locks_waited | 15324 |
- +-----------------------+---------+
如果数据文件不包含空块 (从表的中部删除或更新的行可能导致空洞),INSERT 语句不冲突,可以自由为 MyISAM 表混合并行的 INSERT 和 SELECT 语句而不需要锁定,你可以在其它客户正读取 MyISAM 表的时候插入行,记录总是插入在数据文件的尾部;如果不能同时插入,为了在一个表中进行多次 INSERT 和 SELECT 操作,可以在临时表中插入行并且立即用临时表中的记录更新真正的表,这也适合做批量延迟插入:
- mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
- mysql> INSERT INTO real_table SELECT * FROM insert_table;
- mysql> TRUNCATE TABLE insert_table;
- mysql> UNLOCK TABLES;
六. 选择 InnoDB
InnoDB 使用行锁定,可能存在死锁。这是因为,在 SQL 语句处理期间,InnoDB 自动获得行锁定,而不是在事务启动时获得。对于 InnoDB 和 BDB(BerkeleyDB) 表,如果你用 LOCK TABLES 显式锁定表,MySQL 只使用表锁定,建议不要使用 LOCK TABLES,因为 InnoDB 使用自动行级锁定而 BDB 使用页级锁定来保证事务隔离。
InnoDB 支持外键约束。
一般说来,以多读为主也附带少量写首选 MyISAM,否则选择 InnoDB 或其他引擎会更佳。
七. 悲观锁与乐观锁
悲观锁和乐观锁不是 MySQL 数据库中的标准概念,而只是一种通俗说法。
锁定表可以加速用多个语句执行的 INSERT 操作,因为索引缓存区仅在所有 INSERT 语句完成后刷新到磁盘上一次。一般有多少 INSERT 语句即有多少索引缓存区刷新,如果能用一个语句插入所有的行,就不需要锁定;对于事务表,应使用 BEGIN 和 COMMIT 代替 LOCK TABLES 来加快插入
- LOCK TABLES a WRITE;
- INSERT INTO a VALUES (1, 23),
- (2, 34),
- (4, 33);
- INSERT INTO a VALUES (8, 26),
- (6, 29);
- UNLOCK TABLES;
- 首 步:执行一次查询 select some_column as old_value from some_table where id = id_value (假设该值在当前业务处理过程中不会被其他并发事务修改)
- ...
- 第n步:old_value参与中间业务处理,比如old_value被自己修改 new_value = f(old_value)。这期间可能耗时很长,但不会为持有 some_column 而申请所在的行或表锁定,因此其他并发事务可以获得该锁
- ...
- 尾 步:执行条件更新 update some_table set some_column = new_value where id = id_value and some_column = old_value (条件更新中检查old_value是否被修改)
MySQL 事务与锁机制
来源: http://www.bubuko.com/infodetail-2144321.html