自学内容网 自学内容网
自学内容网 > 正文

mysql事务使用和事务隔离级别与sqlserver的比较

🕗 发布于 2024-10-14 21:53 mysql  数据库  

在 MySQL 中，事务 (Transaction) 是一个将一组 SQL 语句作为一个整体执行的机制。事务确保要么所有操作都执行成功，要么在遇到错误时回滚到之前的状态，从而保证数据库数据的一致性和完整性。

事务的四大特性（ACID）

事务具有以下四个关键特性，简称为 ACID：

  1. 原子性 (Atomicity)：事务中的所有操作要么全部成功，要么全部失败。如果事务中的某一部分失败，整个事务会回滚，数据将恢复到事务开始之前的状态。

  2. 一致性 (Consistency)：事务的执行结果必须使数据库从一个一致状态变为另一个一致状态。事务在完成后，所有的数据都必须符合数据库的完整性约束，列如（不管是用户正常执行提交了，还是执行过程中发生异常回滚了，最终操作的数据都要实现平衡，不能多也不能少，就像化学中的遵循质量守恒定律）。

  3. 隔离性 (Isolation)：一个事务所做的操作对其他事务是不可见的，直到该事务提交。这确保了多个事务并发执行时不相互影响，就行docker一样在自己容器中跑着自己的项目，不会影响到其他容器。

  4. 持久性 (Durability)：一旦事务提交，其对数据库的更改是永久的，即使系统崩溃也不会丢失。

基本使用

下面是个基本案例,简单概括事务的使用，不深入。

先创建两个表，一个users用户表，一个accounts账户表.

用户表有用户id，用户名username，账户idaccountId，账户id对应账户表的id。


CREATE TABLE `users`  (
  `id` int NOT NULL,
  `username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `accountsId` int NOT NULL
) ;



INSERT INTO `users` VALUES (1, 'zhangsan', 2);

账户表，有account_id账户id,balance余额字段

CREATE TABLE `accounts`  (
  `account_id` int NOT NULL,
  `balance` decimal(10, 2) NULL DEFAULT NULL,
  PRIMARY KEY (`account_id`) USING BTREE
) ;

-- ----------------------------
-- Records of accounts
-- ----------------------------
INSERT INTO `accounts` VALUES (1, 800.00);
INSERT INTO `accounts` VALUES (2, 500.00);
INSERT INTO `accounts` VALUES (3, 4500.00);

MySQL 中的事务控制语句
1.开启事务：
start TRANSACTION;

或者 

BEGIN;

  中间业务部分，在开启事务BEGIN;和提交事务之间COMMIT; 写你要执行的sql业务。

在 MySQL 中，事务可以操作的 SQL 语句主要涉及修改数据的 DML（数据操作语言） 语句，这些语句包括 INSERT、UPDATE 和 DELETE，它们会对数据库中的数据进行修改，所以要涉及的业务只能是插入、更新、删除这三种对mysql的操作才能使用事务，事务无法执行，包括 CREATE、DROP、ALTER 等语句，用于定义数据库的结构。

2. 提交事务：

直到事务提交时执行的sql更改才会永久生效。如果sql报错事务回滚，所有更新将被撤销。

COMMIT;
 3.回滚事务：

终止当前事务并撤销事务中所有未提交的更改，使数据库恢复到事务开始之前的状态。

ROLLBACK; 

上面的开启事务和提交事务、回滚事务 ，基本上就是事务的核心，使用这三个操作就可以让事务跑起来。

基本实例：

一个简单的业务当usersid删除，账户id也删除

-- 一个简单的业务当usersid删除，账户id也删除
-- --开启事务 使用start TRANSACTION 和BEGIN;都可以

-- start TRANSACTION;
BEGIN;
delete FROM users u WHERE  accountsId=2;
DELETE FROM accounts WHERE account_id = 2;

-- 如果一切正常，提交事务
COMMIT;

-- 如果有问题，回滚所有更改
ROLLBACK;





-- 恢复数据,上面事务sql全部一起执行，保持事务一致性和原子性
INSERT INTO accounts (account_id, balance) VALUES (2, 500);
INSERT INTO users(id,username,accountsId) VALUES(1,'zhangsan',2);

运行事务代码，从开启事务begin;到ROLLBACK;要全部一起执行；那么执行成功可以去看，两个表的对应account_id=2的数据都已经删除了，其中一个失败都回滚；

事务进阶：
4.使用保存点

如果在一个事务中需要部分回滚，可以使用保存点 (SAVEPOINT)。

简单示例：写一个简单的业务，第一个表插入数据后，往另外一个表插入数据

 如果在一个事务中需要部分回滚，可以使用保存点 (SAVEPOINT)。

-- 恢复数据
INSERT INTO accounts (account_id, balance) VALUES (2, 500);
INSERT INTO users(id,username,accountsId) VALUES(1,'zhangsan',2);


-- 设置存储保存点,的事务用法
-- --写一个简单的业务，第一个表插入数据后，往另外一个表插入数据
BEGIN;
    -- 插入 users 表中的记录一条记录
    INSERT INTO users(id,username,accountsId) VALUES(2,'wangwu',4);
    
    -- 在第一个 插入users表 成功后设置保存点
    SAVEPOINT my_first_savepoint;

    -- 再给 accounts 表插入一条对应users表中对应的数据的记录
 INSERT INTO accounts (account_id, balance) VALUES (4, '100.00');
    --  如果报错 就回滚到保存点
ROLLBACK TO SAVEPOINT my_first_savepoint;
    -- 如果一切正常，提交事务
    COMMIT;
5.事务隔离级别

为什么要使用事务隔离级别？为什么要用锁？

并发事务问题：

脏读：一个事务读到另外一个事务还没有提交的数据。

不可重复读：一个事务先后读取到同一条记录，但是两次读取的数据不同，称为不可重复读。

幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时候，又发现这行数据已经存在了，好像出现了“幻影”。

所以需要设置隔离级别，控制事务之间的相互影响。

对应的隔离级别，对应处理相对应的并发事务问题！

下面这个图打钩的是可能出现的问题。

MySQL 支持不同的事务隔离级别，以控制事务之间的相互影响。常见的隔离级别有四种：

  1. READ UNCOMMITTED（Read uncommitted）（未提交读）：

    • 事务可以读取到其他未提交事务的更改，存在脏读问题。

  2. READ COMMITTED（Read committed）（提交读）：

    • 事务只能读取到已经提交的更改，避免了脏读，但可能存在不可重复读。

  3. REPEATABLE READ（Repeatable Read（mysql默认隔离级别））（可重复读）：

    • 在同一事务内，事务每次读取的结果都是相同的，避免了脏读和不可重复读，但可能存在幻读问题。

  4. SERIALIZABLE（可串行化）：

    • 最严格的隔离级别，所有事务串行执行，避免了脏读、不可重复读和幻读，但性能较差。

 事务设置隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL [级别];

-- 设置事务隔离级别为 REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 开始事务
BEGIN;

-- 插入 users 表中的记录
INSERT INTO users(id, username, accountsId) VALUES(2, 'wangwu', 4);

-- 插入成功后，设置保存点
SAVEPOINT my_first_savepoint;

-- 尝试向 accounts 表插入一条记录
INSERT INTO accounts (account_id, balance) VALUES (4, '100.00');

-- 如果插入 accounts 表时发生错误，回滚到保存点
ROLLBACK TO SAVEPOINT my_first_savepoint;

-- 如果一切正常，提交事务
COMMIT;

扩展总结

MySQL 和 SQL Server 事务隔离级别的相同点和不同点

相同点：

都支持事务的隔离级别，并通过锁机制来实现隔离性。 SQL Server 和 MySQL 都遵循 SQL 标准，支持四种标准的隔离级别，四种隔离级别基本上一模一样。

锁的相同点

MySQL 和 SQL Server 在使用锁来管理并发访问时，都提供了以下锁类型：

  • 共享锁（Shared Lock, S 锁）：用于读取操作，允许多个事务共享读取同一数据，但不能修改数据。
  • 独占锁（Exclusive Lock, X 锁）：用于写操作，确保其他事务不能读取或修改加锁的数据。
  • 意向锁（Intent Lock, IS/IX 锁）：用于标记一个事务打算在更低粒度的数据对象（如行或页）上加锁，帮助防止死锁。
不同点：
隔离级别的不同点

  • 默认隔离级别：

    • MySQL：默认的隔离级别是 REPEATABLE READ，适合大多数并发场景。MySQL 使用的是多版本并发控制（MVCC）来处理事务之间的读取，避免不可重复读，同时结合行锁来保证数据一致性。

    • SQL Server：默认的隔离级别是 READ COMMITTED，这意味着在大多数情况下，SQL Server 会通过短时间持有共享锁，防止读取到未提交的数据（脏读）。并且，SQL Server 提供了 SNAPSHOT 隔离级别，允许事务使用行版本控制（类似 MySQL 的 MVCC）。

隔离级别的实现方式：
  • MySQL（InnoDB 存储引擎）：
    • MySQL 使用 MVCC（多版本并发控制）来处理大多数读操作，尤其是在 REPEATABLE READ 隔离级别下，事务可以看到数据的多个版本，从而避免锁的争用。
    • REPEATABLE READ 下，MySQL 防止幻读的机制是通过 Next-Key Lock（临近键锁），它会锁定可能被修改或插入的新行。
  • SQL Server：
    • SQL Server 默认依赖锁机制来处理数据的读取和写入。READ COMMITTED 和更高的隔离级别下使用共享锁来防止脏读。
    • SQL Server 也提供 SNAPSHOT ISOLATION，它类似于 MySQL 的 MVCC，允许事务在执行时看到数据的一个“快照”，从而避免了使用锁进行读取操作。这个模式是通过行版本控制（Row Versioning）实现的。

小结

1. 隔离级别相似性：
  • MySQL 和 SQL Server 都支持四种标准的隔离级别：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
  • 两者都提供了机制来防止脏读、不可重复读和幻读，并通过锁和版本控制技术来实现隔离级别。
2. 隔离级别不同点：
  • 默认隔离级别：MySQL 默认是 REPEATABLE READ，而 SQL Server 默认是 `


原文地址：https://blog.csdn.net/dawfwafaew/article/details/142780778

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024