InnoDB 存储引擎 lock 的对象是事务,用来锁定的是数据库中的对象,如表、页、行,并且一般 lock 的对象仅在事务 commit 或 rollback 后进行释放(不同事务隔离级别释放的时间可能不同)。
InnoDB 存储引擎实现了如下两种标准的行级锁,其中,X 锁与任何的锁都不兼容,而 S 锁仅和 S 锁兼容(兼容指对同一记录行的兼容性情况)
InnoDB 存储引擎除了行锁以外,还有表锁,通常也称为意向锁,其设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型。其支持两种意向锁:
通过 information_schema 架构下的 INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS 三张表,用户可以更简单的监控当前事务并分析可能存在的锁问题。
SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
InnoDB 存储引擎有三种行锁的算法,其分别是:
Record Lock 总是会去锁住索引记录,InnoDB 存储引擎会使用聚簇索引来进行锁定。
Gap Lock 的作用是为了阻止多个事务将记录插入到同一个范围内,因为这会导致幻读问题(phantom Problem)的产生,用户可以通过以下两种方式来显式地关闭 Gap Lock:
Next-Key Lock 是结合了 Gap Lock 和 Record Lock 的一种索引算法,这种锁定技术,不止锁定记录本身,还锁定一个范围。
InnoDB 对于行的查询默认是采用 Next-Key Lock 算法,当查询的索引含有唯一属性时(主键索引、唯一索引),InnoDB 存储引擎会对 Next-Key Lock 进行优化,将其降级为 Record Lock;而对于辅助索引,不仅会对索引列加 Record Lock ,还会对索引列前后的键值范围加上 Gap Lock。
Phantom Problem:幻读问题,指在同一事务下,连续执行两次同样的 SQL 语句可能导致不同的结果,第二次的 SQL 语句可能会返回之前不存在的行。
select * from students where id = 20 for update;
select * from students where num = 135 for update;
select * from students where score= 91 for update;
select * from students where age = 22 for update;
为了解决多个事务并发会引发的锁问题,数据库系统提供了四种事务隔离级别供用户选择。
为什么 MYSQL 默认使用 Repeatable Read 隔离级别?这跟数据库的主从复制有关,MYSQL 的主从复制是基于 binlog 复制的,而 binlog 有三种格式,分别为:
那 MYSQL 在 5.0 这个版本以前,binlog 只支持 statement 这种格式!而这种格式在读已提交(Read Commited)这个隔离级别下主从复制是有 bug 的,因此 Mysql 将可重复读(Repeatable Read)作为默认的隔离级别。
怎么解决 Read Committed 隔离级别下,主从复制有问题的 bug?首先得解释下这个 bug,在 master 上执行的顺序为先删后插,若此时 binlog 为 statement 格式,它记录的顺序为先插后删,slave 同步的是 binglog,因此 slave 执行的顺序和主机不一致,就会出现主从不一致,怎么解决这个 bug 呢?
innodb_lock_wait_timeout
用来控制等待得时间(默认是 50 秒),innodb_rollback_on_timeout
用来设定是否在等待超时时对进行中的事务进行回滚操作(默认是 OFF,代表不回滚)