MySQL锁机制

MySQL锁的基本介绍

​锁是计算机协调多个进程或线程并发访问某一资源的机制（os的pv操作）

在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。

如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素

从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂

​相对其他数据库而言，MySQL的锁机制比较简单

其最显著的特点是不同的存储引擎支持不同的锁机制

  • 比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；
  • InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁

表级锁

  • 开销小，加锁快
  • 不会出现死锁
  • 锁定粒度大，发生锁冲突的概率最高，并发度最低

行级锁

  • 开销大，加锁慢
  • 会出现死锁
  • 锁定粒度最小，发生锁冲突的概率最低，并发度也最高

仅从锁的角度来说

  • 表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用
  • 而行级锁则更适合于有大量按索引条件并发更新少量不同数据
  • 同时又有并发查询的应用，如一些在线事务处理（OLTP）系统

MyISAM表锁

MySQL的表级锁有两种模式

  • 表共享读锁（Table Read Lock）
  • 表独占写锁（Table Write Lock）

对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求

对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作

MyISAM表的读操作与写操作之间，以及写操作之间是串行的

建表语句

CREATE TABLE mylock ( id int(11) NOT NULL AUTO_INCREMENT, NAME varchar(20) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO mylock (id, NAME) VALUES ('1', 'a');
INSERT INTO mylock (id, NAME) VALUES ('2', 'b');
INSERT INTO mylock (id, NAME) VALUES ('3', 'c');
INSERT INTO mylock (id, NAME) VALUES ('4', 'd');

MyISAM写锁阻塞读

  • 当一个线程获得对一个表的写锁之后，只有持有锁的线程可以对表进行更新操作
  • 其他线程的读写操作都会等待，直到锁释放为止
lock table mylock write;
select * from mylock;
insert into mylock values(5,'e');
select * from mylock;
unlock tables;

MyISAM读阻塞写

  • ​ 一个session使用lock table给表加读锁，这个session可以锁定表中的记录，但更新和访问其他表都会提示错误
  • 另一个session可以查询表中的记录，但更新就会出现锁等待

session1和session2相当于两个不同的进程

  • MyISAM在执行查询语句之前，会自动给涉及的所有表加读锁
  • 在执行更新操作前，会自动给涉及的表加写锁
  • 这个过程并不需要用户干预，因此用户一般不需要使用命令来显式加锁

MyISAM的并发插入问题

MyISAM表的读和写是串行的

在一定条件下，MyISAM也支持查询和插入操作的并发执行

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺

show status like 'table%';
mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 352   |
| Table_locks_waited    | 2     |
+-----------------------+-------+

Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况

InnoDB锁

事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性。

  • 原子性（Actomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行
  • 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态（优先）
  • 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行
  • 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持

并发事务带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多用户的并发操作

当并发事务可能出现问题

脏读

  • 一个事务正在对一条记录做修改，在这个事务提交前，这条记录的数据就处于不一致状态
  • 另一个事务读取这条记录之后，第一个事务回滚了，第二个事务便读取了这些“脏”的数据
  • 据此做出了相关处理，第二个事务提交了，就产生第一个事务未提交的数据依赖关系

不可重复读

  • 一个事务在读取某些数据
  • 另一个事务使得这些数据发生了改变、或某些记录已经被删除了
  • 第一个事务再读，发现数据不一致了

幻读

  • 一个事务按相同的查询条件读取以前检索过的数据
  • 却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”

幻读和不可重复读是同一个级别的

通过事务的隔离机制来解决数据不一致性

  • 数据库的事务隔离越严格，并发副作用就越小，但付出的代价也就越大
  • 事务隔离本质上就是使事务在一定程度上串行化，需要根据具体的业务需求来决定使用哪种隔离级别

| read uncommitted |  √   |    √    |  √   |
|  read committed    |       |    √    |  √   |
| repeatable read    |       |          |  √   |
|   serializable          |       |          |       |

隔离界别解释

  • read uncommitted，不做任何限制，可以读取未提交的数据，所以可能造成脏读，不可重复读，幻读
  • read commited，使用redolog，只能读取提交过的数据，脏读就不可能了
  • 第一个事务读取数据过程中，第二个事务对数据做了操作并且提交，第一个事务根据业务逻辑又读取该行数据发现数据不一致
  • repeatable read，使用MVCC，本质上是加了版本号，隐藏列，记录有效时间和失效时间
  • 串行化，读锁+写锁

查看事务隔离级别 


#
5.1.7 之后
show variables like 'transaction_isolation';
SELECT @@transaction_isolation

# 5.1.7 之前
SELECT @@tx_isolation
show variables like 'tx_isolation'
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

可以通过检查 innodb_row_lock 状态变量来分析系统上的行锁的争夺情况

show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 18702 |
| Innodb_row_lock_time_avg      | 18702 |
| Innodb_row_lock_time_max      | 18702 |
| Innodb_row_lock_waits         | 1     |
+-------------------------------+-------+
  • Innodb_row_lock_current_waits:当前正在等待锁的数量
  • Innodb_row_lock_time:从系统启动到现在锁定总时间长度
  • Innodb_row_lock_time_avg：每次等待所花平均时间
  • Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间长度
  • Innodb_row_lock_waits:系统启动到现在总共等待的次数

Innodb_row_lock_time_avg，Innodb_row_lock_waits，Innodb_row_lock_time这三项比较常用

InnoDB的行锁模式及加锁方法

共享锁（s）：又称读锁

  • 允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
  • 若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁
  • 这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改

排他锁（x）：又称写锁

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

若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁

mysql InnoDB引擎默认的修改数据语句

  • update,delete,insert都会自动给涉及到的数据加上排他锁，
  • select语句默认不会加任何锁类型

加排他锁可以使用select …for update语句

加共享锁可以使用select … lock in share mode语句

  • 加过排他锁的数据行在其他事务是不能被修改的
  • 也不能通过for update和lock in share mode锁的方式查询数据
  • 但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制

InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的

  • InnoDB是只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁
  • Oracle 是通过在数据块中对相应数据行加锁来实现的

在不通过索引条件查询的时候，innodb使用的是表锁而不是行锁

create table tab_no_index(id int,name varchar(10)) engine=innodb;
insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');
  • 开两个进程，两个不同的session
  • session1给一行加排他锁，但session2在请求其他行的排他锁的时候，会出现锁等待
  • 原因是在没有索引的情况下，innodb只能使用表锁

创建带索引的表进行条件查询，innodb使用的是行锁

create table tab_with_index(id int,name varchar(10)) engine=innodb;
alter table tab_with_index add index id(id);
insert into tab_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁

所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现冲突的

按照上表的数据，使用id索引查询，不同的数据行

select * from tab_with_index where id = 1 and name='1' for update
select * from tab_with_index where id = 1 and name='4' for update
  • 开两个进程，两个不同的session
  • session1查询id=1的数据行，然后session2查询id=2的数据，会出现锁等待
  • 原因是使用了相同的索引，所以需要等待锁

MyISAM的表锁

  • 共享读锁（S）之间是兼容的
  • 共享读锁（S）与排他写锁（X）之间，以及排他写锁（X）之间是互斥的
  • 读和写是串行的

在一定条件下，MyISAM允许查询和插入并发执行，我们可以利用这一点来解决应用中对同一表查询和插入的锁争用问题。

MyISAM默认的锁调度机制是写优先，这并不一定适合所有应用

  • 用户可以通过设置LOW_PRIORITY_UPDATES参数
  • 或在INSERT、UPDATE、DELETE语句中指定LOWPRIORITY选项来调节读写锁的争用
  • 由于表锁的锁定粒度大，读写之间又是串行的，因此，如果更新操作较多，MyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突

InnoDB表锁和行锁

  • InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁
  • 在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同

用户可以通过设计和SQL调整等措施减少锁冲突和死锁

  • 尽量使用较低的隔离级别
  • 设计合适的索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会
  • 选择合理的事务大小，小事务发生锁冲突的几率也更小
  • 给记录集显式加锁时，最好一次性请求足够级别的锁，比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁
  • 不同的程序访问一组表时，应尽量按照约定的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会
  • 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
  • 不要申请超过实际需要的锁级别，除非必须，查询时不要显示加锁
  • 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能
论读书
睁开眼，书在面前
闭上眼，书在心里
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/YC-L/p/14465614.html