前言
关于数据库锁,是一个很重要的知识点;
不少人在开发的时候,应该很少会注意到这些锁的问题,也很少会给程序加锁(除了库存这些对数量准确性要求极高的情况下);
一般也就听过常说的乐观锁和悲观锁,了解过基本的含义之后就没了,没有去实际的操作过,本文将简单的整理一下数据库锁的知识,希望对大家有所帮助;
引入
本文参考文章:数据库的两大神器
数据库锁
简介
在MySQL中锁看起来是很复杂的,因为有一大堆的东西和名词:排它锁,共享锁,表锁,页锁,间隙锁,意向排它锁,意向共享锁,行锁,读锁,写锁,乐观锁,悲观锁,死锁。这些名词有的博客又直接写锁的英文的简写--->X锁,S锁,IS锁,IX锁,MMVC等等之类。锁的相关知识又跟存储引擎,索引,事务的隔离级别都是关联的;
以上的一大堆锁可能很多人都只是知道一些概念,但是我们的程序在一般情况下还是可以跑得好好的。因为这些锁数据库隐式帮我们加了:
- 对于
UPDATE、DELETE、INSERT
语句,InnoDB会自动给涉及数据集加排他锁(X),也就是我们常说的写锁; - MyISAM在执行查询语句
SELECT
前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT
等)前,会自动给涉及的表加写锁,这个过程并不需要用户干预
表锁和行锁
从锁的粒度我们可以分为两大类,它们各自的特点如下:
- 表锁:开销小,加锁快;不会出现死锁;锁定力度大,发生锁冲突概率高,并发度低;
- 行锁:开销大,加锁慢;会出现死锁;锁定力度小,发生锁冲突的概率低,并发度高;
同样,不同的存储引擎支持的锁的力度也不一样:
- InnoDB:表锁行锁都支持(InnoDB的行锁是基于索引的 ,稍后会演示);
- MyISAM:只支持表锁;
表锁
表锁也分为两种模式:
- 表读锁(Table Read Lock)
- 表写锁(Table Write Lock)
- 读读不阻塞:当前用户读取数据,其他用户也在读取数据,不会加锁;
- 读写阻塞:当前用户在读取数据的时候,其他用户不能修改当前用户读的数据;
- 写写阻塞:当前用户在修改数据,其他用户不能修改当前用户正在修改的数据;
总结得到:
- 读读不阻塞,读写阻塞,写写阻塞 ;
- 读锁和写锁是互斥的,读写操作是串行 ;
- 在mysql里边,写锁是优先于读锁的 ;
行锁
我们使用MySQL一般是使用的InnoDB引擎,上面也提到了InnoDB和MyISAM的一些区别:
- InnoDB行锁表锁都支持,MyISAM只支持表锁;
- InnoDB支持事务,MyISAM不支持;
InnoDB实现了以下两种类型的行锁:
- 共享锁(s锁):允许一个事务去读一行,会阻止其他事务获取相同数据集的排他锁(读取数据的时候不允许修改)
- 也被称为读锁:读锁是共享的,多个线程可以同时读取统一数据集,但是不允许其他线程进行修改(也就是不允许其他事务获取相同数据集的排他锁);
- 排他锁(x锁):允许获取排他锁去做更新操作,阻止其他事务获取相通数据的共享锁和排他锁(一个事务修改数据的时候,阻止其他事务对相同数据集做更新或者查询操作);
- 也被称为写锁:写锁是排他的,写锁会阻塞其他的写锁和读锁;
意向锁
为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁:
- 意向共享锁(IS):事务打算给数据行加共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁;
- 意向排他锁(IX):事务打算给数据行加排他锁,事务再给一个数据行加排他锁前必须先取得该表的IX锁;
意向锁也是数据库隐式帮我们做了,不需要程序员操心!
表锁行锁测试
准备
上面我们提到了InnoDB支持行锁,但是是基于索引的情况,下面我们来实际的看一下:
首先我们用客户端连接上MySQL数据库,为了测试锁的效果,我们需要打开两个或者两个以上的客户端(我打开了两个)然后创建一个数据库;
CREATE DATABASE test CHARACTER SET utf8;
然后我们需要建立一个表:
CREATE TABLE `user` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `username` varchar(255) NOT NULL, `age` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB CHARSET=utf8;
我们简单的建了一个user表,表中有三个字段,其中id为自增主键,大家都知道主键是自带索引的,也就是聚簇索引(主键索引),其他的字段都是不带索引的。
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | user | +----------------+ 1 row in set (0.01 sec)
现在我们简单的往里面添加几条数据:
INSERT INTO `user`(username,age) VALUES ('tom',23),('joey',22),('James',21),('William',20),('David',24);
mysql> select * from user; +----+----------+-----+ | id | username | age | +----+----------+-----+ | 1 | tom | 23 | | 2 | joey | 22 | | 3 | James | 21 | | 4 | William | 20 | | 5 | David | 24 | +----+----------+-----+ 5 rows in set (0.00 sec)
测试
好的,现在前提都已经弄好了,我们可以开始测试了:
我们知道MySQL的事务是自动提交的,为了测试,我们需要把事务的自动提交关闭;
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.01 sec)
现在我们来查看一下MySQL的事务提交状态:
mysql> show VARIABLES like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set, 1 warning (0.04 sec)
从上面可以看出,我们把事务的自动提交已经关闭了,下面我们开始测试(打开的窗口都需要关闭事务的自动提交);
行锁测试
首先,我打开了两个窗口,分别为A和B,现在,我们两个窗口的状态都已经调整完毕(关闭事务自动提交)。我们在A窗口,输入以下语句:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id = 1 for update;
+----+----------+-----+
| id | username | age |
+----+----------+-----+
| 1 | tom | 23 |
+----+-------