MySQL/MariaDB的锁超详细讲解

1.事务提交的方式

在MariaDB/MySQL中有3种事务提交的方式。

1.显式开启和提交。

使用begin或者start transaction来显式开启一个事务,显式开启的事务必须使用commit或者rollback显式提交或回滚。几种特殊的情况除外:行版本隔离级别下的更新冲突和死锁会自动回滚。

在存储过程中开启事务时必须使用start transaction,因为begin会被存储过程解析为begin...end结构块。

另外,MariaDB/MySQL中的DDL语句会自动提交前面所有的事务(包括显示开启的事务),而在SQL Server中DDL语句还是需要显式提交的,也就是说在SQL Server中DDL语句也是可以回滚的。

2.自动提交。(MySQL默认的提交方式)

不需要显式begin或者start transaction来显式开启事务,也不需要显式提交或回滚事务,每次执行DML和DDL语句都会在执行语句前自动开启一个事务,执行语句结束后自动提交或回滚事务。

3.隐式提交事务

隐式提交事务是指执行某些语句会自动提交事务,包括已经显式开启的事务。

会隐式提交事务的语句主要有:

(1).DDL语句(其中有truncate table)。

(2).隐式修改mysql数据库架构的操作:create user,drop user,grant,rename user,revoke,set password。

(3).管理语句:analyze table、cache index、check table、load index into cache、optimize table、repair table。

通过设置 auto_commit 变量值为1或0来设置是否自动提交,为1表示自动提交,0表示关闭自动提交,即必须显式提交。但是不管设置为0还是1,显式开启的事务必须显式提交,而且隐式提交的事务不受任何人为控制。

2.MariaDB/MySQL中的锁

锁和事务的实现是存储引擎内的组件管理的,而MariaDB/MySQL是插件式的存储引擎实现方式,所以不同的存储引擎可以支持不同级别的锁和事务。

2.1 不同存储引擎支持的锁级别

MariaDB/MySQL相比其他数据产品来说,支持的锁比较简单。

1.MyISAM、Aria(MariaDB中对myisam的改进版本)和memory存储引擎只支持表级别的锁。

2.innodb支持行级别的锁和表级别的锁,默认情况下在允许使用行级别锁的时候都会使用行级别的锁。

3.DBD存储引擎支持页级别和表级别的锁。

2.2 锁类型

在MariaDB/MySQL中只有简单的几种锁类型:

1.共享锁(S):即读锁,不涉及修改数据,在检索数据时才申请的锁。

2.独占锁(X):增、删、改等涉及修改操作的时候,都会申请独占锁。

以上是支持表锁的存储引擎都会有的锁类型。以下两种是支持行锁或页锁才会有的锁类型,也就是说myisam没有下面的锁,而innodb有。

3.意向共享锁(IS):获取低级别共享锁的同时,在高级别上也获取特殊的共享锁,这种特殊的共享锁是意向共享锁。

4.意向独占锁(IX):获取低级别独占锁的同时,在高级别上也获取特殊的独占锁,这种特殊的独占锁是意向独占锁。

低级别锁表示的是行锁或页锁,意向锁可能是多条记录组成的范围锁,也可能直接就是表意向锁。

2.3 锁兼容性

如下表:

MySQL/MariaDB的锁超详细讲解

独占锁和所有的锁都冲突,意向共享锁和共享锁兼容(这是肯定的),还和意向独占锁兼容。所以加了意向共享锁的时候,可以修改行级非共享锁的记录。同理,加了意向独占锁的时候,可以检索这些加了独占锁的记录。

3.MyISAM的表级锁(lock tables和unlock语句)

MariaDB/MySQL中myisam和innodb都支持表级锁。表级锁分为两种:读锁(read lock)和写锁(write lock)。本节所述均为myisam支持的,同样innodb也一样支持。

可以通过语句来实现表级锁的锁定和解锁,这些语句的操作环境是当前客户端会话(即作用范围是会话)。锁表的时候可以一次性锁定多张表,并使用不同的锁,而解锁的时候只能一次性解锁当前客户端会话的所有表。

LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ...
lock_type:
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
 
UNLOCK TABLES

lock tables命令可以锁表或锁视图,锁视图的时候会自动将视图内的基表加上对应类型的锁。由于MariaDB/MySQL中触发器是基于表的,所以lock tables锁定表的时候,触发器内使用的表也都会被锁定。

例如:table1上有一个如下触发器:

CREATE TRIGGER trigger1 AFTER INSERT ON table1 FOR EACH ROW
BEGIN
  INSERT INTO table2 VALUES (1);
  UPDATE table3 SET writes = writes+1
    WHERE id = NEW.id AND EXISTS (SELECT id FROM table4);
END;

如果为table1加上写锁,则table2、table3都会加上写锁,而table4会加上读锁。

lock tables命令会隐式释放当前客户端会话中之前的所有锁。

现在创建3张表作为测试表。

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/3810f55420a83dd159a316202f796b19.html