innodb锁

最新推荐文章于 2024-07-05 09:39:40 发布

带风带不走

最新推荐文章于 2024-07-05 09:39:40 发布

阅读量725

点赞数

分类专栏： InnoDB深入内核文章标签： innodb 锁隔离级别并发

本文链接：https://blog.csdn.net/xxq929604980/article/details/53191945

版权

InnoDB深入内核专栏收录该内容

4 篇文章 0 订阅

订阅专栏

数据库开发最核心的要解决的问题是并发和锁(即数据一致性问题)

不同存储引擎的锁粒度不同，innodb是行级别锁，mysiam是表级锁，还有些支持页锁。

1.锁类型

innodb实现了共享锁和排他锁两种类型的行级锁

共享锁：允许事务读一行数据，其它事务可立即获得共享锁。

排他锁：允许事务更新或删除行，其它事务需等待锁释放才能获取锁。

总结,多个共享锁可以在同一行同时存在，但共享锁和排它锁只能同时存在其一，即锁不兼容。

innodb支持意向共享锁和意向排他锁两种类型的表级锁

意向共享锁：事务想要获得表中某几行的共享锁。

意向排他锁：事务想要获得表中某几行的排他锁。

监控和分析事务及可能存在的锁问题涉及的相关表：

SELECT * from information_schema.INNODB_TRX;
SELECT * from information_schema.INNODB_LOCKS;
SELECT * from information_schema.INNODB_LOCK_WAITS;

INNODB_TRX：查询事务当前的状态，等待或运行。

INNODB_LOCKS：查询锁被占用的情况，可看到锁类型，哪个事务在占用，被锁住的行主键值等。

当事务量大的时候，不容易找出文件所在，哪些事务阻塞了哪些事务。INNODB_LOCK_WAITS可以明显的反映当前的锁等待和事务等待,有四个字段：

request_txt_id:申请锁资源的事务id

request_lock_id:申请锁的id

blocking_txt_id：阻塞的事务id

blocking_lock_id:阻塞的锁id

2.一致性非锁定行读操作

一致性非锁定行读操作是通过MVVC实现的，读取行的快照数据(即行之前版本的数据)，这是通过undo log段实现的。因为没必要对历史数据进行修改操作，所以不必等待行的锁释放。

undo log段是用来事务回滚的，所以快照数据并没有占用额外的开销;

在事务隔离级别COMMITTED READ和REPEATABLE READ,都是使用一致性非锁定行读，但对于快照数据的定义却不同，COMMITTED READ的非锁定读是读取最新的一份快照数据;REPEATABLE READ的非锁定读是读取事务开始时的一份数据版本。

在事务隔离级别REPEATABLE READ下：

在session A中

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set

mysql> begin;
Query OK, 0 rows affected

mysql> select * from t limit 1;
+----+
| id |
+----+
| 2 |
+----+
1 row in set
在session B中:

mysql> begin;
Query OK, 0 rows affected

mysql> select * from t limit 1;
+----+
| id |
+----+
| 2 |
+----+
1 row in set

在session A中:

mysql> update t set id=3 where id=2;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t limit 1;
+----+
| id |
+----+
| 3 |
+----+
1 row in set

在session B中:可以看到session A未提交，session B读取session A事务开始时数据版本

mysql> select * from t limit 1;
+----+
| id |
+----+
| 2 |
+----+
1 row in set

在session A：提交事务

mysql> commit;
Query OK, 0 rows affected

在session B中:session B事务未提交，可以看到session A提交后，session B依然读取session A事务开始时数据版本，而在COMMITTED READ隔离级别中，session A事务提交，id应该是3.可以在修改隔离级别后尝试下。

mysql> select * from t limit 1;
+----+
| id |
+----+
| 2 |
+----+
1 row in set

先查询隔离级别：

SELECT @@session.tx_isolation;

 SELECT @@global.tx_isolation;

再设置隔离级别:

set global tx_isolation='read-committed';

set session tx_isolation='read-committed';

更改后最好在事务开始前查看隔离级别,以确认修改成功，打开另一个会话也是一样的。

3.一致性锁定行读操作

set session tx_isolation='repeatable-read';
set globaltx_isolation='repeatable-read';
一致性锁定行读SQL操作如：

1对.select * from t limit 1 for update;
2.select * from t limit 1 lock in share mode;

这两种操作必须在事务当中,事务提交了,锁也就释放了。因此上两个类型select必须在事务中执行。

需要注意的是:对于一致性非锁定读，即便已经该行已使用select ..for update,也还是可以读取的，但是如果执行的是一致性锁定读不能读取。

在session A:

mysql> begin;
Query OK, 0 rows affected

mysql> select * from student limit 1 for update;
+----+---------+------+
| id | NO | name |
+----+---------+------+
| 1 | 2012001 | 12 |
+----+---------+------+
1 row in set

在session B:

mysql> begin;
Query OK, 0 rows affected

mysql> select * from student limit 1;//非锁定读，能读取到
+----+---------+------+
| id | NO | name |
+----+---------+------+
| 1 | 2012001 | 12 |
+----+---------+------+
1 row in set

mysql> update student set NO='102344' limit 1;//在session A中锁未释放，所以等待超时
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> select * from student limit 1 for update;//锁定读失败。

mysql>

4.自增长和锁

对每个有自增长的表，都有一个自增长计数器，当对有自增长计数器的表进行插入时，这个计数器被初始化，

执行select max(auto_inc_col) from t for update;插入操作会根据获取的最大计数器值加1赋予到插入行的自增列，采用一种特殊的表锁机制，为了提高性能，不是在事务完成后释放，而是完成插入语句后立即释放。但是在大量插入操作情况下，会影响性能，因为在另一个事务中会阻塞。innodb在mysql 5.1.22后采用轻量级互斥量的自增长机制。

并提供了一个参数innodb_autonic_lock_mode，默认为1.可选值0,1,2

自增长的插入分类：

1.insert-like :指所有的插入语句。

2.simple inserts:插入前能确定插入行的插入如insert,replace

3.bulk inserts:不能确定插入行数的插入。如insert ..select ,replace ..select ,load data

4.mixed-mode inserts:不能确定全是自增长的，如主键包含null.

innodb和myIsam对自增长的实现是不同的，myIsam是表锁的，所以不用考虑并发插入问题。在innodb自增长列必须是第一列，且是索引，否则报错，但可以不设置自增长列。

5.外键和锁

外键主要用于引用完整性的约束检查。在innodb中，如果未对外键列加索引，innodb会自动加索引（oracle不会），这样可以避免表锁。

对于子表的外键值的插入和删除，会先select 父表，但对父表的这个select 操作,不是使用非锁定读,因为可能产生数据不一致问题，innodb主动对父表加上共享锁，即在sql查询语句上会主动带上lock in share mode

测试：

session A:

mysql> begin;
Query OK, 0 rows affected

mysql> delete from parent where id=2;
Query OK, 1 row affected

mysql> select * from parent;
+----+
| id |
+----+
| 1 |
+----+
1 row in set

mysql> commit;
Query OK, 0 rows affected

session B:

mysql> begin;
Query OK, 0 rows affected

mysql> insert into child select 5,2;//在session A未提交前一直等待,因为delete操作为这行加上了X(排他)锁。
1452 - Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `b` FOREIGN KEY (`b`) REFERENCES `parent` (`id`) ON DELETE SET NULL ON UPDATE SET NULL)

6.锁算法

innodb有三种锁设计：

1.record lock:行锁

2.gap lock:间隙锁,即锁定一个范围，但不包含记录本身。

3.next-key lock:record lock+gap lock.在该算法下，innodb对行的查询都是采用这种锁算法，但对不同SQL,可能设置share（共享） next-key lock和exclusive（排它） next-key lock.

演示下next-key lock：注意设置为隔离级别repeatable read下，因为该模式下，Next-Key Lock算法是默认的行锁算法。

CREATE TABLE `parent` (
`id` int(2) NOT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

在session A中：
mysql> select @@tx_
isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set

mysql> begin;
Query OK, 0 rows affected

mysql> select * from parent where id<8 lock in share mode;
+----+
| id |
+----+
| 1 |
| 2 |
| 5 |
| 6 |
+----+
4 rows in set

mysql>

在session B中：

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set

mysql> begin;
Query OK, 0 rows affected

mysql> insert into parent(id) select 4;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql>