隔离级别
1、Read Uncommitted
可能出现问题举例:读脏
2、Read Committed
可能出现问题举例:不一致
3、Repeatable Read
可能出现问题举例:幻读
4、Serializable
可能出现问题举例:超时
锁机制
1、开启两个会话
2、每个会话开启事务
3、第一个会话中执行update sql(返回结果)
4、第二个会话中执行update sql(等待返回)
5、第一个会话提交事务(第二个会话返回结果)
6、第二个会话提交事务
对比以下几组sql(存在索引和普通列的区别)
a、
select num from test where id = 1 for update;
select num from test where id = 1 for update;
b、
select num from test where id = 1 for update;
select num from test where id = 2 for update;
c、
select num from test where num = 1 for update;
select num from test where num = 1 for update;
d、
select num from test where num = 1 for update;
select num from test where num = 2 for update;
死锁
会话1:
1、START TRANSACTION;
3、SELECT num FROM test1 WHERE id = 1 FOR UPDATE;
5、SELECT num FROM test2 WHERE id=1 FOR UPDATE;
7、COMMIT;
会话2:
2、START TRANSACTION;
4、SELECT num FROM test2 WHERE id = 1 FOR UPDATE;
6、SELECT num FROM test1 WHERE id=1 FOR UPDATE;
8、COMMIT;
间隙锁:
会话1:
1、START TRANSACTION;
3、SELECT num FROM test WHERE id > 1 AND id < 5 FOR UPDATE;
5、COMMIT;
会话2:
2、START TRANSACTION;
4、INSERT INTO test(id,num) VALUES(3,3);
6、COMMIT;