mysql grant部份表的只读权限 mysql只读事务

文章目录

• 显式事务
• 隐式事务
• 隐式提交数据的情况
• 提交与回滚
• 保存点SAVEPOINT

使用事务有两种方式，分别为 显式事务 和 隐式事务

显式事务

第一步 START TRANSACTION 或者 BEGIN ，作用是显式开启一个事务。

BEGIN;
#或者
START TRANSACTION;

START TRANSACTION 语句相较于 BEGIN 特别之处在于，后边能跟随几个 修饰符 ：

1. READ ONLY ：标识当前事务是一个 只读事务 ，也就是属于该事务的数据库操作只能读取数据，而不
   能修改数据。
2. READ WRITE ：标识当前事务是一个读写事务 ，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据。
3. WITH CONSISTENT SNAPSHOT ：启动一致性读。

START TRANSACTION READ ONLY;#开启一个只读事务
START TRANSACTION READ ONLY,WITH CONSISTENT SNAPSHOT;#开启只读事多和一致性读
START TRANSACTION READ WRITE,WITH CONSISTENT SNAPSHOT;#开启读写事务和一致性读

• READ ONLY和READ WRITE是用来设置所谓的事务访问模式的，就是以只读还是读写的方式来访问数据库中的数据，一个事务的访问模式不能同时既设置为只读的又设置为读写的，所以不能同时把READ ONLY和READ WRITE放到START TRANSACTION语句后边
• 如果不显式指定事务的访问模式，那么该事务的访问模式就是读写模式。

第二步 一系列事务中的操作（主要是DML，不含DDL）

第三步 提交事务 或 中止事务（即回滚事务）

# 提交事务。当提交事务后，对数据库的修改是永久性的
COMMIT;
# 回滚事务。即撤销正在进行的所有没有提交的修改
ROLLBACK;
# 将事务回滚到某个保存点
ROLLBACK TO [SAVEPOINT]

其中关于SAVEPOINT相关操作有:

#在事务中创建保存点，方便后续针对保存点进行回滚。一个事务中可么存在多个保存点
SAVEPOINT 保存点名称;
#删除某个保存点
RELEASE SAVEPOINT保存点名称；

隐式事务

MySQL中有一个系统变量 autocommit ：

SHOW VARIABLES LIKE 'autocommit';
/*
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
*/

默认情况下，如果不显式的使用START TRANSACTION或者BEGIN语句开启一个事务，那么每一条语句都算是一个独立的事务，这种特性称之为事务的自动提交。也就是说，不以START TRANSACTION或者BEGIN语句显式的开启一个事务，那么下边这两条语句就相当于放到两个独立的事务中去执行

#  关键字：autocommit 
# set autocommit = false;

SHOW VARIABLES LIKE 'autocommit';#默认是ON

UPDATE account SET balance = balance - 10 WHERE id = 1; #此时这条DML操作是一个独立的事务

UPDATE account SET balance = balance + 10 WHERE id = 2; #此时这条DML操作是一个独立的事务

当然，如果想关闭这种 自动提交 的功能，可以使用下边两种方法之一：

• 显式的的使用 START TRANSACTION 或者 BEGIN
  语句开启一个事务。这样在本次事务提交或者回滚前会暂时关闭掉自动提交的功能。
• 把系统变量 autocommit 的值设置为 OFF ，就像这样：

SET autocommit = OFF;
#或
SET autocommit = 0;

这样的话，写入的多条语句就算是属于同一个事务了，直到我们显式的写出COMNIT语句来把这个事务提交掉，或者显式的写出ROLLBACK语句来把这个事务回滚掉。

#  如何关闭自动提交？
#方式1：
SET autocommit = FALSE; #针对于DML操作是有效的，对DDL操作是无效的。

UPDATE account SET balance = balance - 10 WHERE id = 1;

UPDATE account SET balance = balance + 10 WHERE id = 2; 

COMMIT; #或rollback;

#方式2：在autocommit为true的情况下，使用start transaction 或begin开启事务，那么DML操作就不会自动提交数据

START TRANSACTION;

UPDATE account SET balance = balance - 10 WHERE id = 1;

UPDATE account SET balance = balance + 10 WHERE id = 2; 

COMMIT; #或rollback;

隐式提交数据的情况

数据定义语言(Data definition language，缩写为:DDL)

数据库对象，指的就是数据库、表、视图、存储过程等结构。当使用CREATE 、ALTER、 DROP等语句去修改数据库对象时，就会隐式的提交前边语句所属于的事务。即:

BEGIN;
SELECT ... #事务中的一条语句
UPDATE ...#事务中的一条语句
...  #丰务中的其它语句
CREATE TABLE ...# 此语句会隐式的提交前边语句所属于的事务

隐式使用或修改mysql数据库中的表

当使用ALTER USER、CREATE USER、DROP USER 、GRANT、RENAME USER、REVOKE. SET PASSWORD等语句时也会隐式的提交前边语句所属于的事务

事务控制或关于锁定的语句

当在一个事务还没提交或者回滚时就又使用START TRANSACTION或者BEGIN语句开启了另一个事务时，会隐式的提交上—个事务。即:

BEGIN;
SELECT ... #事务中的一条语句
UPDATE ... #事务中的一条语句
...       #事务中的其它语句
BEGIN;   #此语句会隐式的提交前面语句所属于的事务

1. 当前的 autocommit 系统变量的值为 OFF ，我们手动把它调为 ON 时，也会 隐式的提交 前边语
   句所属的事务。
2. 使用 LOCK TABLES 、 UNLOCK TABLES 等关于锁定的语句也会 隐式的提交 前边语句所属的事务。

加载数据的语句

使用LOAD DATA语句来批量往数据库中导入数据时，也会隐式的提交前边语句所属的事务。

关于MySQL复制的一些语句

使用START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO等语句时会隐式的提交前边语句所属的事务。

其它的一些语句

使用ANALYZE TABLE、CACHE INDEX、CHECK TABLE、FLUSH、LOAD INDEX INTO CACHE 、0PTIMIZE TABLE、REPAIR TABLE、RESET 等语句也会隐式的提交前边语句所属的事务。

提交与回滚

# 案例分析
# SET autocommit = TRUE; 
#举例1： commit 和 rollback

USE ttst2;
#情况1：
CREATE TABLE user3(NAME VARCHAR(15) PRIMARY KEY);

SELECT * FROM user3;

BEGIN;
INSERT INTO user3 VALUES('张三'); #此时不会自动提交数据
COMMIT;

BEGIN; #开启一个新的事务
INSERT INTO user3 VALUES('李四'); #此时不会自动提交数据
INSERT INTO user3 VALUES('李四'); #受主键的影响，不能添加成功
ROLLBACK;

SELECT * FROM user3;
/*
+--------+
| NAME   |
+--------+
| 张三   |
+--------+
*/

#情况2：
TRUNCATE TABLE user3;  #DDL操作会自动提交数据，不受autocommit变量的影响。

SELECT * FROM user3;

BEGIN;
INSERT INTO user3 VALUES('张三'); #此时不会自动提交数据
COMMIT;

INSERT INTO user3 VALUES('李四');# 默认情况下(即autocommit为true)，DML操作也会自动提交数据。
INSERT INTO user3 VALUES('李四'); #事务的失败的状态

ROLLBACK;

SELECT * FROM user3;
/*
+--------+
| NAME   |
+--------+
| 张三   |
| 李四   |
+--------+
*/

#情况3：
TRUNCATE TABLE user3;

SELECT * FROM user3;

SELECT @@completion_type;

SET @@completion_type = 1;

BEGIN;
INSERT INTO user3 VALUES('张三'); 
COMMIT;


SELECT * FROM user3;

INSERT INTO user3 VALUES('李四');
INSERT INTO user3 VALUES('李四'); 

ROLLBACK;


SELECT * FROM user3;
/*
+--------+
| NAME   |
+--------+
| 张三   |
+--------+
*/

能看到相同的SQL代码，只是在事务开始之前设置了SET @@completion_type = 1;结果就和第一次处理的一样，只有一个“张三”。这是为什么呢?

这里讲解下 MySQL中completion_type参数的作用，实际上这个参数有3种可能:

1. completion=0，这是默认情况。当执行COMNIT的时候会提交事务，在执行下一个事务时，还需要使START TRANSACTION 或者BEGIN来开启。
2. completion=1，这种情况下，当提交事务后，相当于执行了COMMIT AND CHAIN，也就是开启一个 链式事务，即提交事务之后会开启一个相同隔离级别的事务。
3. completion=2，这种情况下CONMMIT=COMMIT AND RELEASE，也就是提交后，会自动与服务器断开连接

当我们设置 autocommit=0 时，不论是否采用 START TRANSACTION 或者 BEGIN 的方式来开启事务，都需要用 COMMIT 进行提交，让事务生效，使用 ROLLBACK 对事务进行回滚。

当我们设置 autocommit=1 时，每条 SQL 语句都会自动进行提交。 不过这时，如果你采用 START

TRANSACTION 或者 BEGIN 的方式来显式地开启事务，那么这个事务只有在 COMMIT 时才会生效，在 ROLLBACK 时才会回滚。

保存点SAVEPOINT

#举例3：体会savepoint

CREATE TABLE user3(NAME VARCHAR(15),balance DECIMAL(10,2));

BEGIN
INSERT INTO user3(NAME,balance) VALUES('张三',1000);
COMMIT;

SELECT * FROM user3;
/*
+--------+
| NAME   |
+--------+
| 张三   |
+--------+
*/

BEGIN;
UPDATE user3 SET balance = balance - 100 WHERE NAME = '张三';

UPDATE user3 SET balance = balance - 100 WHERE NAME = '张三';

SAVEPOINT s1;#设置保存点

UPDATE user3 SET balance = balance + 1 WHERE NAME = '张三';

ROLLBACK TO s1; #回滚到保存点


SELECT * FROM user3;

ROLLBACK; #回滚操作

SELECT * FROM user3;