MYSQL——访问控制、事务处理与并发控制(3)

小朱不猪
177 阅读6分钟

一起养成写作习惯！这是我参与「掘金日新计划 · 4 月更文挑战」的第23天，点击查看活动详情。

MYSQL——访问控制、事务处理与并发控制(3)

需要：

       了解组角色、角色、用户的概念及他们之间的区别；

       了解数据库的不同权限；

       掌握为用户分配权限及收回权限的方法；

       掌握数据库事务管理的基本原理，包括显式事务、事务提交、事务回滚、隐式事务等，以及事务的编程方法。

       掌握数据库并发控制的基本原理及其应用方法。

显式事务、事务提交、事务回滚、隐式事务等的编程方法:

1、准备测试数据

在 banks 数据库中创建数据表 account，用于完成转账功能：

CREATE DATABASE banks CHARSET=utf8;
USE banks;
CREATE TABLE  account (
   id  INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
   username  VARCHAR(32) NOT NULL UNIQUE COMMENT '账户名',
   money  DECIMAL(10,2) NOT NULL COMMENT '存款金额'
)ENGINE=INNODB CHARSET=utf8;

       需要注意的是，在 MySQL 数据库中，要想对数据表进行事务处理，需要将其存储引擎设置为 InnoDB。

2、开启事务

在MySQL数据库中，使用事务之前必须先开启事务，具体SQL语句如下。

START TRANSACTION;
# 或
BEGIN;

       执行上述语句后，若MySQL中没有返回警告或错误信息提示，则初始化事务成功，可以继续执行以下的操作了

3、创建事务

       创建事务就是在事务开启后，执行的一系列 SQL 语句。例如，开启事务处理后，向表 account 中插入两条测试数据，具体如下。

START TRANSACTION;
INSERT INTO  account  VALUES (NULL, '张三', 5000), (NULL, '李四', 100);

       上述SQL语句执行后，通过SELECT查询执行结果。

SELECT * FROM account;

4、提交事务

       在用户没有提交事务前，其他连接MySQL服务器的用户进程是看不到当前事务的处理结果的。

       新创建一个mysql连接“连接2”，进入到banks数据库中查看数据表account中的数据。

USE banks;
SELECT * FROM account;

       在原来的连接客户端，对上述创建的事务进行提交操作，具体SQL语句如下。

COMMIT;

       执行上述语句后，在“连接2”客户端再次执行SELECT查询。

5、撤销事务

       撤销事务也称事务回滚，用于创建事务时执行的SQL语句与业务逻辑不符或操作错误时，通过ROLLBACK命令撤销对数据库的所有操作，或利用ROLLBACK TO SAVEPOINT回滚到指定位置。撤销事务的操作在实际应用中，有着非常重要的作用。

① 撤销全部事务处理

       假设张三转让李四一辆二手自行车，李四需要向张三支付199元，则执行的SQL语句如下。

       在第一个连接窗口执行如下语句：

START TRANSACTION;
UPDATE  account  SET  money = money +199 WHERE username='张三';
UPDATE  account  SET  money = money -199 WHERE username='李四';

       上述SQL语句执行后，通过SELECT查询执行结果。

SELECT * FROM account;

       从查询结果可知，李四的账户余额为负数，显然不符合正常的业务逻辑。此时就需要使用ROLLBACK命令进行全部事务的回滚，撤销刚才对数据表account的更新操作。具体SQL语句如下。

ROLLBACK;

       执行完事务回滚后再进行SELECT查询，结果与开始创建事务查询结果相同。

SELECT * FROM account;

② 撤销事务到指定位置

       使用SAVEPOINT可以在事务处理使用指定不同的撤销位置，在需要事务撤销时进行回滚。例如，向数据表account中逐条插入3条记录，并利用SAVEPOINT在每次插入数据后设置一个回滚位置，SQL语句如下。

START TRANSACTION;
INSERT INTO account VALUES (NULL, '王五', 500);
SAVEPOINT test1;
INSERT INTO account VALUES (NULL, '赵六', 6000);
SAVEPOINT test2;
INSERT INTO account VALUES (NULL, '李七', 70);
SAVEPOINT test3;
SELECT * FROM account;

       上述SQL执行后，进行SELECT查询，就会看到account表中已经存在新插入的这3条记录。

       假设从“李七”开始的记录有误需要撤销，就可以回滚到test2位置，具体SQL如下。

ROLLBACK TO SAVEPOINT test2;

       执行上述SQL语句后，查询数据表account中的数据，就会看到新插入的记录中只有“王五”和“赵六”。由此可见，事务可以回滚到想要的位置。

       需要注意的是，SAVEPOINT指定回滚位置的操作对于已经提交的事务并不适用，且对于定义了相同名称的回滚位置，则后面的定义会覆盖之前的定义。此外，对于不再需要使用的回滚位置标识，可以通过RELEASE SAVEPOINT命令进行删除。例如，删除回滚位置test1，SQL语句如下。

RELEASE SAVEPOINT test1;

       CHAIN和RELEASE子句可以用来分别定义在事务提交或者回滚之后的操作，CHAIN会立即启动一个新事物，并且和刚才的事务具有相同的隔离级别；RELEASE则会断开和客户端的连接。

6、事务的自动提交

       MySQL中默认操作就是自动提交模式。除非显示地开启一个事务（START TRANSACTION），否则所有的SQL都会被当做单独的事务自动提交（COMMIT）。因此，如果用户想要控制事务的自动提交方式，可以通过AUTOCOMMIT来实现，具体如下。

SET AUTOCOMMIT=0;

       通过上述语句即可关闭MySQL中的自动提交功能，实现了只有用户手动执行提交（COMMIT）操作，MySQL才会将事务提交到数据库系统中。否则，若不执行手动提交，而终止MySQL会话，数据库会自动执行回滚操作。

       在第一个连接窗口执行如此下语句：

SET AUTOCOMMIT=0;
INSERT INTO `account` VALUES (NULL, '王丽', 45);
SELECT * FROM account;

       到第2各窗口查看数据，发现语句并未提交。

       执行如下语句，回复MySQL的默认自动提交模式

SET AUTOCOMMIT=1;
avatar
文章
阅读
粉丝