浅析MySQL的锁机制

  • 时间:
  • 浏览:1
  • 来源:大发幸运飞艇APP下载_大发幸运飞艇APP官方

begin;

insert ...   加锁1

update ...   加锁2

commit;      事务提交时,释放锁1,锁2

数据库锁定机制简单来说假如数据库为了保证数据的一致性而使各种共享资源在被并发访问访问变得有序所设计的这种生活规则;对于任何这种生活数据库来说都都能能 有相应的锁定机制,Mysql假如例外。

2.事务隔离级别和锁的关系

常用的2种隔离级别是:已提交读(Read committed)和可重复读(Repeatable read);

mysql> begin;

Query OK, 0 rows affected

 

mysql> insert into test_lock values(null,'zhaohui3',2);

Query OK, 1 row affected

 

mysql> commit;

Query OK, 0 rows affected

Session1执行查询

1.行级锁定

MySQL 各存储引擎使用了这种生活类型(级别)的锁定机制:行级锁定,页级锁定和表级锁定。

3.1准备测试表

CREATE TABLE `test_lock` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(255) NOT NULL,

  `type` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

 

mysql> insert into test_lock values(null,'zhaohui',1);

mysql> insert into test_lock values(null,'zhaohui2',2);

3.2查看和设置隔离级别

mysql> SELECT @@tx_isolation;

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

| @@tx_isolation  |

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

| REPEATABLE-READ |

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

1 row in set

 

mysql> set session transaction isolation level read committed;

Query OK, 0 rows affected

 

mysql> SELECT @@tx_isolation;

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

| @@tx_isolation |

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

| READ-COMMITTED |

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

3.3模拟多个事务交叉执行

Session1执行查询

mysql> select * from test_lock where type=2;

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

| id | name     | type |

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

|  2 | zhaohui2 |    2 |

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

1 row in set

都能能 发现可重复读(Repeatable read)隔离级别下,假如会一有一一两个劲老出幻读的问题;

分析一下意味:怎么通过悲观锁的最好的办法去实现可重复读和不一有一一两个劲老出幻读的问题,对读取的数据加共享锁,对同样的数据执行更新操作就只能等候,另有一一两个 就都能能 保证可重复读,因此对于不一有一一两个劲老出幻读的问题无法通过锁定行数据来处里;

最终看多的问题是没有幻读的问题,同时机会对读取的数据加共享锁,更新相同数据应该会等候,里面的实例中并没有一有一一两个劲老出等候,这种这种mysql组织组织结构应该还有这种锁机制--MVCC机制;

mysql> begin;

Query OK, 0 rows affected

 

mysql> select * from test_lock where type=2;

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

| id | name     | type |

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

|  2 | zhaohui2 |    2 |

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

1 row in set

Session2更新数据

一次会将整张表锁定,该锁定机制最大的特点是实现逻辑非常简单,带来的系统负面影响最小,因此都能能 处里死锁问题;弊端假如锁定资源争用的概率最高,并发处都能能力最低;

使用表级锁定的主假如MyISAM,Memory,CSV等这种非事务性存储引擎。

当前读和Gap锁

mysql> begin;

Query OK, 0 rows affected

 

mysql> select * from test_lock where type=2 for update;

Empty set

Session3更新数据

mysql> select * from test_lock where id=1;

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

| id | name        | type |

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

|  1 | zhaohui_new |    1 |

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

1 row in set

 

mysql> commit;

Query OK, 0 rows affected

Session1中一有一一两个劲老出了不可重复读(NonRepeatable Read),也假如在查询的很久没有锁住相关的数据,意味一有一一两个劲老出了不可重复读,因此写入、修改和删除数据还是加锁了,如下所示:

mysql> begin;

Query OK, 0 rows affected

 

mysql> update test_lock set name='zhaohui3_new' where id=3;

1205 - Lock wait timeout exceeded; try restarting transaction

Session1和Session2使用了共享锁,这种这种都能能 所处多个,好的反义词冲突,因此Session3更新操作都能能 加进排他锁,和共享锁只能同时所处;

5.悲观锁SQL使用

5.1共享锁使用(lock in share mode)

Session1查询数据

3.表级锁定

mysql> begin;

Query OK, 0 rows affected

 

mysql> update test_lock set name='zhaohui_new3' where id=1;

1205 - Lock wait timeout exceeded; try restarting transaction

Session2更新在更新同第第一根数据的很久超时了,在更新数据的很久加进了排他锁;

select ...for update,

select ...lock in share mode,

insert,update,delete;

以上sql这种生活会加悲观锁,这种这种不所处不可重复读的问题,剩下的假如幻读的问题;

Session1执行当前读

4.1查看和设置隔离级别

mysql> set session transaction isolation level repeatable read;

Query OK, 0 rows affected

 

mysql> SELECT @@tx_isolation;

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

| @@tx_isolation  |

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

| REPEATABLE-READ |

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

1 row in set

4.2模拟多个事务交叉执行

Session1执行查询

2.页级锁定

本文首先从Mysql的悲观锁出发,因此介绍了悲观锁和事务隔离级别之间的关系,并分析为那先 没有使用悲观锁来实现隔离级别;因此从问题出发分别介绍了MVCC和Gap锁是怎么处里了不可重复读的问题和幻读的问题;最后介绍了乐观锁一有一一两个劲被用在读数据远大于写数据的系统中。

锁定颗粒度介于行级锁定与表级锁之间,每页有多行数据,并发处都能能力以及获取锁定所都能能 的资源开销在两者之间;

页级锁定主假如BerkeleyDB 存储引擎;

mysql> begin;

Query OK, 0 rows affected

 

mysql> update test_lock set name='zhaohui_new' where id=1;

  

Query OK, 1 row affected

Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;

Query OK, 0 rows affected

Session1执行查询

3.已提交读

mysql> begin;

Query OK, 0 rows affected

mysql> select * from test_lock where id=1;

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

| id | name    | type |

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

|  1 | zhaohui |    1 |

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

1 row in set

Session2更新数据

mysql> begin;

Query OK, 0 rows affected

 

mysql> select * from test_lock where type=2 for update;

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

| id | name         | type |

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

|  2 | zhaohui2_new |    2 |

|  3 | zhaohui3     |    2 |

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

2 rows in set

Session2查询数据

Session3插入数据

两段锁协议(2PL)

即事务的执行分为有一一两个 阶段:

当然并都是说悲观锁就没有用了,在数据更新的很久数据库默认还是使用悲观锁的,这种这种MVCC是都能能 整合起来同时使用的(MVCC+2PL),用来处里读-写冲突的无锁并发控制;

MVCC使用快照读的最好的办法,处里了不可重复读和幻读的问题,如里面的实例所示:select查询的一有一一两个劲是快照信息,只能加进任何锁;

以上实例中使用的select最好的办法把它称为快照读(snapshot read),嘴笨 事务的隔离级别的读还有另一层含义:读取数据库当前版本数据–当前读(current read);

mysql> begin;

Query OK, 0 rows affected

 

mysql> update test_lock set name='zhaohui2_new' where type=2;

Query OK, 1 row affected

Rows matched: 1  Changed: 1  Warnings: 0

 

mysql> commit;

Query OK, 0 rows affected

Session1执行查询

多版本并发控制(Multiversion Concurrency Control):每一有一一两个 写操作都是创建一有一一两个 新版本的数据,读操作会从有限多个版本的数据中选泽一有一一两个 最大慨的结果直接返回;读写操作之间的冲突就不再都能能 被关注,而管理和快速选泽数据的版本就成了MVCC都能能 处里的主要问题。

为那先 要引入此机制,首先通过悲观锁来处里读请求是很耗性能的,其次数据库的事务大都是只读的,读请求是写请求的这种这种倍,最后机会没有并发控制机制,最坏的状况也是读请求读到了机会写入的数据,这对这种这种应用完都是都能能 接受的;

mysql> begin;

Query OK, 0 rows affected

 

mysql> insert into test_lock values(null,'zhaohui_001',1);

1205 - Lock wait timeout exceeded; try restarting transaction

为那先 明明锁住的是type=2的数据,当插入type=1也会锁等候,机会InnoDB对于行的查询都是采用了Next-Key锁,锁定的都是单个值,假如一有一一两个 范围(GAP);

机会当前type类型包括:1,2,4,6,8,10锁住type=2,没有type=1,2,3会被锁住,里面的越多,锁住的是一有一一两个 区间;另有一一两个 也就保证了当前读假如会一有一一两个劲老出幻读的问题;

注:type字段加进了索引,机会没有加进索引,gap锁会锁住整张表;

Mysql几种锁定机制类型

既然数据库提供了共享锁和排他锁,那具体用在那先 地方:

1.1在数据库操作中,为了有效保证并发读取数据的正确性,提出的事务隔离级别,隔离级别就使用了锁机制;

1.2提供了相关的SQL,都能能 方便的在多线程 池池中使用;

mysql> select * from test_lock where type=2 for update;

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

| id | name           | type |

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

|  2 | zhaohui2_new   |    2 |

|  3 | zhaohui3_new_1 |    2 |

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

2 rows in set

Session2执行插入

mysql> begin;

Query OK, 0 rows affected

 

mysql> update test_lock set name='zhaohui_new2' where id=1;

Query OK, 1 row affected

Rows matched: 1  Changed: 1  Warnings: 0

Session2更新数据

乐观锁

再来看一下可重复读(Repeatable read)问题,通过MVCC机制读操作只读该事务现在很久刚开始前的数据库的快照(snapshot), 另有一一两个 在读操作越多阻塞写操作,写操作越多阻塞读操作的同时,处里了脏读和不可重复读;

mysql> begin;

Query OK, 0 rows affected

 

mysql> update test_lock set name='zhaohui3_new' where id=3;

1205 - Lock wait timeout exceeded; try restarting transaction

排他锁只能有一有一一两个 同时所处,所有Session2和Session3都将等等超时;

本文重点介绍Innodb存储引擎使用的行级锁定;

4.可重复读

数据库隔离级别:未提交读(Read uncommitted),已提交读(Read committed),可重复读(Repeatable read)和可串行化(Serializable);

未提交读(Read uncommitted):机会读取到这种会话中未提交事务修改的数据,会一有一一两个劲老出脏读(Dirty Read);

已提交读(Read committed):只能读取到机会提交的数据,会一有一一两个劲老出不可重复读(NonRepeatable Read);

可重复读(Repeatable read):InnoDB默认级别,越多一有一一两个劲老出不可重复读(NonRepeatable Read),因此会一有一一两个劲老出幻读(Phantom Read);

可串行化(Serializable):强制事务排序,使之不机会相互冲突,从而处里幻读问题,使用表级共享锁,读写相互都是阻塞;

mysql> begin;

Query OK, 0 rows affected

 

mysql> select * from test_lock where type=2 lock in share mode;

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

| id | name         | type |

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

|  2 | zhaohui2_new |    2 |

|  3 | zhaohui3     |    2 |

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

2 rows in set

Session2查询数据

行级锁定(悲观锁)

5.2排他锁使用(for update)

Session1查询数据

机会在加锁2的很久,加锁不成功,则进入等候状况,直到加锁成功才继续执行;

机会有另外一有一一两个 事务获取锁的很久顺序刚好相反,是有机会意味死锁的;为此有了一次性封锁法,要求事务都能能 一次性将所有要使用的数据完全加锁,因此就只能继续执行;

begin;

select id,name,version from test_lock where id=1;

....

update test_lock set name='xxx',version=version+1 where id=1 and version=${version};

commit;

先查询后更新,都能能 保证原子性,要么使用悲观锁的最好的办法,对整个事务加锁;要么使用乐观锁的最好的办法,机会在读多写少的系统中,乐观锁性能更好;

锁定对象的颗粒度很小,只对当前行进行锁定,这种这种所处锁定资源争用的概率也最小,不能给予应用多线程 池池尽机会大的并发处都能能力;弊端假如获取锁释放锁更加频繁,系统消耗更大,同时行级锁定也最容易所处死锁;

行级锁定的主假如Innodb存储引擎和NDB Cluster存储引擎;

mysql> begin;

Query OK, 0 rows affected

 

mysql> select * from test_lock where type=2 lock in share mode;

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

| id | name         | type |

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

|  2 | zhaohui2_new |    2 |

|  3 | zhaohui3     |    2 |

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

2 rows in set

Session3更新数据

区别普通的select查询,当前读对应的sql包括:

多版本并发控制MVCC

总结

Innodb的行级锁定同样分为这种生活类型:共享锁和排他锁;

共享锁:当一有一一两个 事务获得共享锁很久,它只都能能 进行读操作,这种这种共享锁也叫读锁,多个事务都能能 同时获得某一行数据的共享锁;

排他锁:而当一有一一两个 事务获得一行数据的排他锁时,就都能能 对该行数据进行读和写操作,这种这种排他锁也叫写锁,排他锁与共享锁和这种的排他锁不兼容;

第一阶段是获得封锁的阶段,称为扩展阶段;第二阶段是释放封锁的阶段,称为收缩阶段;

mysql> select * from test_lock where type=2;

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

| id | name     | type |

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

|  2 | zhaohui2 |    2 |

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

1 row in set

都能能 发现2次查询的数据结果是一样的,实现了可重复读(Repeatable read),再来看一下是有无有幻读(Phantom Read)的问题;

乐观锁是这种生活思想,认为事务间争用没有没有多,和悲观锁是相对的,乐观锁在java的并发包中少许的使用;一般采用以下最好的办法:使用版本号(version)机制来实现,版本号假如为数据加进一有一一两个 版本标志,一般在表中加进一有一一两个 version字段;当读取数据的很久把version也取出来,因此version+1,更新数据库的很久对比第一次取出来的version和数据库里面的version是有无一致,机会一致则更新成功,因此失败进入重试,具体使用大致如下:

两段锁协议规定所有的事务应遵守的规则:

1.在对任何数据进行读、写操作很久,首比较慢申请并获得对该数据的封锁;

2.在释放一有一一两个 封锁很久,事务不再申请和获得其它任何封锁;

1.共享锁和排他锁

定理:若所有事务均遵守两段锁协议,则那先 事务的所有交叉调度都是可串行化的(串行化不为什么会么会要,尤其是在数据恢复和备份的很久);

Session1更新数据