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

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

事务

事务的acid:

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

常见的名词：

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

事务的隔离级别：为解决数据一致性问题（脏度，幻读，不可重复读）

2. 隔离级别是影响并发的，越高影响越大，因为它一定程度上是使事务串行化，与并发矛盾
3. 查看当前数据库的事务隔离级别：show variables like 'tx_isolation'
4. 设置事务隔离级别：set tx_isolation='REPEATABLE-READ';
5. 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会更新版本号，是当前读（当前版本）。

间隙锁 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尽量放在事务最后执行 
    尽可能低级别事务隔离