本文更新于2019-09-22,使用Mysql 5.7,操作系统为Deepin 15.4。 目录锁锁概述MyISAM表级锁InnoDB行级锁InnoDB表级锁死锁事务事务概述InnoDB事务分布式事务 锁 锁概述 MyISAM和M
本文更新于2019-09-22,使用Mysql 5.7,操作系统为Deepin 15.4。
目录
MyISAM和MEMORY存储引擎使用表级锁。BDB存储引擎进使用页级锁,但也支持表级锁。InnoDB存储引擎默认使用行级锁,也支持表级锁。
默认情况下,表级锁和行级锁都是自动获取的。但在有些情况下,用户需要明确进行锁定。
表级锁有两种模式:
加锁,如果表已被其他线程锁定,则当前线程会等待直至获得锁:
LOCK TABLE|TABLES
tablename [AS alias] {READ [LOCAL]}|{[LOW_PRioRITY] WRITE}
[, ...]
加锁时指定LOCAL
,则允许在满足MyISAM表并发插入条件(使用变量concurrent_insert
控制)的情况下,其他用户在表尾并发插入记录。加锁时,需一次锁定所有用到的表,且同一个表在sql语句中出现多少次,就要通过与SQL语句中相同的别名锁定多少次(使用AS
)。加锁后,只能访问加锁的表,且不支持锁升级(即如果是读锁,那么只能执行读操作,不能执行写操作)。
MyISAM在执行读操作(SELECT
)前,会自动给涉及的所有表加读锁,在执行写操作(UPDATE
、DELETE
、INSERT
)前,会自动给涉及的所有表加写锁。
即使读请求先到锁等待队列,写请求后到,写锁也会插到读锁之前。可以使用max_write_lock_count
给予读请求获得锁的机会,或使用以下方法改变请求优先级:
low-priority-updates
,默认给予写请求比读请求更低的优先级。SET low_priority_updates=1
,给予该连接写请求比读请求更低的优先级。INSERT
、UPDATE
、DELETE
语句的LOW_PRIORITY
,降低该语句的优先级。解锁,释放当前线程获得的所有锁:
UNLOCK TABLES
如在锁表期间,当前线程执行另一个LOCK TABLES
或START TRANSACTION
(对InnoDB存储引擎),或与服务器的连接被关闭时,会隐含地执行UNLOCK TABLES
。
通过SHOW STATUS LIKE "table_locks%"
查看表级锁使用情况。table_locks_waited
比较高说明存在较严重的表级锁争用。
可以通过SHOW STATUS LIKE "innodb_row_lock%"
,或查看infORMation_schema中相关的表,或通过设置InnoDB Monitors查看行级锁争夺情况。
InnoDB实现了两种类型的行级锁:
另外,为了允许行级锁和表级锁共存,InoDB还有两种内部使用的意向锁,二者都是表锁:
InoDB行级锁模式兼容性如下(纵向是当前锁模式,横向是请求锁模式):
X | IX | S | IS | |
---|---|---|---|---|
X | 冲突 | 冲突 | 冲突 | 冲突 |
IX | 冲突 | 兼容 | 冲突 | 兼容 |
S | 冲突 | 冲突 | 兼容 | 兼容 |
IS | 冲突 | 兼容 | 兼容 | 兼容 |
对于UPDATE
、DELETE
、INSERT
语句会自动给涉及数据集加排他锁(X)。对普通SELECT
语句不会加任何锁,可通过select_statement LOCK IN SHARE MODE
加共享锁或select_statement FOR UPDATE
加排他锁,并需进行提交或回滚。
意向锁是InnoDB自动加的。
InnoDB行级锁是通过给索引上的索引项或间隙加锁来实现的,共分三种:
InnoDB行级锁的特点,需注意如下问题:
InnoDB存储引擎中不同SQL在不同隔离级别下的锁比较(off/on指变量innodb_locks_unsafe_for_binlog的值):
SQL | 条件 | 未提交读 | 已提交读 | 可重复读 | 可序列化 |
---|---|---|---|---|---|
SELECT | 相等 | 无锁 | 一致性读/无锁 | 一致性读/无锁 | 共享锁 |
SELECT | 范围 | 无锁 | 一致性读/无锁 | 一致性读/无锁 | 共享Next-Key锁 |
UPDATE | 相等 | 排他锁 | 排他锁 | 排他锁 | 排他锁 |
UPDATE | 范围 | 排他Next-Key锁 | 排他Next-Key锁 | 排他Next-Key锁 | 排他Next-Key锁 |
INSERT | 排他锁 | 排他锁 | 排他锁 | 排他锁 | |
REPLACE | 无键冲突 | 排他锁 | 排他锁 | 排他锁 | 排他锁 |
REPLACE | 键冲突 | 排他Next-Key锁 | 排他Next-Key锁 | 排他Next-Key锁 | 排他Next-Key锁 |
DELETE | 相等 | 排他锁 | 排他锁 | 排他锁 | 排他锁 |
DELETE | 范围 | 排他Next-Key锁 | 排他Next-Key锁 | 排他Next-Key锁 | 排他Next-Key锁 |
SELECT ... FROM ... LOCK IN SHARE MODE | 相等 | 共享锁 | 共享锁 | 共享锁 | 共享锁 |
SELECT ... FROM ... LOCK IN SHARE MODE | 范围 | 共享锁 | 共享锁 | 共享Next-Key锁 | 共享Next-Key锁 |
SELECT ... FROM ... FOR UPDATE | 相等 | 排他锁 | 排他锁 | 排他锁 | 排他锁 |
SELECT ... FROM ... FOR UPDATE | 范围 | 排他锁 | 排他锁 | 排他Next-Key锁 | 排他Next-Key锁 |
INSERT INTO ... SELECT ...(源表锁) | off | 共享Next-Key锁 | 共享Next-Key锁 | 共享Next-Key锁 | 共享Next-Key锁 |
INSERT INTO ... SELECT ...(源表锁) | on | 无锁 | 一致性读/无锁 | 一致性读/无锁 | 共享Next-Key锁 |
CREATE TABLE ... SELECT ...(源表锁) | off | 共享Next-Key锁 | 共享Next-Key锁 | 共享Next-Key锁 | 共享Next-Key锁 |
CREATE TABLE ... SELECT ...(源表锁) | on | 无锁 | 一致性读/无锁 | 一致性读/无锁 | 共享Next-Key锁 |
INSERT INTO ... SELECT ...
和CREATE TABLE ... SELECT ...
叫做不确定的SQL,属于不安全的SQL,不推荐使用。如确实需要使用,又不希望因加锁对源表并发更新产生影响,可使用以下方法:
innodb_locks_unsafe_for_binlog
设置为on
,强制使用多版本数据库(mvcC),但可能无法使用binlog正确恢复和复制数据。SELECT ... INTO OUTFILE ...
和LOAD DATA INFILE ...
间接实现,这种方式不会对源表加锁。以下两种情况可以考虑使用表级锁:
使用表级锁需要注意:
LOCK TABLES
可以给InnoDB表加表级锁,但表级锁不是由InnoDB存储引擎管理的,而是由其上一层——MySQL Server管理的。仅当autocommit=0
、innodb_table_locks=1
时,InnoDB才能知道MySQL Server加的表级锁,MySQL Server也才能知道InnoDB加的行级锁。这样InnoDB才能自动识别涉及表级锁的死锁。LOCK TABLES
给InnoDB表加锁时,需将autocommit
设为0。事务结束前,不要用UNLOCK TABLES
,因其会隐含地提交事务。COMMIT
和ROLLBACK
不能释放表级锁,必须使用UNLOCK TABLES
。MyISAM总是一次获得所需的全部锁,要么全部满足,要么等待,因此不会出现死锁。InnoDB,除单个SQL组成的事务外,锁是逐步获得的,这就决定了InnoDB可能发生死锁。
发生死锁后,InnoDB一般都能自动检测到,并使一个事务释放锁并回滚,另一个事务获得锁继续完成事务。但在涉及外部锁或涉及表级锁的情况下,InnoDB并不能完全自动检测到死锁,这需要通过设置锁等待超时参数innodb_lock_wait_timeout
解决。
减少锁冲突和死锁的方法:
SELECT ... FOR UPDATE
加排他锁,在没有符合该条件记录的情况下,两个线程都会加锁成功。程序发现记录尚不存在,就试图插入一条新记录,如果两个线程都这么做,就会出现死锁。这种情况将隔离级别改成已提交读就可避免问题。SELECT ... FOR UPDATE
判断是否存在符合条件的记录,如果没有就插入记录。此时,只有一个线程能插入成功,另一个线程会出现锁等待。等第一个线程提交后,第二个线程会因主键重出错,但虽然这个线程出错了,却会获得一个排他锁!这时如果有第三个线程来申请排他锁,也会出现死锁。对于这种情况,可以直接做插入操作,然后再捕获主键重异常,或者在遇到主键重错误时,总是执行ROLLBACK
释放获得的排他锁。可以使用SHOW ENGINE INNODB STATUS
查看最后一个死锁产生的原因。
事务的ACID属性:
并发事务处理的问题:
防止更新丢失是应用的责任,需要应用对要更新的数据加锁来解决。脏读、不可重复读、幻读其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决。事务隔离实质上是使事务在一定程度上串行化。数据库实现事务隔离的方式基本上分两种:
有以下4个事务隔离级别:
隔离级别 | 读一致性 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|---|
未提交读(Read Uncommitted) | 最低级别,只能保证不读取物理上损坏的数据 | 是 | 是 | 是 |
已提交读(Read Committed) | 语句级 | 否 | 是 | 是 |
可重复读(Repeatable read) | 事务级 | 否 | 否 | 是 |
可序列化(Serializable) | 最高级别,事务级 | 否 | 否 | 否 |
可使用语句改变事务隔离级别:
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED}|{READ COMITTED}|{REPEATABLE READ}|SERIALIZABLE
默认情况下,InnoDB是自动提交事务的,即每执行一条语句提交一次事务。可设置变量autocommit
指定是否自动提交。
在同一个事务中,最好不要使用不同存储引擎的表,否则ROLLBACK
需要对非事务表进行特别的处理,因为COMMIT
、ROLLBACK
只能对事务表有效。通常情况下,只对提交的事务记录到二进制日志中,但如果一个事务中包含非事务表,那么回滚的操作也会被记录到二进制日志中,以确保非事务表的更新也可以被复制到从数据库中。
所有的DDL语句都是不能回滚的,并且部分DDL语句会造成隐式的事务提交。
开始事务:
{START TRANSACTION}|{BEGIN [WORK]}
提交事务:
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
回滚事务,可以回滚到指定的savepointname。注意,可以回滚事务的一个部分,但不能提交事务的一个部分:
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] [TO SAVEPOINT savepointname]
CHAIN
和RELEASE
子句用于定义事务提交或回滚后的操作:CHAIN
会立即启动一个新事务,并且和原先的事务有相同的隔离级别;RELEASE
会断开客户端和服务器之间的连接。默认是NO CHAIN NO RELEASE
。
定义SAVEPOINT
。可以定义多个SAVEPOINT
,如果定义了相同名字的SAVEPOINT
,则后面定义的覆盖前面定义的:
SAVEPOINT savepointname
删除SAVEPOINT
:
RELEASE SAVEPOINT savepointname
当前分布式事务只支持InnoDB存储引擎。
一个分布式事务会涉及多个分支事务(XA事务),这些XA事务必须一起被提交,或一起被回滚。
使用分布式事务的应用程序涉及一个或多个资源管理器和一个事务管理器:
XA
语句时,MySQL服务器相当于资源管理器。XA
语句时,与服务器连接的客户端相当于事务管理器。执行分布式事务的过程使用两阶段提交:
启动XA事务:
XA START|BEGIN xid [JOIN|RESUME]
每个XA事务必须有一个唯一的xid,该值不能被其他的XA事务使用。xid由客户端提供,或由MySQL服务器生成,包含3个部分:"gtrid"[,"bqual"[,formatID]]
。
使XA事务进入PREPARE状态,也即两阶段提交的第一阶段:
XA END xid [SUSPEND [FOR MIGRATE]];
XA PREPARE xid;
提交XA事务,进入两阶段提交的第二阶段:
XA COMMIT xid [ONE PHASE]
回滚XA事务,进入两阶段提交的第二阶段:
XA ROLLBACK xid
返回当前数据库中处于PREPARE状态的XA事务详细信息:
XA RECOVER
MySQL的分布式事务还存在比较严重的缺陷:
--结束END--
本文标题: MySQL学习笔记(13):锁和事务
本文链接: https://www.lsjlt.com/news/7051.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-09
2024-05-09
2024-05-09
2024-05-09
2024-05-08
2024-05-08
2024-05-08
2024-05-08
2024-05-08
2024-05-08
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0