MySQL的锁定机制

数据库的锁定机制应该算是数据库内部的一个核心技术,数据库主要用于大量用户的访问,如何对资源粒度实现有效的控制,锁定机制起着核心决定方面。Mysql的存储引擎使用三种类型的锁定机制:表级锁定、行级锁定和页级锁定。

  
行级锁定:
  行级锁定也就是以数据行为资源粒度进行锁定,oracle数据库也就是使用的行级锁定,mysql中Innodb存储引擎和NDB cluster存储引擎使用的是行级锁定,相对于锁定粒度较小,当然其实现算法相对比较复杂,消耗相对较高,容易产生死锁,不过确实可以提高最大的并发度。
  
表级锁定:
  表级锁定也就是以整个表为资源粒度进行锁定,MyISAM、Memory、CSV等非事务性存储引擎使用,相对于锁定粒度较大,当然实现算法相对比较简单,资源消耗较低,不容易产生死锁,不过并发度确是大打则扣。
  
页级锁定:
  应该算是mysql数据库中比较独特的一种锁定机制,BerkeleyDB存储引擎使用,其实自己也就用过mysql oracle两种主流数据库,页级锁定资源粒度是以页级为标准,并发和消耗位于行级和表级之间。
  
myisam存储引擎采用的是表级别锁定,表级锁定实现的原理:
  表级锁定又可以细分为读锁定和写锁定,在mysql中通过4个队列来维护:
  Current read-lock queue (lock-read) 当前读
  Current read-lock queue (lock-read_wait) 读请求等待
  Current write-lock queue (lock-write) 当前写
  Current write-lock queue (lock-write_wait) 写请求等待
  
新的会话请求读资源,由于读是不会阻塞读的,那么只需要满足:1当前写队列中不存在相同的资源竞争 2 写锁定等待队列中不存在更高级别的写锁定等待。如果能满足上述条件,那么读锁定的资源会立即被获取。
  
新的会话请求写锁定资源是:1当前写队列中不存在相同的资源 2 写锁定等待队列中不存在同样资源的竞争 3 当前读队列中不存在或者写锁定请求的锁定类型是write_delayed或者写锁定的类型是write_concurrent_insert,而当前读队列中并不是read_no_insert读锁定,这个也就是MYISAM后面推出的读取时可以并发的插入,默认情况下concurrent_insert=1,当MYISAM存储引擎表数据文件不存在空闲空间时,可以从尾部插入。
  
一般来说写请求会比相同资源下的读请求级别高,不过mysql还是允许设置优先级别读,也就是read_high_priority的读锁定,优先级读会阻塞所有的写锁定队列中的同等资源的请求。
  
Session A
mysql> create table t_myisam01(id int,name varchar(100)) engine=MYISAM;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t_myisam01 values(1,'ORCL'),(2,'MYSQL');
Query OK, 2 rows affected (0.00 sec)
mysql> lock table t_myisam01 read;
Query OK, 0 rows affected (0.00 sec)

Session B
mysql> select * from t_myisam01;
+------+-------+
| id | name |
+------+-------+
| 1 | ORCL |
| 2 | MYSQL |
+------+-------+
2 rows in set (0.00 sec)
--上面读并没有阻塞读,读并不阻塞读

mysql> update t_myisam01 set name='AWR' where id=2;
--被阻塞了,读阻塞了写请求锁定。

在session A下释放到read锁定,而后session b的写请求马上得到相应。
Session A
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

继续在session A下对表进行写锁定
mysql> lock table t_myisam01 write;
Query OK, 0 rows affected (0.00 sec)
Session b下请求读锁定,被阻塞
mysql> select * from t_myisam01;

--此时写阻塞了读,当然写也会阻塞写
换另一个会话执行插入,同样被阻塞。
mysql> insert into t_myisam01 values(3,'OCM');

不过我们上面也说过mysql的myisam允许在一个线程在读取表时,另外的线程可以在文件末尾插入数据
Session A:
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> lock table t_myisam01 read local;
Query OK, 0 rows affected (0.00 sec)

Session B:
mysql> insert into t_myisam01 values(3,'OCM');
Query OK, 1 row affected (0.00 sec)

看出此时数据依然可以插入,concurrent_insert该参数控制读取和insert级别的插入是否能够共存。

行级锁定的实现机制:
  行级锁定是由各个存储引擎自行实现,innodb存储引擎虽然和oracle有相似的地方,但是其实现机制大不相同,oracle是利用在block上的事务槽上标记相应的数据行已经被锁定,而innodb锁定则是利用索引键的空间来进行锁定,也就是间隙锁。而当innodb存储引擎的表上没有索引时,则会将行级锁定升级为表级锁定。行级锁定虽然会带来死锁,不过innodb存储引擎有专门检索死锁的机制,死锁环境下mysql会牺牲一个相对来说事务较小的事务回滚。
  
测试1:
Session A:
mysql> create table t_innodb01(id int,name varchar(100)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> create index index_id on t_innodb01(id);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t_innodb01 values(1,'ORCL'),(2,'XIAOYU'),(4,'MYSQL');
Query OK, 3rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> update t_innodb01 set name='AWR' where id>1 and id<5;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0

Session B
mysql> insert into t_innodb01 values(1,'ODU');
看出innodb锁定机制会以范围值锁定,而不管该值是否存在,新插入的值被阻塞。

测试2:
Session A
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> create index index_name on t_innodb01(name);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table t_innodb01\G;
*************************** 1. row *************************** Table: t_innodb01
Create Table: CREATE TABLE t_innodb01 (
id int(11) DEFAULT NULL,
name varchar(100) DEFAULT NULL,
KEY index_id (id),
KEY index_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from t_innodb01;
 +------+------+
 | id | name |
 +------+------+
 | 1 | ORCL |
 | 2 | AWR |
 | 4 | AWR |
 | 1 | ODU |
 +------+------+
 4 rows in set (0.00 sec)
 
mysql> update t_innodb01 set name='DUL' where id=1 and name='ODU';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Session B:
mysql> update t_innodb01 set name='ASH' where id=1 and name='ORCL';
看出mysql在使用锁定定位数据时候,使用的索引键一样,但是数据的访问不一样时,还是会被锁定,因为innodb是间隙锁,范围性的锁定。

测试3:
Session A:
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t_innodb02(id int,name varchar(100));
Query OK, 0 rows affected (0.05 sec
  
mysql> show create table t_innodb02\G;
*************************** 1. row *************************** Table: t_innodb01
Create Table: CREATE TABLE t_innodb02 (
id int(11) DEFAULT NULL,
name varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
ERROR:
No query specified

mysql> insert into t_innodb02 values(1,'ORCL'),(2,'MYISAM');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_innodb02;
  +------+--------+
  | id | name |
  +------+--------+
  | 1 | ORCL |
  | 2 | MYISAM |
  +------+--------+
2 rows in set (0.00 sec)
mysql> update t_innodb02 set name='ODU' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Session B:
mysql> update t_innodb02 set name='DUL' where id=2;
看出innodb存储引擎的t_innodb01由于没有索引,升级为表级锁定。

最后我们来看个特殊的锁定,这个对myisam还是innodb存储引擎都成立
  write_allow_read的锁定:在对表进行ddl时,其他会话可以对表进行读请求读锁,因为mysql时构建的中间表然后rename来实现的

session A下执行ddl
mysql> alter table t_robots_article add name varchar(100) default 'XIAOYU';
Query OK, 37318 rows affected (0.55 sec)
Session b下可以执行
mysql> select count() from t_robots_article01;
  +----------+
  | count(
) |
  +----------+
  | 10000 |
  +----------+
1 row in set (0.00 sec)

锁定机制的合理利用:
各种技术都以其实用的一面,当然也有其不擅长的一面,如何利用设计schemas来发挥出存储引擎的锁定的机制的优点,这个是需要用户的我们好好斟酌的。就myiasm存储引擎来说,并发度较低是需要克服的,减少锁定时间着手,那么相应的可以考虑减少query语句的执行时间,分离复杂的业务分批为相应的query,还是要从应用的设计和query的执行效率上提高。Innodb存储引擎来说,并发度较高,但是资源消耗较多,容易出现死锁,要利用好innodb的行级锁定提高并发,同样需要不错的schemas设计,合理的索引,说到底还是从schemas的设计和query的执行效率上提高。

Mysql统计信息中有下列变量用来计算mysql的锁定有关的信息,系统的锁定的一些统计信息:
  
mysql> show status like 'table_Lock%';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| Table_locks_immediate | 3265412 |
| Table_locks_waited | 11 |
+-----------------------+---------+
2 rows in set (0.00 sec)

Table_locks_immediate表示产生表级锁定立即就能获取的次数,可以简单理解为表级别锁定获取时没有其他线程阻塞,立即就能获取的次数。
table_locks_waited表示出现表级别锁定争用的等待次数,如果table_locks_waited等待较高,则表示myisam存储引擎的表的表级别锁定争用较严重。

下面我们来模拟相应的事务来探索table_locks_immediate和table_locks_waited数据是如何计算的:
Session A:
mysql> lock table t_myisam01 read;
Query OK, 0 rows affected (0.00 sec)
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
mysql> show status like 'table_locks%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 0 |
| Table_locks_waited | 0 |
+-----------------------+-------+

首先我们先lock table t_myisam read,然后flush stats刷新所有的统计信息。

Session B:
mysql> select * from t_myisam01;
+------+------+
| id | name |
+------+------+
| 1 | ORCL |
| 2 | AWR |
| 3 | OCM |
| 3 | OCM |
| 1 | ODU |
+------+------+
5 rows in set (0.00 sec)

然后我们在session B查询t_myisam01表

Session A:
mysql> show status like 'table_locks%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 1 |
| Table_locks_waited | 0 |
+-----------------------+-------+
2 rows in set (0.00 sec)

我们再回到session A 查看table_locks_immediate发觉已经从0变为1,session b获取t_myisam01表级别读锁定时并没有发生任何等待,所以table_locks_immediate会自动添加1.

如果有阻塞了,我们再看看:
Session B:
mysql> delete from t_myisam01 limit 1;

--session b的更新被阻塞

我们再回到session A查看,此时table_locks_waited从0变为1,也不是发生表级别写锁定的等待。
mysql> show status like 'table_locks%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 1 |
| Table_locks_waited | 1 |
+-----------------------+-------+
2 rows in set (0.00 sec)

这里还有个需要注意的是table_locks_immediate并不只是表示myisam存储引擎的表,接下来我们继续看
Session A
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> show status like 'table_locks%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 1 |
| Table_locks_waited | 1 |
+-----------------------+-------+

Session C:
mysql> show create table t_innodb01;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_innodb01 | CREATE TABLE t_innodb01 (
id int(11) DEFAULT NULL,
name varchar(100) DEFAULT NULL,
KEY index_id (id),
KEY index_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
mysql> select * from t_innodb01 where id=1;
+------+------+
| id | name |
+------+------+
| 1 | ORCL |
| 1 | ODU |
+------+------+
2 rows in set (0.00 sec)

回到session A查看:
mysql> show status like 'table_locks%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 2 |
| Table_locks_waited | 1 |
+-----------------------+-------+
2 rows in set (0.00 sec)

看见这里访问innodb存储引擎的表t_innodb01,table_locks_immediate变量也会计算在内,按理说此时id列上有索引,此时应该是请求的行级锁,并不是表级别锁定,关于这个现象不知道是否跟5.5之后的mysql MDL锁有关。

Innodb相关的锁定统计信息变量:
mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 113514 |
| Innodb_row_lock_time_avg | 22702 |
| Innodb_row_lock_time_max | 51036 |
| Innodb_row_lock_waits | 5 |
+-------------------------------+--------+
5 rows in set (0.00 sec)

Innodb_row_lock_current_waits:当前正在等待锁定的数量
Innodb_row_lock_time:系统从启动到现在锁定的总时间
Innodb_row_lock_time_avg:系统从启动到现在每次等待的平均时间
Innodb_row_lock_time_max:系统从启动到现在等待最长的一次的时间
Innodb_row_lock_waits:系统从启动到现在发生innodb存储引擎的锁等待次数

我们需要对innodb_row_lock_time等待总时长,innodb_row_lock_time_avg等待平均时间和innodb_row_lock_waits锁等待次数进行观察,当这三个参数较高,尤其innodb_row_lock_waits等待次数较长,且innodb_row_lock_time_avg平均等待时间也较长时,需要对其进行分析,例如找到相应的query进行优化调整,业务分离等。

同样我们来测试下,看这些系统变量是如何计算的,tab02表是innodb存储引擎

Session A:
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 215267 |
| Innodb_row_lock_time_avg | 30752 |
| Innodb_row_lock_time_max | 51036 |
| Innodb_row_lock_waits | 7 |
+-------------------------------+--------+
5 rows in set (0.00 sec)
mysql> delete from tab02 limit 2;
Query OK, 2 rows affected (0.00 sec)

--Session A删除表的前两行数据不提交

Session B:
mysql> delete from tab02 limit 2;
--session B也删除同样的数据,此时被阻塞

session A:
mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 1 |
| Innodb_row_lock_time | 215267 |
| Innodb_row_lock_time_avg | 26908 |
| Innodb_row_lock_time_max | 51036 |
| Innodb_row_lock_waits | 8 |
+-------------------------------+--------+
5 rows in set (0.00 sec)
此时查看innodb_db_row_current_waits从0变为1,此时正在等待innodb存储引擎的锁的数量为1,innodb_row_lock_waits从7变为8

当sessin B的事务超时后,由系统变量innodb_row_wait_timeout控制
mysql> delete from tab02 limit 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Session A:
mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 265788 |
| Innodb_row_lock_time_avg | 33223 |
| Innodb_row_lock_time_max | 51036 |
| Innodb_row_lock_waits | 8 |
+-------------------------------+--------+
5 rows in set (0.00 sec)
此时innodb_row_lock_time、innodb_row_lock_time_avg和innodb_row_lock_time_max三个参数被更新。

mysql还可以创建innodb_monitor表来记录mysql的innodb存储引擎信息。
mysql> create table innodb_monitor(a int) engine=innodb;
Query OK, 0 rows affected (0.07 sec)

开启innodb的monitor功能后,mysql会将innodb事务信息记录到mysql的error log中以供我们进一步分析。

上述初略的介绍了一点mysql的锁定机制的分类,原理、实现机制、监控、调整建议等,还需要切合实际应用来完善对锁定机制的理解。

添加新评论