您的位置: 首页  >  IT文章  >  MySQL中的事务

MySQL中的事务

分类: IT文章 • 2024-02-08 20:03:25

一、概念

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

  • 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
  • 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
  • 事务用来管理 insert,update,delete 语句

在MySQL中，事务由单独单元的一个或多个SQL语句组成。在这个单元中，每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事务开始以前的状态；如果单元中的所有SQL语句均执行成功，则事务被顺利执行。

二、事务的四个条件

一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

  • 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

  • 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

  • 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

  • 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

三、MySQL事务的创建与存在周期

（一）创建事务

创建事务的一般过程是：

  • 初始化事务
  • 创建事务
  • 应用SELECT语句查询数据是否被录入
  • 提交事务

如果用户不在操作数据库完成后执行事务提交，则系统会默认执行回滚操作。如果用户在提交事务前选择撤销事务，则用户在撤销前的所有事务将被取消，数据库系统会回到初始状态。

注意：默认情况下，在MySQL中创建的数据表类型都是MyISAM，但是该类型的数据表并不能支持事务。所以，如果用户想让数据表支持事务处理能力，必须将当前操作数据表的类型设置为InnoDB或BDB。

 

（1）在创建事务的过程中，用户需要创建一个InnoDB或BDB类型的数据表，其基本命令结构如下：

  • CREATE TABLE table_name（field-defintions）TYPE=INNODB/BDB；

           其中，table_name为表名，而field_defintions为表内定义的字段等属性，TYPE为数据表的类型，既可以是InnoDB类型，同样也可以是BDB类型。

  • 当用户希望已经存在的表支持事务处理，则可以应用ALTER TABLE命令指定数据表的类型实现对表的类型更改操作，使原本不支持事务的数据表更改为支持事务处理的类型。其命令如下：

          ALTER TABLE table_name TYPE=INNODB/BDB；

当用户更改完表的类型后，即可使数据表支持事务处理。

注意：应用ALTER TABLE操作可能会导致数据库中数据丢失，因此为了避免非预期结果出现，在使用ALTER TABLE命令之前，用户需要创建一个表备份。

1、初始化事务

  • 初始化MySQL事务，首先声明初始化MySQL事务后所有的SQL语句为一个单元。在MySQL中，应用START TRANSACTION命令来标记一个事务的开始。初始化事务的结构如下：

START TRANSACTION；

  • 另外，用户也可以使用BEGIN或者BEGIN WORK命令初始化事务，通常START TRANSACTION命令后面跟随的是组成事务的SQL语句。

在命令提示符中输入如下命令：

start transaction；

如果在用户输入以上代码后，MySQL数据库没有给出警告提示或返回错误信息，则说明事务初始化成功，用户可以继续执行下一步操作。

2、创建事务

  • insert into connection（email, cellphone, QQ, sid）values（'barrystephen@126.com'，13456000000，187034000，3）；

应用SELECT语句查看数据是否被正确输入

  • SELECT * FROM connection WHERE sid=3；

ps:在用户插入新表为“InnoDB”类型或更改原来表类型为“InnoDB”时，如果在输入命令提示后，MySQL提示“The 'InnoDB' feature is disabled；you needInnoDB' to have it working”警告，则说明InnoDB表类型并没有被开启，用户需要找到MySQL文件目录下的“my.ini”文件，定位“skip_innodb”选项位置，将原来的“skip_innodb”改为“#skip_innodb”后保存该文件，重新启动MySQL服务器，即可令数据库支持“InnoDB”类型表。 

3、提交事务

在用户没有提交事务之前，当其他用户连接MySQL服务器时，应用SELECT语句查询结果，则不会显示没有提交的事务。当且仅当用户成功提交事务后，其他用户才可能通过SELECT语句查询事务结果，由事务的特性可知，事务具有孤立性，当事务处在处理过程中，其实MySQL并未将结果写入磁盘中，这样一来，这些正在处理的事务相对其他用户是不可见的。一旦数据被正确插入，用户可以使用COMMIT命令提交事务。提交事务的命令结构如下：

COMMIT

一旦当前执行事务的用户提交当前事务，则其他用户就可以通过会话查询结果。

4、撤销事务（事务回滚）

撤销事务，又被称作事务回滚。

即事务被用户开启、用户输入的SQL语句被执行后，如果用户想要撤销刚才的数据库操作，可使用ROLLBACK命令撤销数据库中的所有变化。ROLLBACK命令结构如下：

ROLLBACK

输入回滚操作后，如何判断是否执行回滚操作了呢？可以通过SELECT语句查看11.2.2小节中插入的数据是否存在.

如果执行一个回滚操作，则在输入START TRANSACTIONA命令后的所有SQL语句都将执行回滚操作。故在执行事务回滚前，用户需要慎重选择执行回滚操作。如果用户开启事务后，没有提交事务，则事务默认为自动回滚状态，即不保存用户之前的任何操作。

（2）事务的存在周期

事务的周期由用户在命令提示符中输入START TRANSACTION指令开始，直至用户输入COMMIT结束

事务不支持嵌套功能，当用户在未结束第一个事务又重新打开一个事务，则前一个事务会自动提交，同样MySQL命令中很多命令都会隐藏执行COMMIT命令

四、MySQL行为

在MySQL中，存在两个可以控制行为的变量，它们分别是AUTOCOMMIT变量和TRANSACTION ISOLACTION LEVEL变量。

  • 自动提交

在MySQL中，如果不更改其自动提交变量，则系统会自动向数据库提交结果，用户在执行数据库操作过程中，不需要使用START TRANSACTION语句开始事务，应用COMMIT或者ROLLBACK提交事务或执行回滚操作。如果用户希望通过控制MySQL自动提交参数，可以更改提交模式，这一更改过程是通过设置AUTOCOMMIT变量来实现。

下面通过一个示例向读者展示如何关闭自动提交参数。在命令提示符中输入以下命令：

SET AUTOCOMMIT=0；

只有当用户输入COMMIT命令后，MySQL才将数据表中的资料提交到数据库中，如果不提交事务，而终止MySQL会话，数据库将会自动执行回滚操作。

可以通过查看“@@AUTOCOMMIT”变量来查看当前自动提交状态，查看此变量SELECT @@AUTOCOMMIT。

五、事务的隔离级别

基于ANSI/ISO SQL规范，MySQL提供4种孤立级：

SERIALIZABLE（序列化）

REPEATABLE READ（可重读）

READ COMMITTED（提交后读）

READ UNCOMMITTED（未提交读）

在MySQL中，可以使用TRANSACTION ISOLATION LEVEL变量来修改事务孤立级，其中，MySQL的默认隔离级别为REPEATABLE READ（可重读），用户可以使用SELECT命令获取当前事务孤立级变量的值，其命令如下：

SELECT @@tx_isolation；

如果用户想要修改事务的隔离级别，必须首先获取SUPER优先权，以便用户可以顺利执行修改操作，set。

六、事务的使用技巧和注意事项

  • 应用小事务，保证每个事务不会在执行前等待很长时间，从而避免各个事务因为互相等待而导致系统性能的大幅度下降。
  • 选择合适的孤立级，因为事务的性能与其对服务器产生的负载成反比，即当事务孤立级越高，其性能越低，但是其安全性也越高。只有选择适当的孤立级，才能有效地提高MySQL系统性能和应用性。
  • 死锁的概念与避免，

        （1）即当两个或者多个处于不同序列的用户打算同时更新某相同的数据库时，因互相等待对方释放权限而导致双方一直处于等待状态。

        （2）在实际应用中，两个不同序列的客户打算同时对数据执行操作，极有可能产生死锁。

        （3）更具体地讲，当两个事务相互等待操作对方释放所持有的资源，而导致两个事务都无法操作对方持有的资源，这样无限期的等待被称作死锁。

        （4）MySQL的InnoDB表处理程序具有检查死锁这一功能，如果该处理程序发现用户在操作过程中产生死锁，该处理程序立刻通过撤销操作来撤销其中一个事务，以便使死锁消失。这样就可以使另一个事务获取对方所占有的资源而执行逻辑操作

七、MySQL伪事务

在MySQL中，InnoDB和BDB类型表可以支持事务处理，但是MySQL中MyISAM类型表并不能支持事务处理，对于某些应用该类型的数据表，用户可以选择应用表锁定来替代事务。这种引用表锁定来替代事务的事件被称作伪事务。

使用表锁定来锁定表的操作，可以加强非事务表在执行过程的安全性和稳定性。

相关推荐