首页 > 技术文章 > mysql基础知识点四-事务隔离与锁机制

xiaoshahai 2022-01-27 10:13 原文

mysql基础知识点四-事务隔离与锁机制

事务

事务的acid:

  1. 原子性:数据要么都修改,要么都不修改
  2. 一致性: 事务开始和结束,数据必须保持一致
  3. 隔离性: 数据库事务不受外部并发操作影响
  4. 持久性:事务完成之后,数据修改是永久保存的 (保存机制需要参考日志)

常见的名词:

  1. 脏读: 读取了其他事物未提交的数据
  2. 更新丢失:多个事务修改同一行,最后更新覆盖其他事务更新
  3. 不可重复读:一个事务读取数据某个时间后再次读取之前的数据,数据改变,删除
  4. 幻读:事务a读到了事务b的新增

1643023360862

事务的隔离级别:为解决数据一致性问题(脏度,幻读,不可重复读)

  1. 隔离级别是影响并发的,越高影响越大,因为它一定程度上是使事务串行化,与并发矛盾
  2. 查看当前数据库的事务隔离级别:show variables like 'tx_isolation'
  3. 设置事务隔离级别:set tx_isolation='REPEATABLE-READ';
  4. Mysql默认的事务隔离级别是可重复读,用Spring开发程序时,如果不设置隔离级别默认用Mysql设置的隔 离级别,如果Spring设置了就用已经设置的隔离级别

锁:

  1. 计算机协调多个进程和线程访问的某一资源的机制,争夺的资源有cpu,ram,io,数据

锁分类

  1. 性能上分乐观锁和悲观锁
  2. 对数据库操作类型 共享锁(读锁),排他锁(写锁)
  3. 对数据操作粒度:表锁,行锁,间隙锁

表锁

  1. 特点:锁表,不会死锁,冲突概率高,并发度低,适合表数据迁移
  2. 例子
-- 建表SQL 
CREATE TABLE `mylock` ( `id` INT ( 11 ) NOT NULL AUTO_INCREMENT, `NAME` VARCHAR ( 20 ) DEFAULT NULL, PRIMARY KEY ( `id` ) ) ENGINE = MyISAM DEFAULT CHARSET = utf8;

INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('1', 'a');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('2', 'b');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('3', 'c');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('4', 'd');

select * from mylock;

-- 写锁
lock table mylock write;

-- 读锁
lock table mylock read;

-- 查看加锁的表
show open tables;

-- 解锁
unlock tables;

-- 行锁分析 通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况 
show status like 'innodb_row_lock%'; 
-- 对各个状态量的说明如下:
Innodb_row_lock_current_waits: 当前正在等待锁定的数量 
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度 
Innodb_row_lock_time_avg: 每次等待所花平均时间 
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits:系统启动后到现在总共等待的次数
对于这5个状态变量,比较重要的主要是: 
Innodb_row_lock_time_avg (等待平均时长) I
nnodb_row_lock_waits (等待总次数) 
Innodb_row_lock_time(等待总时长) 
尤其是当等待次数很高,而且每次等待时长也不小的时候,我们就需要分析系统中为什么会有如此多的等待, 然后根据分析结果着手制定优化计划。

案例结论
1、对MyISAM表的读操作(加读锁) ,不会阻寒其他进程对同一表的读请求,但会阻赛对同一表的写请求。只有当 读锁释放后,才会执行其它进程的写操作。 
2、对MylSAM表的写操作(加写锁) ,会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进 程的读写操作
3、以上结论对innodb同样适用,建议分别在写锁,读锁下去测试,注意锁有自动失效时间。

疑问:
for update 有索引行锁,无索引表锁,测试失败,为解决?可能是隔离级别问题。
猜测:
可重复读的隔离级别下使用了MVCC(multi-version concurrency control)机制,select操作不会更新版本号, 是快照读(历史版本);insert、update和delete会更新版本号,是当前读(当前版本)。

1643247706247

间隙锁 gap lock

  1. 锁的是俩个值之间的空隙,举个例子,如上图

  2. 间隙为 id(3,10),(10,20),(20,正无穷)

    在Session_1下面执行 update account set name = 'zhuge' where id > 8 and id <18;,则其他Session没法在这个范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙里插入或修改任何数据,即id在(3,20]区间都无法修改数据,注意最后那个20也是包含在内的。

    间隙锁是在可重复读隔离级别下才会生效。

临键锁(Next-Key Locks)

  1. Next-Key Locks是行锁与间隙锁的组合。像上面那个例子里的这个(3,20]的整个区间可以叫做临键锁

无索引行锁升级为表锁

  1. 锁是要加在索引上的,如果对非索引字段更新,行锁可能会变成表锁

额外知识,理论性,未实践

‐‐ 查看事务 
select * from INFORMATION_SCHEMA.INNODB_TRX; 
‐‐ 查看锁 4 select * from INFORMATION_SCHEMA.INNODB_LOCKS; 
‐‐ 查看锁等待 
6 select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 
‐‐ 释放锁,trx_mysql_thread_id可以从INNODB_TRX表里查看到 
kill trx_mysql_thread_id 
‐‐ 查看锁等待详细信息 
show engine innodb status
    
死锁
set tx_isolation='repeatable-read'; 
Session_1执行:select * from account where id=1 for update; 
Session_2执行:select * from account where id=2 for update; Session_1执行:select * from account where id=2 for update; 
Session_2执行:select * from account where id=1 for update; 查看近期死锁日志信息:show engine innodb status	; 
大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务,但是有些情况mysql没法自动检测死锁
    
锁优化建议 
	尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁 
    合理设计索引,尽量缩小锁的范围 尽
    可能减少检索条件范围,避免间隙锁 尽
    量控制事务大小,减少锁定资源量和时间长度,涉及事务加锁的sql尽量放在事务最后执行 
    尽可能低级别事务隔离

推荐阅读