SQL: 事务隔离

事务并发处理可能存在的异常都有哪些?

-- ----------------------------
-- Table structure for heros_temp
-- ----------------------------
DROP TABLE IF EXISTS `heros_temp`;
CREATE TABLE `heros_temp`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of heros_temp
-- ----------------------------
INSERT INTO `heros_temp` VALUES (1, '张飞');
INSERT INTO `heros_temp` VALUES (2, '关羽');
INSERT INTO `heros_temp` VALUES (3, '刘备');

-- SQL> 开头表示为使用命令行操作.
SQL> BEGIN;
SQL> INSERT INTO heros_temp values(4, '吕布');
SQL> SELECT * FROM heros_temp;

脏读: 还没提交事务,其余人已经看到没提交的数据

SQL> SELECT name FROM heros_temp WHERE id = 1;


SQL> BEGIN;
SQL> UPDATE heros_temp SET name = '张翼德' WHERE id = 1;


SQL> SELECT name FROM heros_temp WHERE id = 1;

不可重复读: 同一条记录,两次读取的结果不同

SQL> SELECT * FROM heros_temp;

SQL> BEGIN;
SQL> INSERT INTO heros_temp values(4, '吕布');


SQL> SELECT * FROM heros_temp;

MySQL数据库图标
image-2927

总结

  1. 脏读:读到了其他事务还没有提交的数据。
  2. 不可重复读:对某数据进行读取,发现两次读取的结果不同,也就是说没有读到相同的内容。这是因为有其他事务对这个数据同时进行了修改或删除。
  3. 幻读:事务 A 根据条件查询得到了 N 条数据,但此时事务 B 更改或者增加了 M 条符合事务 A 查询条件的数据,这样当事务 A 再次进行查询的时候发现会有 N+M 条数据,产生了幻读。

事务的隔离级别有哪些

隔离级别名称脏读不可重复读幻读
读未提交(READ UNCOMMITTED)允许允许允许
读已提交(READ COMMITTED)禁止允许允许
可重复读(REPEATABLE READ)禁止禁止允许
可串行化(SERIALIZABLE)禁止禁止禁止
  1. 读未提交,也就是允许读到未提交的数据,这种情况下查询是不会使用锁的,可能会产生脏读、不可重复读、幻读等情况。
  2. 读已提交就是只能读到已经提交的内容,可以避免脏读的产生,属于 RDBMS 中常见的默认隔离级别(比如说 Oracle 和 SQL Server),但如果想要避免不可重复读或者幻读,就需要我们在 SQL 查询的时候编写带加锁的 SQL 语句。
  3. 可重复读,保证一个事务在相同查询条件下两次查询得到的数据结果是一致的,可以避免不可重复读和脏读,但无法避免幻读。MySQL 默认的隔离级别就是可重复读。
  4. 可串行化,将事务进行串行化,也就是在一个队列中按照顺序执行,可串行化是最高级别的隔离等级,可以解决事务读取中所有可能出现的异常情况,但是它牺牲了系统的并发性。

使用MySQL客户端来模拟三种异常

-- mysql> 为mysql客户端的提示.
--  ./mysql -h localhost -uroot -p 登录MySQL客户端.在MySQL的安装路径的bin目录下面执行.

mysql> SHOW VARIABLES LIKE 'transaction_isolation';

-- 在老版本中,比如我的:5.7.11 中,上面的语句应该为下面的这样:
mysql> SHOW VARIABLES LIKE 'tx_isolation';

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

mysql> SET autocommit = 0;

mysql> SHOW VARIABLES LIKE 'autocommit';

模拟’脏读’

客户端2,不要提交事务.

BEGIN;
INSERT INTO heros_temp values(4, '吕布');

客户端1

SELECT * FROM heros_temp;

客户端1中读取了客户端2未提交的新英雄’吕布’,实际上客户端2可能马上回滚,从而造成了’脏读’.

模拟’不可重复读’

mysql> BEGIN;
mysql> UPDATE heros_temp SET name = '张翼德' WHERE id = 1;

对于客户端1来说,同一条查询语句出现了’不可重复读’.

模拟’幻读’

客户端1

SELECT * FROM heros_temp;

客户端2,不要提交事务.

BEGIN;
INSERT INTO heros_temp values(4, '吕布');

查询某一个范围的数据行变多了或者少了.

不可重复读 VS 幻读的区别

  1. 不可重复读是同一条记录的内容被修改了,重点在于UPDATE或DELETE
  2. 幻读是查询某一个范围的数据行变多了或者少了,重点在于INSERT

SQL: 事务

事务的特性: ACID

事务的特性:要么完全执行,要么都不执行。不过要对事务进行更深一步的理解,还要从事务的 4 个特性说起,这 4 个特性用英文字母来表达就是 ACID。

  1. A,也就是原子性(Atomicity)。原子的概念就是不可分割,你可以把它理解为组成物质的基本单位,也是我们进行数据处理操作的基本单位。
  2. C,就是一致性(Consistency)。一致性指的就是数据库在进行事务操作后,会由原来的一致状态,变成另一种一致的状态。也就是说当事务提交后,或者当事务发生回滚后,数据库的完整性约束不能被破坏。
  3. I,就是隔离性(Isolation)。它指的是每个事务都是彼此独立的,不会受到其他事务的执行影响。也就是说一个事务在提交之前,对其他事务都是不可见的。
  4. D,指的是持久性(Durability)。事务提交之后对数据的修改是持久性的,即使在系统出故障的情况下,比如系统崩溃或者存储介质发生故障,数据的修改依然是有效的。因为当事务完成,数据库的日志就会被更新,这时可以通过日志,让系统恢复到最后一次成功的更新状态。

MySQL数据库图标
image-2923

事务的控制

  1. START TRANSACTION或者BEGIN,作用是显式开启一个事务.
  2. COMMIT: 提交事务,当提交事务后,对数据库的修改是永久性的.
  3. ROLLBACK或者ROLLBACK TO [SAVEPOINT],意为回滚事务.意思是撤销正在进行的所有没有提交的修改,或者将事务回滚到某个保存点.
  4. SAVEPOINT: 在事务中创建保存点,方便后续针对保存点进行回滚.一个事务中可以存在多个保存点.
  5. RELEASE SAVEPOINT: 删除某个保存点.
  6. SET TRANSACTION,设置事务的隔离级别.

使用事务有两种方式,分别为隐式事务和显式事务.隐式事务实际上就是自动提交,Oracle默认不自动提交,需要手写COMMIT命令.而MySQL默认自动提交,当然我们可以配置MySQL的参数:

mysql> set autocommit =0;  // 关闭自动提交

mysql> set autocommit =1;  // 开启自动提交




CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
BEGIN;
INSERT INTO test SELECT '关羽';
COMMIT;
BEGIN;
INSERT INTO test SELECT '张飞';
INSERT INTO test SELECT '张飞';
ROLLBACK;
SELECT * FROM test;




CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
BEGIN;
INSERT INTO test SELECT '关羽';
COMMIT;
INSERT INTO test SELECT '张飞';
INSERT INTO test SELECT '张飞';
ROLLBACK;
SELECT * FROM test;



CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
SET @@completion_type = 1;
BEGIN;
INSERT INTO test SELECT '关羽';
COMMIT;
INSERT INTO test SELECT '张飞';
INSERT INTO test SELECT '张飞';
ROLLBACK;
SELECT * FROM test;



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

事务特性的理解:原子性是基础,隔离性是手段,一致性是约束条件,持久性是目的.

SQL: 存储过程

定义:

CREATE PROCEDURE 存储过程名称 ([参数列表])
BEGIN
    需要执行的语句
END    
CREATE PROCEDURE `add_num`(IN n INT)
BEGIN
       DECLARE i INT;
       DECLARE sum INT;
       
       SET i = 1;
       SET sum = 0;
       WHILE i <= n DO
              SET sum = sum + i;
              SET i = i +1;
       END WHILE;
       SELECT sum;
END
DELIMITER //
CREATE PROCEDURE `add_num1`(IN n INT)
BEGIN
       DECLARE i INT;
       DECLARE sum INT;
       
       SET i = 1;
       SET sum = 0;
       WHILE i <= n DO
              SET sum = sum + i;
              SET i = i +1;
       END WHILE;
       SELECT sum;
END //
DELIMITER ;

MySQL数据库图标
image-2918

CREATE PROCEDURE `get_hero_scores`(
       OUT max_max_hp FLOAT,
       OUT min_max_mp FLOAT,
       OUT avg_max_attack FLOAT,  
       s VARCHAR(255)
       )
BEGIN
       SELECT MAX(hp_max), MIN(mp_max), AVG(attack_max) FROM heros WHERE role_main = s INTO max_max_hp, min_max_mp, avg_max_attack;
END

要一起执行:

CALL get_hero_scores(@max_max_hp, @min_max_mp, @avg_max_attack, '战士');
SELECT @max_max_hp, @min_max_mp, @avg_max_attack;

流控制语句

CASE 
    WHEN expression1 THEN ...
    WHEN expression2 THEN ...
    ...
    ELSE 
    --ELSE 语句可以加,也可以不加。加的话代表的所有条件都不满足时采用的方式。
END

SQL: 视图

如何创建,更新和删除视图

创建视图: CREATE VIEW

创建视图的语法:

CREATE VIEW view_name AS
SELECT column1, column2
FROM table
WHERE condition

创建视图:

CREATE VIEW player_above_avg_height AS
SELECT player_id, height
FROM player
WHERE height > (SELECT AVG(height) from player);

查询:

SELECT * FROM player_above_avg_height;

嵌套视图

CREATE VIEW player_above_above_avg_height AS
SELECT player_id, height
FROM player
WHERE height > (SELECT AVG(height) from player_above_avg_height);

修改视图:ALTER VIEW

语法:

ALTER VIEW view_name AS
SELECT column1, column2
FROM table
WHERE condition;

示例:

ALTER VIEW player_above_avg_height AS
SELECT player_id, player_name, height
FROM player
WHERE height > (SELECT AVG(height) from player);

SELECT * FROM player_above_avg_height;

删除视图:DROP VIEW

语法:

DROP VIEW view_name;

示例:

DROP VIEW player_above_avg_height

SQLite不支持视图的修改,仅支持只读视图.也就是如果要修改,则只能先DROP然后CREATE.

如何使用视图简化SQL操作

利用视图完成复杂的连接

CREATE VIEW player_height_grades AS
SELECT p.player_name, p.height, h.height_level
FROM player as p JOIN height_grades as h
ON height BETWEEN h.height_lowest AND h.height_highest;

SELECT * FROM player_height_grades WHERE height >= 1.90 AND height <= 2.08;

MySQL数据库图标
image-2916

利用视图对数据进行格式化

CREATE VIEW player_team AS 
SELECT CONCAT(player_name, '(' , team.team_name , ')') AS player_team FROM player JOIN team WHERE player.team_id = team.team_id;

SELECT * FROM player_team;

使用视图与计算字段

CREATE VIEW game_player_score AS
SELECT game_id, player_id, (shoot_hits-shoot_3_hits)*2 AS shoot_2_points, shoot_3_hits*3 AS shoot_3_points, shoot_p_hits AS shoot_p_points, score  FROM player_score;

SELECT * FROM game_player_score;

视图是虚拟表,有些RDBMS不支持对视图创建索引.

SQL: 99的连接与SQL92的连接

交叉连接

SELECT * FROM player CROSS JOIN team;

SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3;

自然连接

SQL92:

SELECT player_id, a.team_id, player_name, height, team_name FROM player as a, team as b WHERE a.team_id = b.team_id;

SQL99:

SELECT player_id, team_id, player_name, height, team_name FROM player NATURAL JOIN team 

ON连接

SELECT player_id, player.team_id, player_name, height, team_name FROM player JOIN team ON player.team_id = team.team_id;

SELECT p.player_name, p.height, h.height_level FROM player as p JOIN height_grades as h ON height BETWEEN h.height_lowest AND h.height_highest

USING连接

SELECT player_id, team_id, player_name, height, team_name FROM player JOIN team USING(team_id)

外连接

左外连接

SQL92:

SELECT * FROM player, team where player.team_id = team.team_id(+)

SQL99:

SELECT * FROM player LEFT JOIN team ON player.team_id = team.team_id;

MySQL数据库图标
image-2914

右外连接

SQL92:

SELECT * FROM player, team where player.team_id(+) = team.team_id

SQL99:

SELECT * FROM player RIGHT JOIN team ON player.team_id = team.team_id

外连接

SQL99

MySQL不支持:

SELECT * FROM player FULL JOIN team ON player.team_id = team.team_id;

自连接

SQL92:

SELECT b.player_name, b.height FROM player as a , player as b WHERE a.player_name = '布雷克 - 格里芬' and a.height < b.height;

SQL99:

SELECT b.player_name, b.height FROM player as a JOIN player as b ON a.player_name = '布雷克 - 格里芬' and a.height < b.height;

不同DBMS中使用连接需要注意的地方

  1. 不是所有的DBMS都支持全外连接;
  2. Oracle 没有表别名 AS
  3. SQLite的外连接只有左连接

连接的性能问题注意事项

  1. 控制连接表的数量
  2. 在连接时不要忘记WHERE语句
  3. 使用自连接而不是子查询