MySQL的基本学习(五)——事务、DCL和SQL基图

前言

前面写了一篇比较短的文章简单记录了一下多表查询的利用示例,这篇文章我们来继续学习MySQL数据库,我们这篇主要是学习事务的概念还有SQL语言的最后一类DCL。

 

 

 

 


事务

概念

如果一个包含多个步骤的业务操作叫做事务管理,那么这些操作要么同时成功,要么同时失败。

操作

语法

  • 开启事务:START TRANSACTION
  • 回滚:ROLLBACK
  • 提交;COMMIT

示例

我们来用一个示例学习事务的操作:

我们有一个账户表

CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10),
balance DOUBLE
);

里面有Tom和Jack,两名账户,他们各自有一千块钱

-- 添加数据
INSERT INTO 
account (name,balance)
VALUES
('Tom',1000),
('Jack',1000);

Tom想要转账500元给Jack。

-- 1.Tom的账户减少500元
UPDATE account SET balance=balance-500 WHERE name='Tom';
-- 2.Jack的账户增加500元
UPDATE account SET balance=balance+500 WHERE name='Jack';

我们先不考虑判读余额是否大于等于500的问题,我们就先考虑转账

我们利用SQLyog等数据库图形软件去执行这两条语句,很明显这样操作没有问题,最后结果:

    id  name    balance  
------  ------  ---------
     1  Tom           500
     2  Jack         1500

可是如果我们中间出现了一些错误,例如:

-- 1.Tom的账户减少500元
UPDATE account SET balance=balance-500 WHERE name='Tom';
出错了
-- 2.Jack的账户增加500元
UPDATE account SET balance=balance+500 WHERE name='Jack';

那么再执行,第一句会被执行,执行到中间的时候会报错无法解析“出错了”,最后结果:

    id  name    balance  
------  ------  ---------
     1  Tom           500
     2  Jack         1000

很明显,这是一次失败的转账,Tom损失了钱而Jack并没有得到钱。

故我们引出事务的概念,利用一致性的特点避免这种操作中的失误。

我们使用关键字START  TRANSACTION 来开启事务。

然后我们执行:

-- 0.开启事务
START TRANSACTION;
-- 1.Tom的账户减少500元
UPDATE account SET balance=balance-500 WHERE NAME='Tom';
出错了
-- 2.Jack的账户增加500元
UPDATE account SET balance=balance+500 WHERE NAME='Jack';

然后检索全表,我们发现

    id  name    balance  
------  ------  ---------
     1  Tom           500
     2  Jack         1000

这个数据明显是错误的,但是我们在别的地方登录这个MySQL数据库,我们去查看这个表,我们会发现Tom和Jack的账户还是一千,原来开启事务之后,该端的数据库的变化就只是临时性变化,当该端窗口关闭,临时变化就会被取消。

我们发现数据不如意之后,就可以使用如下命令进行回滚:

-- 回滚事务
ROLLBACK;

执行回滚之后,数据就会恢复到执行START TRANSACTION,即开启事务的地方。

如果我们把“出错了”去掉,再执行:

-- 0.开启事务
START TRANSACTION;
-- 1.Tom的账户减少500元
UPDATE account SET balance=balance-500 WHERE NAME='Tom';
-- 2.Jack的账户增加500元
UPDATE account SET balance=balance+500 WHERE NAME='Jack';

然后查看发现数据合理,则就可以使用下面的命令进行提交,实现数据库中的持久性修改。

-- 提交事务
COMMIT;

注意,如果开启了事务不进行提交,那么在关闭该端MySQL的时候,数据会自动回滚。

 

插曲:自动提交与手动提交

MySQL的提交方式

在MySQL数据库中事务默认自动提交。

即一条DML(增删改),自动进行了事务的开启、提交,从而一条语句就进行了一次持久化的更改。

总结一下就是:

事务提交的两种方式:

  1. 自动提交:(MySQL默认自动提交)
  2. 手动提交:需要先开启事务,在进行提交命令

修改事务的默认提交方式

我们在MySQL的软件中执行:

SELECT @@autocommit;

会返回:

@@autocommit  
--------------
             1

@@autocommit这个数据是1,则代表现在事务的提交方式是自动提交;如果是0,则为手动提交。

由此,修改事务的提交方式只需要修改这个值即可:

SET @@autocommit=0;

这个值调整成0之后,你的每一次登录MySQL的操作,一定要注意最后要手动执行:

-- 提交事务
COMMIT;

Oracle数据库是手动提交事务噢,尽早养成手动提交事务的好习惯吧。

事务的四大特征——ACID

  1. 原子性(Atomicity):事务是不可分割的最小操作单位,要么同时成功,要么同时失败。
  2. 持久性(Consistency):当事务结束之后(提交或者回滚),数据库会持久化的保存数据。
  3. 隔离性(Isolation):多个事务之间,相互独立。
  4. 一致性(Durability):事务操作前后,数据总量不变。

一致性是事务的最终目的,原子性、隔离性、持久性都是为了实现一致性。

 

事务的隔离级别

概念

事务的四大特征之中,有一个特征叫做“隔离性”,即多个事务之间隔离的,相互独立的。但是,如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题。

存在问题

多个事务操作用一批数据,会引发如下问题:

  1. 脏读:一个事务,读取到另一个事务中没有提交的数据
  2. 虚读(不可重复读):同一个事务中两次读取到的数据不一样
  3. 幻读:一个事务操作(DML)数据表中所有的记录,另一个事务添加了一条数据,则第一个事务查看不到自己的修改。

隔离级别

  • read  uncommitted:读未提交。
    • 产生的问题:脏读、虚读、幻读
  • read  committed:读已提交(Oracle默认)
    • 产生的问题:虚读、幻读
  • repeatable read:可重复读(MySQL默认)
    • 产生的问题:幻读
  • serializable:串行化(串行化类似于线程锁,是一种上锁策略)
    • 可以解决所有的问题

隔离级别从小到大安全性越来越高,但是效率越来越低。

查询隔离级别

SELECT  @@tx_isolation;

Mysql8以上,tx_isolation更名为 transaction_isolation

设置隔离级别

SET  global  transaction  isolation  level  级别字符串;

每次设置隔离级别之后,需要关闭当前连接再打开再查询,才能通过“查询隔离级别”看到隔离级别设置成功。

不同问题的演示

还是我们的account表,表中的Tom和Jack的账户各自有1000元。

mysql> SELECT * FROM account;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | Tom  |    1000 |
|  2 | Jack |    1000 |
+----+------+---------+
2 rows in set (0.00 sec)

我们开启一个窗口登录MySQL,然后我们设置隔离级别:读未提交

SET  global  transaction  isolation  level  read  uncommitted;

然后我们再开启一个窗口,然后两个窗口同时开启事务:

START TRANSACTION;

然后在窗口1中,给Tom减少500,给Jack增加500

UPDATE account SET balance=balance-500 WHERE name='Tom';
UPDATE account SET balance=balance+500 WHERE name='Jack';

注意,这里我们先不要提交。然后我们在两个窗口都分别查看account表,然后会发现两个表都如下:

mysql> SELECT * FROM account;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | Tom  |     500 |
|  2 | Jack |    1500 |
+----+------+---------+
2 rows in set (0.00 sec)

这就是脏读,事务并没提交,但是不同的登录端拿到的数据在同步变化。

另外这里还有虚读的问题,第二个MySQL登录端开启事务后,两次检索account表,余额发生了变化,数据不一样。

数据脏读有什么坏处呢?

例如Tom和Jack各自有1000元,Jack找Tom借500元,Tom利用数据库开启事务,给Jack转账了500,但是尚未提交,Tom让Jack去查看自己的余额,Jack用另一端登录MySQL发现自己的余额成了1500,所以就给Tom写了一张500元欠条,这时,Tom那边利用命令ROLLBACK进行了回滚,两人的余额又各自成了1000,Jack就很冤枉的欠了Tom500元。

 

数据虚读有什么坏处呢?

例如Jack的老婆要检查Jack的余额,Jack开启事务后,查看了一下自己的余额,发现是1000元,然后离开房间要去把老婆叫过来,这时Tom在另一个MySQL端给Jack转了500元,然后提交了Tom那边的事务。过了一会Jack的老婆来了,一检索余额发现有1500元,这Jack就非常冤枉。

避免脏读,我们接下来只要将事务的隔离级别升级成read  committed即可。

如果需要避免虚读,我们将事务的隔离级别升级成repeatable read即可,这种情况下,别的MySQL登录端提交了他们各自的事务,就可以看到数据的真正的变化了。

幻读问题在MySQL这里不能演示,所以我们就讲到这里了。

串行化

最高隔离级别串行化底层是加锁机制,感兴趣的可以试一下:一登录端开启事务进行操作的时候,另一登录端使用SELECT * FROM 检索表的时候会出现阻塞,因为该表已经被第一端给锁起来了,必须等待第一端结束事务(提交或回滚)。

 


DCL

SQL四大类语言中最后还有一个DCL没有讲解,这里来补上。

概念

DCL,(Data Control Language)数据控制语言,用来定义数据库的访问权限和安全级别,及创建用户,主要就是管理用户和授权两个方面。

关键字:GRANT、REVOKE等

不同于DDL操作数据库和数据表、DML用来增删改数据表中的数据、DQL来查询表中的数据,DCL主要来管理数据库的用户。

DCL语句只需简单掌握即可,在商业应用中,会有专门的数据库管理人员来管理数据库的账号等问题。

管理用户

我们的用户信息在MySQL的数据目录(一般是C:/ProgramData/MySQL/MySQL Server 8.0/Data)中,系统自带数据库mysql,就是存放了一些用户信息。

添加用户

一般出于安全性考虑,我们不会直接在user中INSERT,我们会使用专门的语法。

CREATE  USER  '用户名'@'主机名'  IDENTIFIED BY  '密码';

示例:

创建用户Tom,在主机登录:

CREATE USER 'Tom'@'localhost' IDENTIFIED  BY '123456';

创建用户Jack,可以在任意电脑登录:

CREATE USER 'Jack'@'%' IDENTIFIED  BY '123456';

删除用户

DROP  USER '用户名'@'主机名';

示例:

删除用户Tom

DROP USER 'Tom'@'localhost';

修改用户密码

ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';

忘记了root密码怎么办:

  1. cmd(管理员权限下)==》net  stop  mysql  :停止mysql服务
  2. 使用无验证方式启动mysql服务:mysqld  –skip-grant-tables,然后该窗口会卡顿
  3. 然后保持前一个窗口的前提下,打开一个新的mysql窗口,输入mysql直接登录
  4. 然后修改root密码
  5. 修改后关闭这两个窗口
  6. 打开任务管理器,找到mysql.exe进程,手动结束
  7. cmd(管理员权限下)==》net  start  mysql  :启动mysql服务

另外,如果出现提示mysql服务无效,请打开services.msc,在其中查看mysql的服务名,用这个服务名即可。

 

查询用户

切换到mysql数据库,查看user表即可:

USE  mysql;
SELECT * FROM user;

注意:

  1. 密码password是经过加密的
  2. %,表示可以在任意主机上使用用户登录数据库

 

权限管理

查询权限

SHOW GRANTS FOR '用户名'@'主机名';

例如

SHOW GRANTS FOR 'Les'@'%';


Grants for Les@%                 
---------------------------------
GRANT USAGE ON *.* TO `Les`@`%` 

即Les用户仅具有登录权限,使用Les去USE  db3,就会弹出“拒绝访问”。

 

授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

示例:

GRANT SELECT ON db3.account TO 'Les'@'%';

如此一来,Les就可以USE  db3,但是如果SHOW  TABLES; 只能看见account这一张表,并且只能对该表进行查询操作。

如果我们这样做:

GRANT SELECT,UPDATE,DELETE ON db3.account TO 'Les'@'%';

就可以对该account表进行多种操作了。

那么如果要给某用户授予所有权限且对于所有数据库和数据表呢?

GRANT ALL ON *.* TO 'Les'@'%';

从此Les就得到所有权限。

 

撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

示例:

REVOKE SELECT ON db3.account  FROM  'Les'@'%';

然后我们在Les端:

mysql> SELECT * FROM account;
ERROR 1142 (42000)

 

撤销也可以使用通配符,道理和上面一样。

 

 


SQL基图

本图作者:计枭(是的,就是is-hash.com的站长)

 

 

 

 


 

 

 

 

商业转载 请联系作者获得授权,非商业转载 请标明出处,谢谢

 

发表评论