MySQL数据库锁介绍

页面导航:首页 > 数据库 > Mysql > MySQL数据库锁介绍

MySQL数据库锁介绍

来源: 作者: 时间:2016-02-04 09:03 【

MySQL数据库锁介绍1 锁的基本概念当并发事务同时访问一个资源时,有可能导致数据不一致,因此需要一种机制来将数据访问顺序化,以保证数据库数据的一致性。锁就是其中的一种机制
MySQL锁介绍
 
1. 锁的基本概念
当并发事务同时访问一个资源时,有可能导致数据不一致,因此需要一种机制来将数据访问顺序化,以保证数据库数据的一致性。
锁就是其中的一种机制。
我们可以用商场的试衣间来做个比喻。商场里得每个试衣间都可供多个消费者使用,因此可能出现多个消费者同时试衣服需要使用试衣间。为了避免冲突,试衣间装了锁,某一个试衣服的人在试衣间里把锁锁住了,其他顾客就不能再从外面打开了,只能等待里面的顾客,试完衣服,从里面把锁打开,外面的人才能进去。
 
2. 锁的基本类型
数据库上的操作可以归纳为两种:读和写。
多个事务同时读取一个对象的时候,是不会有冲突的。同时读和写,或者同时写才会产生冲突。因此为了提高数据库的并发性能,通常会定义两种锁:共享锁和排它锁。
2.1 共享锁(Shared Lock,也叫S锁)
共享锁(S)表示对数据进行读操作。因此多个事务可以同时为一个对象加共享锁。(如果试衣间的门还没被锁上,顾客都能够同时进去参观)
产生共享锁的sql:select * from ad_plan lock in share mode;
2.2 排他锁(Exclusive Lock,也叫X锁)
排他锁也叫写锁(X)。
排他锁表示对数据进行写操作。如果一个事务对对象加了排他锁,其他事务就不能再给它加任何锁了。(某个顾客把试衣间从里面反锁了,其他顾客想要使用这个试衣间,就只有等待锁从里面给打开了)
产生排他锁的sql: select * from ad_plan for update;
 
对于锁,通常会用一个矩阵来描述他们之间的冲突关系。
      S      X  
S    +      –  
X    –      –  
+ 代表兼容, - 代表不兼容
 
时间\事务
 
Tx1:
 
Tx2:
 
T1

set autocommit=0;

set autocommit=0;

T2

select * from ad_plan lock in share mode;



T3



update ad_plan set name='' ; blocking

 

执行sql: select * from information_schema.innodb_locks; 可以查看锁。
 
3. 锁的粒度
就是通常我们所说的锁级别。MySQL有三种锁的级别:页级、表级、行级。
相对其他数据库而言,MySQL的锁机制比较简单,其最 显著的特点是不同的存储引擎支持不同的锁机制。
比如,MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);BDB存储引擎采用的是页面锁(page-level locking),但也支持表级锁;InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。
MySQL这3种锁的特性可大致归纳如下:
表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。
数据库引擎通常必须获取多粒度级别上的锁才能完整地保护资源。
 
3.1 行锁(Row Lock)
对一行记录加锁,只影响一条记录。
 
通常用在DML语句中,如INSERT, UPDATE, DELETE等。
 
InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!
用下面例子来说明一下:
CREATE TABLE test_index(id int , name VARCHAR(50),age int )engine=innodb ;

INSERT INTO test_index values(1,'张一',15);

INSERT INTO test_index values(3,'张三',16);

INSERT INTO test_index values(4,'张四',17);

INSERT INTO test_index values(5,'张五',19);

INSERT INTO test_index values(7,'刘琦',19);

 

不再启用多事务描述了,直接解释执行查询语句
 explain select * from test_index where id = 1;
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | test_index | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |

+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

 

type: all ,rows: 5 很明显是会使用全表锁。
增加索引,id加唯一索引,age加普通索引。
ALTER TABLE test_index

ADD UNIQUE uk_id(id),
ADD index idx_age(age);

> explain select * from test_index where id = 1;

+----+-------------+------------+-------+---------------+-------+---------+-------+------+-------+

| id | select_type | table      | type  | possible_keys | key   | key_len | ref   | rows | Extra |

+----+-------------+------------+-------+---------------+-------+---------+-------+------+-------+

|  1 | SIMPLE      | test_index | const | uk_id         | uk_id | 5       | const |    1 | NULL  |

+----+-------------+------------+-------+---------------+-------+---------+-------+------+-------+

type: const ,key:uk_id,rows:

 

 1 很明显是会使用行锁,锁定一条记录。
 
 
 
下面做个有趣的实验:两个事务,TX1加共享行锁, 查询age=17的记录, TX2往数据库里插入一条age=18的记录。
TX1:
mysql> set autocommit=0;

mysql> select * from test_index where age=17 lock in share mode;

+------+------+------+

| id   | name | age  |

+------+------+------+

|    4 | 张四 |   17 |

+------+------+------+

1 row in set (0.00 sec)



TX2:
mysql> set autocommit=0;

mysql> insert test_index values(8,'test',18);

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

 
结果是TX2获取锁超时,看来TX1锁定的并不止age=17的记录,不存在的间隙age=18,也被加锁了。
 
 
执行select * from information_schema.innodb_locks;可以看到加锁的具体信息
+--------------+-------------+-----------+-----------+---------------------+------------+------------+-----------+----------+--------------------+

| lock_id      | lock_trx_id | lock_mode | lock_type | lock_table          | lock_index | lock_space | lock_page | lock_rec | lock_data          |

+--------------+-------------+-----------+-----------+---------------------+------------+------------+-----------+----------+--------------------+

| 45288:57:5:5 | 45288       | X,GAP     | RECORD    | `test`.`test_index` | idx_age    |         57 |         5 |        5 | 19, 0x000000000208 |

| 45289:57:5:5 | 45289       | S,GAP     | RECORD    | `test`.`test_index` | idx_age    |         57 |         5 |        5 | 19, 0x000000000208 |

+--------------+-------------+-----------+-----------+---------------------+------------+------------+-----------+----------+--------------------+

 

 
行锁S、X锁上做了一些精确的细分,在代码中称作Precise Mode。这些精确的模式,  使的锁的粒度更细小。可以减少冲突。  
A.间隙锁(Gap Lock),只锁间隙。  
B.记录锁(Record Lock) 只锁记录。  
C.Next-Key Lock(代码中称为Ordinary Lock),同时锁住记录和间隙。
D.插入意图锁(Insert Intention Lock),插入时使用的锁。在代码中,插入意图锁,实际上是GAP锁上加了一个LOCK_INSERT_INTENTION的标记。
 
行锁兼容矩阵
 
    G I R N
G + + + +
I + +
R + +
N + + –+ 代表兼容, -代表不兼容. 
G代表Gap锁,I代表插入意图锁,R代表记录锁,N代表Next-Key锁.  
S锁和S锁是完全兼容的,因此在判别兼容性时不需要对比精确模式。  
精确模式的检测,用在S、X和X、X之间。
从这个矩阵可以看到几个特点:  
A. INSERT操作之间不会有冲突。  
B. GAP,Next-Key会阻止Insert。  
C. GAP和Record,Next-Key不会冲突  
D. Record和Record、Next-Key之间相互冲突。  
E. 已有的Insert锁不阻止任何准备加的锁。
 
Gap lock:
间隙锁只会出现在辅助索引(index)上,唯一索引(unique)和主键索引是没有间隙锁。
间隙锁(无论是S还是X)只会阻塞insert操作。
间隙锁的目的是为了防止幻读(但是需要应用自己加锁,innodb默认不会加锁防止幻读)。
3.2 页面锁
3.3 表锁(Table Lock)
对整个表加锁,影响标准的所有记录。通常用在DDL语句中,如DELETE TABLE,ALTER TABLE等。  
很明显,表锁影响整个表的数据,因此并发性不如行锁好。
在MySQL 数据库中,使用表级锁定的主要是MyISAM,Memory等一些非事务性存储引擎。
 
因为表锁覆盖了行锁的数据,所以表锁和行锁也会产生冲突(商场关门了,试衣间自然也没法使用了)。如:
A. trx1 BEGIN
  B. trx1 给 T1 加X锁,修改表结构。
  C. trx2 BEGIN
  D. trx2 给 T1 的一行记录加S或X锁(事务被阻塞,等待加锁成功)
trx1要操作整个表,锁住了整个表。那么trx2就不能再对T1的单条记录加X或S锁,去读取或修这条记录。 
3.3.1 表锁—意向锁
为了方便检测表级锁和行级锁之间的冲突,就引入了意向锁。
A. 意向锁分为意向读锁(IS)和意向写锁(IX)。  
B. 意向锁是表级锁,但是却表示事务正在读或写某一行记录,而不是整个表。     所以意向锁之间不会产生冲突,真正的冲突在加行锁时检查。  
C. 在给一行记录加锁前,首先要给该表加意向锁。也就是要同时加表意向锁和行锁。
 
采用了意向锁后,上面的例子就变成了:
A. trx1 BEGIN  
B. trx1 给 T1 加X锁,修改表结构。  
C. trx2 BEGIN  
D. trx2 给 T1 加IX锁(事务被阻塞,等待加锁成功)  
E. trx2 给 T1 的一行记录加S或X锁.
 
表锁的兼容性矩阵
  IS IX S X
IS + + +
IX + +
S + +
X –+ 代表兼容, -代表不兼容
意向锁之间不会冲突, 因为意向锁仅仅代表要对某行记录进行操作。在加行锁时,会判断是否冲突。
 
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<