源码商城网站源码,网站英文版建设,武昌做网站的公司,沈阳 建设工程 招标中心网站MySQL锁 事务事务的隔离级别脏读#xff0c;不可重复读#xff0c;幻读 表锁与行锁表锁测试准备测试 行锁测试 读锁与写锁读锁#xff08;共享锁#xff09;测试 写锁#xff08;排他锁#xff09;测试 元数据锁表级元数据锁表级MDL**#xff08;Metadata Lock#xff… MySQL锁 事务事务的隔离级别脏读不可重复读幻读 表锁与行锁表锁测试准备测试 行锁测试 读锁与写锁读锁共享锁测试 写锁排他锁测试 元数据锁表级元数据锁表级MDL**Metadata Lock**测试 锁表如果解决锁表呢 聊到MySQL锁的一些机制的时候我们会想到一写相关的知识。 事务事务隔离级别表锁行锁读锁共享锁写锁排他锁元数据锁DDL或MDL造成的锁锁表 事务
通常我们需要一组操作同时成功或者同时失败的时候需要通过事务来进行保证。
从而保证原子性一致性隔离性和持久性。
事务的隔离级别
Read Uncommitted 读未提交最低 允许一个事务读取另外一个事务未提交的数据可能造成脏读不可重复读幻读。Read Committed 读已提交 允许一个事务读取另外一个事务已提交的数据可以防止脏读。但可能出现不可重复读幻读。Repeatable Read 可重复读 保证同一个事务内多次读取相同数据的结果是一致的可以防止脏读和不可重复读。但会出现幻读。Serializable 串行最高 确保所有事务按照严格的顺序执行避免了幻读不可重复度和幻读但是会牺牲并发性能。
脏读不可重复读幻读
脏读读取一个事务还未提交的数据作为了当时的数据但是如果这个事务回滚了就会导致当时的那个数据是脏的。不可重复读读取另外一个事务已提交的数据如果一个事务内有两次读取这个数据去计算那么这两个计算结果不通。重复读取的数据不一致的问题。幻读同一个事务类读取相同的数据结果是一致的。那么如果在一个事务内需要读取两次这个数据那么在第二次结果读取前我们的数据发生了改变。尽管我们在事务内的数据读取的都是开启事务时的数据但是实际上数据库的数据就不一致了。那么就会造成幻读的问题。
表锁与行锁
InnoDB 是支持 表锁与行锁的。
MyIsam 只支持行锁。
表锁 用于锁定整个表阻止其他会话对整个表进行访问 测试准备
创建一个InnoDB引擎的表
drop table if exists InnoDBTest;
CREATE TABLE InnoDBTest (id int(11) NOT NULL AUTO_INCREMENT,a int(11) NOT NULL,b int(11) NOT NULL,PRIMARY KEY (id),KEY idx_a (a)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4;
insert into InnoDBTest(a,b) values(1,1),(2,2);测试
# 开启session1
BEGIN;# 使用写锁select * from InnoDBTest FOR UPDATE;
# 重新开始一个查询窗口后# 开启session2
BEGIN;# 使用写锁select * from InnoDBTest FOR UPDATE;
# 会发现 session2的selet会被阻塞住
# 当我们的session1 执行commit后session2的select才会执行完成。行锁 用于锁定表中的某行或者某些行范围允许其他事务方法未被锁定的行 测试
# 开启session1
BEGIN;# 使用写锁select * from InnoDBTest where a 1 FOR UPDATE;
# 重新开始一个查询窗口后# 开启session2
BEGIN;# 使用写锁select * from InnoDBTest where a 1 FOR UPDATE;# 会发现 session2的selet会被阻塞住# 开启session3
BEGIN;# 使用写锁select * from InnoDBTest where a 2 FOR UPDATE;
# 会发现可以session3的select会执行成功不会等待session1
# 可以理解会 where a 1 给a1的记录加了行锁只要不访问带有锁的行就能不被阻塞。读锁与写锁
读锁共享锁
普通的select通过加上关键字LOCK IN SHARE MODE可以使对应的行或者表获取到锁。 允许多个事务同时读取一份数据不会互相干扰。 但读锁会阻止其他事务获得写锁但不会阻止其他事务获得写锁。 多个事务可以同时获取读锁称之为共享锁 测试
# 开启session1
BEGIN;# 使用读锁select * from InnoDBTest where a 1 LOCK IN SHARE MODE;# 重新开始一个查询窗口后# 开启session2
BEGIN;# 使用读锁select * from InnoDBTest where a 1 LOCK IN SHARE MODE;# 会发现session2能正常获取数据# 开启session3
BEGIN;# 使用写锁update InnoDBTest set b 3 where a 1;
# session3会被阻塞因为update where a1 需要获取a1的记录当时此时session1给a1的记录上了读锁所以session3会阻塞只到 session1完成。# 开启session4
BEGIN;# 使用写锁update InnoDBTest set b 3 where a 2;# 会发现session4不会阻塞因为在innodb中支持行锁a2的记录没有被上读锁。写锁排他锁
写锁用于防止青铜器会话在同一时间内对相同数据进行读或者写操作。只允许一个事务对数据进行写操作其他事务不能同时读取或写入。这里也称为排他锁写锁会阻塞其他写锁和读锁以确保数据的一致性。
通常我们的update、insert、delete都是会获取写锁的。但是普通的select并没有获取锁需要为其加上 for update才能使普通的select拿到写锁
测试
# 开启session1
BEGIN;# 使用读锁select * from InnoDBTest for update;# 开启session2
BEGIN;select * from InnoDBTest;# 普通读不会阻塞# 开启session3
BEGIN;# 使用读锁select * from InnoDBTest LOCK IN SHARE MODE;# 需要获取InnoDBTest的读锁所以会阻塞# 开启session4
BEGIN;# 使用写锁insert into InnoDBTest value(3,3);# session4执行insert 需要获取写锁所以 session3会阻塞元数据锁
表级元数据锁 当一个会话正在修改表结果新增列删除列(DDL)等时会持有一个表级的元数据锁阻止其他会话对相同的表结果进修改 表级MDL**Metadata Lock** 当我们的表被锁住时我们通过DDL语句去操作表结果的时候就会触发表级MDL,触发后不管是读锁写锁或者是普通的select都会被阻塞 测试
# 开启session1
BEGIN;# 获取写锁delete from InnoDBTest;# 开启session2
alter table InnoDBTest add COLUMN c VARCHAR(50);
# 因为session1把表的写锁给获取了所以当我们执行DDL语句的时候就被阻塞了需要等待session1执行完成但是alter table 又需要获取元数据锁所以之后只要操作InnoDBTest的操作都会阻塞需要等待 seesion1与session2执行完成。# 开启session3
select * from InnoDBTest# 普通的select阻塞了。这里如果session1一直不commit这里就是一个死锁。也就导致了锁表。
如果验证呢请看锁表章节
锁表
用于查看当前打开的表信息
SHOW OPEN TABLES WHERE In_use 0;In_use 0 代表至少被一个会话使用的表才显示。 2.查看当前正在执行的所有数据库连接和查询的详细信息
SHOW FULL PROCESSLIST;列描述
Id连接或线程的唯一标识符。User执行查询的MySQL用户。Host连接的主机名或IP地址。db当前连接的数据库如果有。Command正在执行的命令类型例如 SELECT、INSERT、UPDATE、DELETE 等。Time该查询执行的时间以秒为单位。State查询的当前状态例如正在锁定、发送数据等。Info包含有关查询的更多信息如查询文本。
可以看见我们这条sql导致了锁表。也就是我们的session使得session2也阻塞了从而导致table metadata lock一直被占用。 所以线上环境在高峰期的时候一定要避免去执行DDL,这样会导致非常严重的问题类似与数据源连接池全部都阻塞了。导致程序挂掉。有幸遇到过
如果解决锁表呢
如果session1能够被关闭还好了但是如果通过session1关不掉不是我们自己的程序去操作的那怎么办
刚才通过SHOW FULL PROCESSLIST找到了是个主机及那个sql使得我们的表锁定了。找到该行的**ID**
# 通过线程ID来终止指定的线程
KILL process_id;# 终止对应的进程后
kill 21;SHOW FULL PROCESSLIST;
# 也没有看到其他的锁表信息了。# 普通select 可以正常查询了
select * from InnoDBTest今天遇到一个问题就是 有一个truncate table 在执行。一直没有导致线上服务查询基本信息的时候出现大量卡住的问题导致服务不可用。记录下问题及解决方案及其他知识点。
以上没有解决的话
请直接祭出最终办法万能重启!!!
