MySQL事务与锁机制详解-优快云博客

本文链接：https://blog.youkuaiyun.com/m0_59348479/article/details/119777104

本文介绍了MySQL的事务控制语句，包括COMMIT、ROLLBACK和BEGIN，以及锁的概念、重要性和不同类型的锁，如自增锁、共享锁和排它锁。通过案例展示了事务在数据库并发控制中的作用，强调了锁在保证数据一致性和并发访问安全性中的关键角色，同时也指出锁带来的额外开销。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL 事务语句

MySQL 为我们提供了以下重要语句来控制事务：

为了启动一个事务，你使用 START TRANSACTION 语句。BEGIN 或 BEGIN WORK 是 START TRANSACTION 的别名。
要提交当前事务并使其变化永久化，你要使用 COMMIT 语句。
要回滚当前事务并取消其变化，你可以使用 ROLLBACK 语句。
要禁用或启用当前事务的自动提交模式，你可以使用 SET autocommit 语句。
默认情况下，MySQL 自动将更改永久性地提交给数据库。要强迫 MySQL 不自动提交更改，你可以使用以下语句：

SET autocommit = 0;

-- OR --

SET autocommit = OFF

你使用下面的语句来明确地启用自动提交模式：

SET autocommit = 1;

-- OR --

SET autocommit = ON;

COMMIT 实例

为了使用事务，你首先要把 SQL 语句分成逻辑部分，并确定数据何时应提交或回滚。
下面说明了创建一个新的销售订单的步骤：

首先，通过使用 START TRANSACTION 语句启动一个事务。
接下来，从 orders 表中选择最新的销售订单号，并使用下一个销售订单号作为新的销售订单号。
然后，在 orders 表中插入一个新的销售订单。
之后，在 orderdetails 表中插入销售订单项目。
最后，使用 COMMIT 语句提交该事务。

你可以选择从 orders 和 orderdetails 表中选择数据来检查新的销售订单。

-- 1. start a new transaction
START TRANSACTION;

-- 2. Get the latest order number
SELECT 
    @orderNumber:=MAX(orderNUmber)+1
FROM
    orders;

-- 3. insert a new order for customer 145
INSERT INTO orders(orderNumber,
                   orderDate,
                   requiredDate,
                   shippedDate,
                   status,
                   customerNumber)
VALUES(@orderNumber,
       '2005-05-31',
       '2005-06-10',
       '2005-06-11',
       'In Process',
        145);
        
-- 4. Insert order line items
INSERT INTO orderdetails(orderNumber,
                         productCode,
                         quantityOrdered,
                         priceEach,
                         orderLineNumber)
VALUES(@orderNumber,'S18_1749', 30, '136', 1),
      (@orderNumber,'S18_2248', 50, '55.09', 2); 
      
-- 5. commit changes    
COMMIT;

ROLLBACK 实例

首先，登录到 MySQL 数据库服务器，从 orders 表中删除数据：


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

mysql> DELETE FROM orders;
Query OK, 327 rows affected (0.03 sec)

正如你从输出中看到的，MySQL 确认 orders 表中的所有行被删除了。

第二，在一个单独的会话中登录到 MySQL 数据库服务器，查询 orders 表中的数据：

在这第二个会话中，我们仍然可以看到 orders 表中的数据。

我们已经在第一个会话中进行了修改。然而，这些变化并不是永久性的。在第一个会话中，我们可以提交或回滚这些变化。

出于演示的目的，我们将回滚第一个会话中的更改。

mysql> ROLLBACK;

在第一次会话中，我们还将验证 orders 表的内容：

我们要向 teachers 表中插入一条 Xie Xun 的信息，其年龄为 49 岁，国籍为 CN，请补充 SQL 语句，来实现插入 Xie Xun 的信息。

-- 不要删除预置代码 --

-- 开启一个事务 --

BEGIN;

-- 插入 Kansas 的信息 --

-- Write your SQL Query here --

insert into teachers (name, age, country) values ('Xie Xun', 49, 'CN');

COMMIT;

向 teachers 表中插入一条 Kansas 的信息，其年龄为 41 岁，国籍为 UK，请补充 SQL 语句，来实现插入 Kansas 的信息。

BEGIN;

insert into teachers (name, age, country) values ('Kansas', 41, 'UK');

COMMIT;

锁的认识

锁的解释

锁：计算机协调多个进程或线程并发访问某一资源的机制。

锁的重要性

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

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

锁的缺点

加锁是消耗资源的，锁的各种操作，包括：获得锁、检测锁是否已经解除、释放锁等，都会增加系统的开销。

请编写 SQL 语句，查看你当前使用的数据库是什么事务隔离级别

SHOW VARIABLES like '%isolation%';

查看你当前使用的数据库是什么事务隔离级别

show variables like '%isolation%';

InnoDB 锁的种类之自增锁

自增锁（Auto-inc Locks; table-level lock）

查询当前数据库的自增锁模式：

show variables like '%innodb_autoinc_lock_mode%';

0：traditonal 每次 insert 语句执行都会产生表锁
1：consecutive simple insert 会获得批量的锁，保证一批插入自增序列的连续性，插入之前提前释放锁，在这个模式下你会发现当你 insert 多条数据回滚的时候虽然 DB 没有插入数据，但是自增 ID 已经增长了，也是数据库默认的级别
2：interleaved 不会锁表，实时插入，并发最高，但是基于主从的复制是不安全的，感兴趣可以去查询 RBR 和 SBR 的优缺点

通过查询知道，我们的数据库是开启 consecutive 级别，simple insert 表示通过分析 insert 语句可以确定插入的数量；如：insert, insert, insert … valies(), values(), values()；相应的其他插入方式还有 Bulk inserts，Mixed-mode inserts 等。
官方原话如下

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

自增锁是一种特殊的表级别锁（table-level lock），专门针对事务插入 AUTO_INCREMENT 类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。