mysql四大隔离级别测试

1. 1. 事务隔离级别

mysql默认的事务隔离级别为repeatable-read

show variables like '%tx_isolation%';

 

未提交读（READ UNCOMMITED）脏读

set SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

测试：

启动两个session

一个session中

  start TRANSACTION

  update account set balance = balance -50 where id = 1

另外一个session中查询

select * from account

回到第一个session中 回滚事务

ROLLBACK

在第二个session种

update account set balance = balance -50 where id = 1

查询结果还是 400

第二个session以为结果是350，但前面的400数据为脏读数据，导致最后的结果和意料中的结果并不一致。

已提交读 （READ COMMITED）不可重复读

测试

show variables like '%tx_isolation%';

set SESSION TRANSACTION ISOLATION LEVEL read committed;

一个session中

  start TRANSACTION

  update account set balance = balance -50 where id = 1

另外一个session中查询 (数据并没改变)

select * from account

回到第一个session中 回滚事务

commit

在第二个session种

select * from account (数据已经改变)

可重复读（REPEATABLE READ）

测试

show variables like '%tx_isolation%';

set SESSION TRANSACTION ISOLATION LEVEL repeatable read;

一个session中

  start TRANSACTION

  update account set balance = balance -50 where id = 1

另外一个session中查询 (数据并没改变)

select * from account

回到第一个session中 回滚事务

commit

在第二个session种

select * from account (数据并未改变)

​​​​​​​可串行化（SERIALIZABLE）

account 表有3条记录，业务规定，最多允许4条记录。

1.开启一个事务

begin

select * from account  发现3条记录

2.开启另外一个事务

begin

select * from account  发现3条记录 也是3条记录

insert into account VALUES(4,'deer',500)

查询  4条记录

select * from account

3.回到第一个session 

insert into account VALUES(5,'james',500)

select * from account  4条记录

4.session1 与 session2 都提交事务 

1. set SESSION TRANSACTION ISOLATION LEVEL serializable; 重新上面的测试发现插入报错

​​​​​​​总结

 

事务隔离级别为可重复读时，如果有索引（包括主键索引）的时候，以索引列为条件更新数据，会存在间隙锁间、行锁、页锁的问题，从而锁住一些行；如果没有索引，更新数据时会锁住整张表

事务隔离级别为串行化时，读写数据都会锁住整张表

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大，对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。

间隙锁(Gap Lock)
间隙锁，锁的就是两个值之间的空隙。Mysql默认级别是repeatable-read，有办法解决幻读问题吗？

间隙锁：在某些情况下可以解决幻读问题。
 

假设有这种记录：

那么间隙就有 id 为 (3,10)，(10,20)，(20,正无穷) 这三个区间，
在Session_1下面执行 update account set name = 'zhuge' where id > 7 and id <19;，

这时候会锁住3-10的区间，同时也会锁住10-20的区间，也就是在这两个区间上加上间隙锁

那么此时你要插入11的记录肯定会阻塞，这就很好的解决了幻读问题。
间隙锁是在可重复读隔离级别下才会生效

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

无索引行锁会升级为表锁
锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁
session1 执行：update account set balance = 800 where name = 'lilei';
session2 对该表任一行操作都会阻塞住
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁，也就是修改的字段没有加上索引就会对该表加上表锁。

锁定某一行还可以用lock in share mode(共享锁) 和for update(排它锁)，

例如：select * fromtest_innodb_lock where a = 2 for update; 这样其他session只能读这行数据，修改则会被阻塞，直到锁定行的session提交

for update:行级锁，mysql对update,insert,delete会自动加上行锁

如果发生了死锁怎么处理？

查看INFORMATION_SCHEMA系统库锁相关数据表
‐‐ 查看事务
 select * from INFORMATION_SCHEMA.INNODB_TRX;

‐‐ 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到
 kill trx_mysql_thread_id

死锁
set tx_isolation='repeatable-read';
Session_1执行：select * from account1 where id=11 for update;
Session_2执行：select * from account1 where id=22 for update;
Session_1执行：select * from account1 where id=22 for update;
Session_2执行：select * from account1 where id=11 for update;
查看近期死锁日志信息：show engine innodb status\G;
大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁

锁优化建议
尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围(比如上述的3-20，如果插入22的记录，那么22-正无穷的记录都会别锁住，此时只需要我们插入28的记录既可以解决锁住整张表的情况，尽量缩小锁的范围)
尽可能减少检索条件范围，避免间隙锁（一旦出现间隙锁那么就会锁表的情况）
尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
尽可能低级别事务隔离(默认rr)