欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

mysql的事物以及锁原理

程序员文章站 2024-01-13 16:05:16
...

1、事务

我们把同时满足原子性、一致性、隔离行、持久性的一个或者
多个数据库操作称为一个事务。

1.2、事务的使用

1.2.1、BEGIN；

BEGIN语句就代表开启一个事务。

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> sql...

1.2.2、START TRANSACTION

与BEGIN一样，都表示开启一个事务

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> sql...

1.2.3、COMMIT

提交事务

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE account SET balance = balance - 10 WHERE id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE account SET balance = balance + 10 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

1.2.4、ROLLBACK

回滚事务

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE account SET balance = balance - 10 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE account SET balance = balance + 1 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

1.2.4、自动提交

mysql> SHOW VARIABLES LIKE 'autocommit';

默认情况下，我们不显示的使用START TRANSACTION或者BEGIN开始一个事务，那么每一条语句都算是一条独立的事务，这种特性称之为事务的自动提交。

如果我们想关闭这种自动提交的功能，

  • 显式的的使用START TRANSACTION或者BEGIN语句开启一个事务。这样在本次事务提交或者回
    滚前会暂时关闭掉自动提交的功能。
  • 把系统变量autocommit的值设置为OFF，就像这样： SET autocommit = OFF; 这样的话，我
    们写入的多条语句就算是属于同一个事务了，直到我们显式的写出COMMIT语句来把这个事务提交
    掉，或者显式的写出ROLLBACK语句来把这个事务回滚掉。

1.2.5、隐式提交

当我们使用START TRANSACTION或者BEGIN语句开启了一个事务，或者把系统变量autocommit的值设置为OFF时，事务就不会进行自动提交，但是如果我们输入了某些语句之后就会悄悄的提交掉，就像我们输入了COMMIT语句了一样，这种因为某些特殊的语句而导致事务提交的情况称为隐式提交，这些会导致事务隐式提交
的语句包括：

  • 定义或修改数据库对象的数据定义语言（DDL）。所谓的数据
    库对象，指的就是数据库、表、视图、存储过程等等这些东西。当我们使用CREATE、ALTER、DROP等语句去修改这些所谓的数据库对象时，就会隐式的提交前边语句所属于的事务。
  • 隐式使用或修改mysql数据库中的表：当我们使用ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、SET PASSWORD等语句时也会隐式的提交前边语句所属于的事务。
  • 事务控制或关于锁定的语句：当我们在一个事务还没提交或者回滚时就又使用START TRANSACTION或者BEGIN语句开启了另一个事务时，会隐式的提交上一个事务。或者当前的autocommit系统变量的值为OFF，我们手动把它调为ON时，也会隐式的提交前边语句所属的事务。或者使用LOCK TABLES、UNLOCK TABLES等关于锁定的语句也会隐式的提交前边语句所属的事务。
  • 加载数据的语句：比如我们使用LOAD DATA语句来批量往数据库中导入数据时，也会隐式的提交前边语句所属的事务。
  • 其它的一些语句：使用ANALYZE TABLE、CACHE INDEX、CHECK TABLE、FLUSH、 LOAD、INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE、RESET等语句也会隐式的提交前边语句所属的事务。

1.2.6、保存点

如果你开启了一个事务，并且已经敲了很多语句，忽然发现上一条语句有点问题，你只好使用ROLLBACK语句来让数据库状态恢复到事务执行之前的样子，然后一切从头再来，总有一种一夜回到*的感觉。所以MYSQL提出了一个保存点（英文：savepoint）的概念，就是在事务对应的数据库语句中打几个点，我们在调用
ROLLBACK语句时可以指定会滚到哪个点，而不是回到最初的原点。定义保存点的语法如下：

SAVEPOINT  保存点名称;

当我们想回滚到某个保存点时，可以使用下边这个语句（下边语句中的单词WORK和SAVEPOINT是可有可无的）：

ROLLBACK [WORK] TO [SAVEPOINT] 保存点名称;

不过如果ROLLBACK语句后边不跟随保存点名称的话，会直接回滚到事务执行之前的状态。
如果我们想删除某个保存点，可以使用这个语句:

RELEASE SAVEPOINT 保存点名称;

1.3、隔离性详解

-- 修改隔离性级别
mysql> set session transaction isolation level read uncommitted;
-- 查看隔离级别
mysql> select @@tx_isolation;

1.3.1、未提交读（READ UNCOMMITIED）

一个事务可以读到另一事务没有提交的数据，会出现脏读。

1.3.2、已提交读（READ COMMITIED）

一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，会出现不可重复读、幻读。

1.3.3、可重复读（RETEATABLE）

一个事务第一次读过某条记录后，即使其他事务修改了该记录的值并且提交，该事务之后再读该条记录时，读到
的仍是第一次读到的值，而不是每次都读到不同的数据，这就是可重复读，这种隔离级别解决了不可重复，但是
还是会出现幻读。

1.3.4、串行化（SERIALIALIZABLE）

以上3种隔离级别都允许对同一条记录同时进行读-读、读-写、写-读的并发操作，如果我们不允许读-写、写-读
的并发操作，可以使用SERIALIZABLE隔离级别，这种隔离基金因为对同一条记录的操作都是串行的，所以不会
出现脏读、幻读等现象。

1.3.5、总结

  • READ UNCOMMITTED隔离级别下，可能发生脏读脏读、不可重复读和和幻读问题。
  • READ COMMITTED隔离级别下，可能发生不可重复读 不可重复读和幻读问题，但是不会发生 问题，但是不会发生脏读问
    题。
  • REPEATABLE READ隔离级别下，可能发生幻读幻读问题，不会发生脏读脏读和不可重复读的问题。
  • SERIALIZABLE隔离级别下，各种问题都不可以发生。
    注意：这四种隔离级别是SQL的标准定义，不同的数据库会有不同的实现，特别需要注意的是
  • MySQL在在REPEATABLE READ隔离级别下，是可以禁止幻读问题的发生的 隔离级别下，是可以禁止幻读问题的发生的。

1.4 锁

1.4.1、读锁和写锁

  • 读锁：共享锁、Shared Locks、S锁。
  • 写锁：排他锁、Exclusive Locks、X锁。
    只有读锁与读锁不会冲突

1.4.2、读操作

对于普通 SELECT 语句，InnoDB 不会加任何锁
select … lock in share mode
将查找到的数据加上一个S锁，允许其他事务继续获取这些记录的S锁，不能获取这些记录的X锁（会阻塞）
select … for update
将查找到的数据加上一个X锁，不允许其他事务获取这些记录的S锁和X锁。

1.4.3、写操作

  • DELETE：删除一条数据时，先对记录加X锁，再执行删除操作。
  • INSERT：插入一条记录时，会先加隐式锁 隐式锁来保护这条新插入的记录在本事务提交前不被别的事务
    访问到。
  • UPDATE：如果被更新的列，修改前后没有导致存储空间变化，那么会先给记录加X锁，再直接对记录进行修改。如果被更新的列，修改前后导致存储空间发生了变化，那么会先给记录加X锁，然后将记录删掉，再Insert一条新记录。

1.4.5、行锁和表锁

1.4.5.1、行锁
  • LOCK_REC_NOT_GAP：单个行记录上的锁。
  • LOCK_GAP：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务
    的两次当前读，出现幻读的情况。
  • LOCK_ORDINARY：锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。
1.4.5.2、间隙锁（LOCK_GAP GAP锁）
  • 查询使用的是主键时，只要在主键值对应的那一条数据加锁就可以。
  • 查询使用的是唯一索引时，只需要对查询值所对应的唯一索引记录项和对应的聚集索引上的项加锁即可。
  • 查询使用的是普通索引时，会对满足条件的索引记录都加上锁，同时对这些索引记录对应的聚集索引上的项也加锁。
  • 查询的时候没有走索引，也只会对满足条件的记录加锁。
1.4.5.3、LS锁、IX锁

IS锁：意向共享锁、Intention Shared Lock。当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁。
IX锁，意向排他锁、Intention Exclusive Lock。当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁。
IS、IX锁是表级锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录。

1.4.5.4、AUTO_INC锁

在执行插入语句时就在表级别加一个AUTO-INC锁，然后为每条待插入记录的AUTO_INCREMENT
修饰的列分配递增的值，在该语句执行结束后，再把AUTO-INC锁释放掉。这样一个事务在持有
AUTO-INC锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的。
采用一个轻量级的锁，在为插入语句生成AUTO_INCREMENT修饰的列的值时获取一下这个轻量级锁，然后生成本次插入语句需要用到的AUTO_INCREMENT列的值之后，就把该轻量级锁释放掉，并不需要等到整个插入语句执行完才释放锁。

系统变量
innodb_autoinc_lock_mode：
innodb_autoinc_lock_mode值为0：采用AUTO-INC锁。
innodb_autoinc_lock_mode值为2：采用轻量级锁。
当innodb_autoinc_lock_mode值为1：当插入记录数不确定是采用AUTO-INC锁，当插入记录数确定时采用轻量级锁。

1.4.6、悲观锁

悲观锁用的就是数据库的行锁，认为数据库会发生并发冲突，直接上来就把数据锁住，其他事务不能修改，直至
提交了当前事务。

1.4.7、乐观锁

乐观锁其实是一种思想，认为不会锁定的情况下去更新数据，如果发现不对劲，才不更新(回滚)。在数据库中往往添加一个version字段来实现。

1.4.8、死锁

session1:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 where a =1 for update; -- 1
+---+------+------+------+------+
| a | b | c | d | e | +---+------+------+------+------+
| 1 | 1 | 1 | 1 | 1 | +---+------+------+------+------+
1 row in set (0.00 sec)
mysql> update t1 set c = 2 where a = 4; -- 3 一开始会阻塞
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

session2:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t1 where a = 4; -- 2
Query OK, 1 row affected (0.00 sec)
mysql> delete fromt t1 where a = 1; -- 4 按道理会阻塞，并产生死锁，但是mysql有死锁检查机制，让死锁终端。
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 't1 where a
= 1' at line 1
mysql> delete from t1 where a = 1;
Query OK, 1 row affected (0.01 sec)
1.4.8.1、避免死锁
  • 以固定的顺序访问表和行
  • 大事务拆小，大事务更容易产生死锁
  • 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率
  • 降低隔离级别（下下签）
  • 为表添加合理的索引
相关标签： mysql 数据库锁

上一篇： php简单备份与还原MySql的方法 mysql官网客户端 mysql 命令 mysql安装

下一篇： 迁移数据时Oracle字符集的转换

推荐阅读