点滴

MySQL数据库锁

发表于 | 分类于 |

锁的基本概念

锁，在现实生活中是为我们想要隐藏于外界所使用的一种工具。在计算机中，是协调多个进程或线程并发访问某一资源的一种机制。在数据库当中，除了传统的计算资源（CPU、RAM、I/O等等）的争用之外，数据也是一种供许多用户共享访问的资源。如何保证数据并发访问的一致性、有效性，是所有数据库必须解决的一个问题，锁的冲突也是影响数据库并发访问性能的一个重要因素。从这一角度来说，锁对于数据库而言就显得尤为重要。
相对于其他的数据库而言，MySQL的锁机制比较简单，最显著的特点就是不同的存储引擎支持不同的锁机制。根据不同的存储引擎，MySQL中锁的特性可以大致归纳如下：

存储引擎 行锁 表锁 页锁
MyISAM 不支持 支持 不支持
BDB 不支持 支持 支持
InnoDB 支持 支持 不支持

MySQL3种锁的特性：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

很难笼统的说哪种锁最好，只能根据具体应用的特点来说哪种锁更加合适。仅仅从锁的角度来说的话：表锁更适用于以查询为主，只有少量按索引条件更新数据的应用；行锁更适用于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用。

行锁（Row Lock）

对一行记录加锁，只影响一条记录。常用在DML语句中，如INSERT, UPDATE, DELETE等。InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

1
2
3
4
5
6
7
8
9
10
11
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);

直接解释查询：

1
explain select * from test_index where id = 1;

查询结果：type: all ，rows: 5 很明显是会使用全表锁

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
    +----+-------------+------------+------+---------------+------+---------+------+------+-------------+
     
    | 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 |
     
    +----+-------------+------------+------+---------------+------+---------+------+------+-------------+
```  
####  增加索引，id加唯一索引，age加普通索引
``` sql
ALTER TABLE test_index
 
ADD UNIQUE uk_id(id),
ADD index idx_age(age);
 
mysql> explain select * from test_index where id = 1;

查询结果：锁定一条记录

1
2
3
4
5
6
7
8
9
10
11
12
 
+----+-------------+------------+-------+---------------+-------+---------+-------+------+-------+
 
| 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:

两个事务，TX1加共享行锁, 查询age=17的记录， TX2往数据库里插入一条age=18的记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
set autocommit=0;//MySQL默认操作模式就是autocommit自动提交模式。这就表示除非显式地开始一个事务，
否则每个查询都被当做一个单独的事务自动执行。我们可以通过设置autocommit的值改变是否是自动提交autocommit模式。
值0和OFF都是一样的，当然，1也就表示ON。通过以上设置autocommit=0，则用户将一直处于某个事务中，直到执行一条
commit提交或rollback语句才会结束当前事务重新开始一个新的事务。
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

显然被锁住的不止age=17的那一行
执行sql查看加锁的具体信息

1
select * from information_schema.innodb_locks

表锁

MySQL表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。什么意思呢，就是说对MyISAM表进行读操作时，它不会阻塞其他用户对同一表的读请求，但会阻塞 对同一表的写操作；而对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作。
InnoDB锁与MyISAM锁的最大不同在于：一是支持事务（TRANCSACTION），二是采用了行级锁。我们知道事务是由一组SQL语句组成的逻辑处理单元，其有四个属性（简称ACID属性），分别为：
原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全部执行，要么全都不执行；
一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态；
隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行；
持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。
InnoDB有两种模式的行锁：
共享锁(S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

1
( Select * from table_name where ......lock in share mode)

排他锁(X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

1
(select * from table_name where.....for update)

为了允许行锁和表锁共存，实现多粒度锁机制；同时还有两种内部使用的意向锁（都是表锁），分别为意向共享锁和意向排他锁。InnoDB行锁是通过给索引项加锁来实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表锁！
意向共享锁(IS)
事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁(IX)
事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

悲观锁

正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）

乐观锁

相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。
而乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。
本质上，数据库的乐观锁做法和悲观锁做法主要就是解决下面假设的场景，避免丢失更新问题：

并发事务处理带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况。
更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题－－最后的更新覆盖了由其他事务所做的更新。例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖另一个编辑人员所做的更改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题。
脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做”脏读”。
不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。
幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

MySQL锁机制详细参考链接

打赏一下

热评文章