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 锁兼容性
如下表:
独占锁和所有的锁都冲突,意向共享锁和共享锁兼容(这是肯定的),还和意向独占锁兼容。所以加了意向共享锁的时候,可以修改行级非共享锁的记录。同理,加了意向独占锁的时候,可以检索这些加了独占锁的记录。
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张表作为测试表。