mysql事务、隔离级别详解

发布时间：2019-02-27

事务四大特性

数据库事务的四大特性以及事务的隔离级别

⑴ 原子性（Atomicity）

　　原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，如果操作失败则不能对数据库有任何影响。

原子性是指属于原子性的操作集合是一个整体，不会出现一部分执行成功，一部分执行失败的情况，要么全部成功，要么全部失败回滚，一致性就是数据从一个一致的状态到另外一个一致的状态，原子性、隔离性、持久性可以保证一致性

⑵ 一致性（Consistency）

　　一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

　　拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

⑶ 隔离性（Isolation）

　　隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

　　即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

　　关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。

⑷ 持久性（Durability）

　　持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

隔离性分为：四种隔离等级

1、未提交读 read uncommited

read uncommitted

2、提交读

read committed

3、重复读

repeatable read

4、序列化

serializable

1）read uncommitted : 读取尚未提交的数据：哪个问题都不能解决

2）read committed：读取已经提交的数据：可以解决脏读 ---- oracle默认的

3）repeatable read：可以重复读：可以解决脏读和不可重复读 ---mysql默认的

4）serializable：串行化：可以解决脏读不可重复读和虚读---相当于锁表

以上四种隔离级别最高的是Serializable级别，最低的是Read ncommitted级别，当然级别越高，执行效率就越低。

像Serializable这样的级别，就是以锁表的方式使得其他的线程只能在锁外等待，所以平时选用何种隔离级别应该根据实际情况。

后记：隔离级别的设置只对当前链接有效。对于使用MySQL命令窗口而言，一个窗口就相当于一个链接，当前窗口设置的隔离级别只对当前窗口中的事务有效；

对于JDBC操作数据库来说，一个Connection对象相当于一个链接，

而对于Connection对象设置的隔离级别只对该Connection对象有效，

与其他链接Connection对象无关。

如果事务上一个事务还没有执行完毕，这又来一个新事务，只能等待上一个事务结束，这个新事务才能执行。

等待过程中，有可能会超时，报错 1205 lock wait timeout

如果说事务1在开启的事务中，没有提交、没有回滚，则事务2 如果对这张表进行操作，则会一直等待事务1的结束。

还有一种情况，会有超时

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

锁超时、重试事务。

如果事务1 执行了转账操作，遇到意外情况，意外退出。事务2正在等待执行，那么事务2可以正常执行吗？

mysql查看当前事务级别

mysql> SELECT @@tx_isolation;

+-----------------+

| @@tx_isolation |

+-----------------+

| REPEATABLE-READ |

+-----------------+

1 row in set (0.00 sec)

mysql的默认级别

3）repeatable read 可以重复读

oracle隔离级别

默认级别是 read committed 提交读

如何更改事务隔离级别

set tx_isolation='read-uncommitted'; 读未提交读

set tx_isolation='read-committed'; 可提交读

set session transaction isolation level repeatable read 重复读

select @@tx_isolation 查看当前会话的事务隔离级别

下面举例说明下：

首先将当前事务级别设置成 read-uncommitted 未提交读

read-uncommitted 未提交读

未提交读，又称为脏读

脏读：读取到上一个事务未提交的修改数据

提交读

前提设置当前会话的事务隔离级别是提交读

set tx_isolation='read-committed'; 可提交读

场景：

当事务1 开启事务

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> update shop_goods set pricle=pricle+100 where username='user3';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql>commit;

==================================================================

事务2 开启事务

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from shop_goods;

+----+----------+--------+

| id | username | pricle |

+----+----------+--------+

| 1 | user1 | 120 |

| 2 | user2 | 80 |

| 3 | user3 | 200 |

+----+----------+--------+

我们发现 user3 并没有更改，这是因为事务1 还没有进行提交，如果进行了 rollback 那么就会回滚

假设事务1 commit 提交了，那么事务2 会很快就能看到结果。

https://blog.csdn.net/jack__frost/article/details/71194208

整理：

数据库事务的四大特性以及事务的隔离级别

⑴ 原子性（Atomicity）

　　原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，如果操作失败则不能对数据库有任何影响。

⑵ 一致性（Consistency）

　　一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

⑶ 隔离性（Isolation）

　　关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。

⑷ 持久性（Durability）

　　持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

//查看当前的事务级别

select @@tx_isolation;

事务的终止

设置“自动提交”模式SET AUTOCOMMIT = 0每条SQL都是同一个事务的不同命令，之间由 COMMIT 或 ROLLBACK隔开掉线后，没有 COMMIT 的事务都被放弃

事务锁定模式

系统默认：不需要等待某事务结束，可直接查询到结果，但不能再进行修改、删除。缺点：查询到的结果，可能是已经过期的。优点：不需要等待某事务结束，可直接查询到结果。

需要用以下模式来设定锁定模式

1、SELECT …… LOCK IN SHARE MODE（共享锁）查询到的数据，就是数据库在这一时刻的数据（其他已commit事务的结果，已经反应到这里了）SELECT 必须等待，某个事务结束后才能执行

2、SELECT …… FOR UPDATE（排它锁）例如 SELECT * FROM tablename WHERE id<200那么id<200的数据，被查询到的数据，都将不能再进行修改、删除、SELECT …… LOCK IN SHARE MODE操作一直到此事务结束

共享锁和排它锁的区别：在于是否阻断其他客户发出的 SELECT …… LOCK IN SHARE MODE命令

3、INSERT / UPDATE / DELETE所有关联数据都会被锁定，加上排它锁

4、防插入锁例如 SELECT * FROM tablename WHERE id>200那么id>200的记录无法被插入

5、死锁自动识别死锁先进来的进程被执行，后来的进程收到出错消息，并按ROLLBACK方式回滚innodb_lock_wait_timeout = n 来设置最长等待时间，默认是50秒

事务隔离模式

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVELREAD UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE

1、不带SESSION、GLOBAL的SET命令只对下一个事务有效

2、SET SESSION为当前会话设置隔离模式3、SET GLOBAL为以后新建的所有MYSQL连接设置隔离模式（当前连接不包括在内）

隔离模式

READ UNCOMMITTED不隔离SELECT其他事务未完成的修改（未COMMIT），其结果也考虑在内

READ COMMITTED把其他事务的 COMMIT 修改考虑在内同一个事务中，同一 SELECT 可能返回不同结果

REPEATABLE READ（默认）不把其他事务的修改考虑在内，无论其他事务是否用COMMIT命令提交过同一个事务中，同一 SELECT 返回同一结果（前提是本事务，不修改）

SERIALIZABLE和REPEATABLE READ类似，给所有的SELECT都加上了共享锁

出错处理根据出错信息，执行相应的处理

mysql事物处理实例

MYSQL的事务处理主要有两种方法1.用begin,rollback,commit来实现 begin开始一个事务 rollback事务回滚 commit 事务确认2.直接用set来改变mysql的自动提交模式 mysql默认是自动提交的，也就是你提交一个query，就直接执行！可以通过 set autocommit = 0 禁止自动提交 set autocommit = 1 开启自动提交来实现事务的处理。但要注意当用set autocommit = 0 的时候，你以后所有的sql都将作为事务处理，直到你用commit确认或 rollback结束，注意当你结束这个事务的同时也开启了新的事务！按第一种方法只将当前的做为一个事务!MYSQL只有 INNODB和BDB类型的数据表才支持事务处理，其他的类型是不支持的!MYSQL5.0 WINXP下测试通过～ _

//设置成未提交读

set tx_isolation='read-uncommitted';

set tx_isolation='read-committed';

set tx_isolation='repeatable read';

set session transaction isolation level repeatable read

以上四种隔离级别最高的是Serializable级别，最低的是Read ncommitted级别，当然级别越高，执行效率就越低。

像Serializable这样的级别，就是以锁表的方式使得其他的线程只能在锁外等待，所以平时选用何种隔离级别应该根据实际情况。

mysql中默认隔离级别：可重复读

在MySQL数据库中默认的隔离级别为Repeatable read (可重复读)。

现在来看看MySQL数据库为我们提供的四种隔离级别：

1）read uncommitted : 读取尚未提交的数据：哪个问题都不能解决

2）read committed：读取已经提交的数据：可以解决脏读 ---- oracle默认的

3）repeatable read：可以重复读：可以解决脏读和不可重复读 ---mysql默认的

4）serializable：串行化：可以解决脏读不可重复读和虚读---相当于锁表

记住：设置数据库的隔离级别一定要是在开启事务之前！

setTransactionIsolation();

对于JDBC操作数据库来说，一个Connection对象相当于一个链接，而对于Connection对象设置的隔离级别只对该Connection对象有效，与其他链接Connection对象无关。

如果说事务1在开启的事务中，没有提交、没有回滚，则事务2 如果对这张表进行操作，则会一直等待事务1的结束。

还有一种情况，会有超时

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

如果事务1 执行了转账操作，遇到意外情况，意外退出。事务2正在等待执行，那么事务2可以正常执行吗？

因为写的时候会加排他锁，所以，在写的时候，能做到细粒度的锁住最小的数据单元是很重要的，因此，添加合适并且适当的索引，避免间隙锁

，更要避免锁表，是在设计数据库和实际操作表中必须要考虑的。

start transaction;

或者使用 begin;

update shop_goods set pricle=pricle+10 where username='user2';

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> update shop_goods set pricle=pricle+20 where username='user1';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> update shop_goods set pricle=pricle-20 where username='user2';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> commit;

Query OK, 0 rows affected (0.01 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> update shop_goods set pricle=pricle+20 where username='user1';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> update shop_goods set pricle=pricle-20 where username='user2';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

insert into shop_goods (username,pricle) values('user3',100);

1、不管autocommit 是1还是0

START TRANSACTION 后，只有当commit数据才会生效，ROLLBACK后就会回滚。

2、当autocommit 为 0 时

不管有没有START TRANSACTION。

只有当commit数据才会生效，ROLLBACK后就会回滚。

3、如果autocommit 为1 ，并且没有START TRANSACTION 。

调用ROLLBACK是没有用的。即便设置了SAVEPOINT。

深入解析MySQL分区（Partition）功能 https://blog.csdn.net/tjcyjd/article/details/11194489

1、什么是分区表

所有数据还在一个表中，但物理存储根据一定的规则放在不同的文件中。这个是mysql支持的功能，业务rd代码无需改动。

2、分区表在什么场景下使用

3、分区表与分库分表的区别

回答：

1）分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁

2）一旦数据量并发量上来，如果在分区表实施关联，就是一个灾难

3）自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写了一个sql，都不确定mysql是怎么玩的，不太可控

4）运维的坑，嘿嘿

4、分区表可以关联查询吗

分区的策略

全量扫描数据：通过where条件语句来限制扫描的分区，需要注意的是，限制的分区的数量不能太多。

建立分区索引，设置热点分区将热点数据设置到一个分区中，这样能够充分利用缓存和索引，大幅提高数据查询效率

ps:以上策略均以查询得到过滤，丢掉额外的分区，分区本身不产生额外的代价为准则

分区表的类型

range 分区（现在很少用，因为使用上有局限在mysql5.5之后的话增加了Columns 分区，更加好用）

list 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

hash分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数

key分区：根据MySQLS数据库提供的哈希函数来进行分区

一个表最多可以创建多少个分区？

MySQL只支持水平分区，不支持垂直分区。

水平分区：将同一表中不同行的记录分配到不同的物理文件中。

垂直分区：将同一表中不同列的记录分配到不同的物理文件中。

MySQL暂时还不支持全局分区索引

MySQL数据库支持以下几种类型的分区：

RANGE分区

LIST分区

HASH分区

KEY分区

> create table t4 (

col1 int null,

col2 date null,

col3 int null,

col4 int null,

key (col3)

) engine=InnoDB

partition BY HASH (col3)

partitions 4;

分区类型：

1 RANGE分区：

> create table t1 (id int)

partition by range(id)(

partition p0 values less than (10),

partition p1 values less than (20));

当数据小于10的时候，插入p0分区。大于等于10小于20时候，插入p1分区。

> INSERT INTO t1 SELECT 12;

> INSERT INTO t1 SELECT 2;

表物理文件变成了下面这种：

-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 10:14 t1#P#p0.ibd

-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 10:17 t1#P#p1.ibd

从表面上，看不出来到底插入到什么分区中了，可以用下面的命令查看：

SELECT * from information_schema.PARTITIONS where table_schema=database() and table_name='t1'\G

分区表语法

分区表分为

RANGE,

LIST,

HASH,

KEY

四种类型,并且分区表的索引是可以局部针对分区表建立的

创建分区表

CREATE TABLE sales (

id INT AUTO_INCREMENT,

amount DOUBLE NOT NULL,

order_day DATETIME NOT NULL,

PRIMARY KEY(id, order_day)

) ENGINE=Innodb PARTITION BY RANGE(YEAR(order_day)) (

PARTITION p_2010 VALUES LESS THAN (2010),

PARTITION p_2011 VALUES LESS THAN (2011),

PARTITION p_2012 VALUES LESS THAN (2012),

PARTITION p_catchall VALUES LESS THAN MAXVALUE);

要注意如果这么做,则order_day必须包含在主键中,且会产生一个问题,就是当年份超过阈值,到了2013,2014时,需要手动创建这些分区

替代方法就是使用HASH

CREATE TABLE sales (

id INT PRIMARY KEY AUTO_INCREMENT,

amount DOUBLE NOT NULL,

order_day DATETIME NOT NULL

) ENGINE=Innodb PARTITION BY HASH(id DIV 1000000);

这种分区表示每100W条数据建立一个分区,且没有阈值范围的影响

关于存储引擎的限制

2.1 MERGE引擎不支持分区，分区表也不支持merge。

2.2 FEDERATED引擎不支持分区。这限制可能会在以后的版本去掉。

2.3 CSV引擎不支持分区

2.4 BLACKHOLE引擎不支持分区

2.5 在NDBCLUSTER引擎上使用分区表，分区类型只能是KEY(or LINEAR KEY) 分区。

2.6 当升级MYSQL的时候，如果你有使用了KEY分区的表（不管是什么引擎，NDBCLUSTER除外），那么你需要把这个表dumped在 reloaded。

2.7 分区表的所有分区或者子分区的存储引擎必须相同，这个限制也许会在以后的版本取消。

不指定任何引擎（使用默认引擎）。

所有分区或者子分区指定相同引擎。

作为程序员一定要保持良好的睡眠，才能好编程

mysql事务、隔离级别详解

mysql查看当前事务级别

mysql的默认级别

oracle隔离级别

如何更改事务隔离级别

read-uncommitted 未提交读

提交读

更多>> Mysql