开发进阶篇系列,MySQL事务特性

1. innodb在不同隔离级别下的一致性读及锁的差异
  不同的隔离级别下,innodb处理sql 时采用的一致性读策略和需要的锁是不同的,同时,数据恢复和复制机制的特点,也对一些sql的一致性读策略和锁策略有很大影响。对于许多sql, 隔离级别越高,innodb给记录集的锁就越严格(龙其是使用范围条件的时候),产生的锁冲突的可能性也就越高,对并发性事务处理性能的影响也就越大。因此,在应用中,应该尽量使用较低的隔离级别,减少锁争用。通常使用Read Commited隔离级别就足够了, 对于一些确实需要更高隔离级别的事务,可能在程序中执行 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 或SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE 动态来改变隔离级别。 

事务特性ACID

Atomic,原子:同一个事务里,要么都提交,要么都回滚;

Consistency,一致性:即在事务开始之前和事务结束以后,数据库的完整性约束没有被破坏;

Isolation,隔离:并发事务间的行数据是彼此隔离的;

Durability,持久:事务提交后,所有结果务必被持久化。

MySQL支持事务的存储引擎:Innodb,NDBcluster,TokuD

MySQL不支持事务的存储引擎:myisam  ,memory

1.隔离性通过锁的方式实现

2.原子性,一致性,持久性通过数据库的redo和undo来完成

撤销日志,undo log 没提交的事务撤销

重做日志,redo log 检查已经提交没有持久化的事务重做

 

显式事务启动|结束
1.以start transaction/begin开始事务
2.以commit/rollback transaction结束事务

隐形事务提交

主要是DDL,DCL会引发事务隐形提交
DDL语句
1.alter function
2.alter procedure
3.alter table
4.begin
5.create databases
6.create function
7.create index
8.create procedure
9.create table
10.drop databases
11.drop function
12.drop index
13.drop procedure
14.drop table
15.unlock tables
16.load master data
17.lock tables
18.rename table
19.truncate table
20.set autocommit=1
21.start transaction
22.create table…select
23.create temporary table ….select 除外
用户管理
1.create user
2.drop user
3.grant
4.rename user
5.revoke
6.set password
事务控制
1.begin
2.lock tables
3.set autocommit=1(if the valueis not already 1)
4.start transaction
5.unlock tables
6.lock tables unlock tables也会
7.flush tables with read lock & unlock table除外
数据导入
Load data infile
表管理语句
1.analyze table
2.cache index
3.check table
4.load index into cache
5.optimize table
6.repair table

事务隔离级别

图片 1

图片 2

Innodb采用next-key lock机制来避免幻读,RR innodb_locks_unsafe_for_binlog=1,它的作用是事务隔离级别降为RC,只有record lock,没有gap lock。

图片 3

set tx_isolation='read-uncommitted';
select @@session.tx_isolation;
 ------------------------ 
| @@session.tx_isolation |
 ------------------------ 
| READ-UNCOMMITTED       |
 ------------------------ 
1 row in set (0.00 sec)

脏读

 

Session1

Session2

>begin;

Query OK, 0 rows affected (0.00 sec)
>select * from t5 where id=7;
Empty set (0.00 sec)

 

 

>select * from t5 where id=7;
Empty set (0.00 sec
>insert into t5 select 7,'wwb',29,'dba','M';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
>select * from t5 where id=7;
 ---- ------ ------ --------- ------ 

| id | name | age  | content | sex  |
 ---- ------ ------ --------- ------ 

|  7 | wwb  |   29 | dba     | M    |
 ---- ------ ------ --------- ------ 
1 row in set (0.00 sec)

 

 

 

>select * from t5 where id=7;
 ---- ------ ------ --------- ------ 
| id | name | age  | content | sex  |
 ---- ------ ------ --------- ------ 
|  7 | wwb  |   29 | dba     | M    |
 ---- ------ ------ --------- ------ 
1 row in set (0.00 sec)

 

 

将事务隔离级别改为RC

>set tx_isolation='read-committed';
QueryOK, 0 rows affected (0.00 sec)
>select@@session.tx_isolation;
 ------------------------ 

|@@session.tx_isolation |

 ------------------------ 
|READ-COMMITTED         |
 ------------------------ 
1 row in set (0.00 sec)

 

  下面重点看下REPEATABLE READ与Read commited 锁申请的不同区别,在增删改查上申请的锁都是一致的,但在事务中锁释放的时间是不一样的这点需要注意。

不可重复读

Session1

Session2

>select * from t5 where id=7;
 ---- ------ ------ --------- ------ 
| id | name | age  | content | sex  |
 ---- ------ ------ --------- ------ 
|  7 | wwb  |   29 | dba     | M    |
 ---- ------ ------ --------- ------ 
1 row in set (0.00 sec)

 

 

 

select * from t5 where id=7;
 ---- ------ ------ --------- ------ 
| id | name | age  | content | sex  |
 ---- ------ ------ --------- ------ 
|  7 | wwb  |   29 | dba     | M    |
 ---- ------ ------ --------- ------ 
1 row in set (0.00 sec)

 

update t5 set sex='W' where id=7;
select * from t5;
 ---- ------ ------ --------- ------ 
| id | name | age  | content | sex  |
 ---- ------ ------ --------- ------ 
|  7 | wwb  |   29 | dba     | W    |
 ---- ------ ------ --------- ------ 
commit;
Query OK, 0 rows affected (0.00 sec)

 

 

 

select * from t5;
 ---- ------ ------ --------- ------ 
| id | name | age  | content | sex  |
 ---- ------ ------ --------- ------ 
|  7 | wwb  |   29 | dba     | W    |
 ---- ------ ------ --------- ------ 
1 row in set (0.00 sec)

 

 

幻读

begin;
Query OK, 0 rows affected (0.00 sec)
>select * from t5;
 ------ -------- ------ --------- ------ 
| id   | name   | age  | content | sex  |
 ------ -------- ------ --------- ------ 
|    7 | wwb    |   29 | dba     | M    |
|    8 | laoyan |   29 | dba     | M    |
 ------ -------- ------ --------- ------ 

 

begin;
Query OK, 0 rows affected (0.00 sec)
>select * from t5;
 ------ -------- ------ --------- ------ 
| id   | name   | age  | content | sex  |
 ------ -------- ------ --------- ------ 
|    7 | wwb    |   29 | dba     | M    |
|    8 | laoyan |   29 | dba     | M    |
 ------ -------- ------ --------- ------ 
2 rows in set (0.00 sec)

 

>insert into t5 select 9,'leilei',32,'dba','M';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
root@localhost:mysql3308.sock  03:24:05 [wwb]>select * from t5;
 ------ -------- ------ --------- ------ 
| id   | name   | age  | content | sex  |
 ------ -------- ------ --------- ------ 
|    7 | wwb    |   29 | dba     | M    |
|    8 | laoyan |   29 | dba     | M    |
|    9 | leilei |   32 | dba     | M    |
 ------ -------- ------ --------- ------ 
3 rows in set (0.01 sec)

 

 

 

select * from t5;
 ------ -------- ------ --------- ------ 
| id   | name   | age  | content | sex  |

 ------ -------- ------ --------- ------ 
|    7 | wwb    |   29 | dba     | M    |
|    8 | laoyan |   29 | dba     | M    |
|    9 | leilei |   32 | dba     | M    |
 ------ -------- ------ --------- ------ 
3 rows in set (0.00 sec)

 

>select@@session.tx_isolation;
 ------------------------ 
|@@session.tx_isolation |
 ------------------------ 
|REPEATABLE-READ        |
 ------------------------ 
1 row in set (0.00 sec)

 

 

Session

Session

>begin;
Query OK, 0 rows affected (0.00 sec)
>select * from t5 where id=7;
 ---- ------ ------ --------- ------ 
| id | name | age  | content | sex  |
 ---- ------ ------ --------- ------ 
|  7 | wwb  |   29 | dba     | W    |
 ---- ------ ------ --------- ------ 
1 row in set (0.00 sec)

 

 

 

>begin;
Query OK, 0 rows affected (0.00 sec)
>select * from t5 where id=7;
 ---- ------ ------ --------- ------ 
| id | name | age  | content | sex  |
 ---- ------ ------ --------- ------ 
|  7 | wwb  |   29 | dba     | W    |
 ---- ------ ------ --------- ------ 
1 row in set (0.00 sec)

 

>update t5 set sex='M' where id=7;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
>commit;
Query OK, 0 rows affected (0.00 sec)

 

 

 

>select * from t5 where id=7;
 ---- ------ ------ --------- ------ 
| id | name | age  | content | sex  |
 ---- ------ ------ --------- ------ 
|  7 | wwb  |   29 | dba     | W    |
 ---- ------ ------ --------- ------ 
1 row in set (0.00 sec)

SQL

修改查看隔离级别

在my.cnf配置文件中【mysqld】分段中,加入一行

Transaction-isolation=‘READ-COMMITTED’  #默认值是REPEATABLE-READ

在线动态修改

Set【GLOBAL|SESSIION】 TRANSACTION ISOLATION LEVEL READ COMMITTED

查看当前隔离级别

Select @@global.tx_isolation,@@session.tx_isolation,@@tx_isolation;

MySQL默认事务隔离级别是:RR

 

条件

Read uncommited

Read commited

Repeatable read

serializable

Select

 

=

None locks

Consisten read/

None locks

Consisten read/

None locks

Share locks

范围

None locks

Consisten read/

None locks

Consisten read/

None locks

Share next-key

Update

=

X(排它锁)

X

X

X

范围

X next-key

X next-key

X next-key

X next-key

Insert

 

X

X

X

X

REPLACE

无键冲突

X

X

X

X

键冲突

X next-key

X next-key

X next-key

X next-key

Delete

 

=

X

X

X

X

范围

本文由澳门新萄京官方网站发布于信息数据库,转载请注明出处:开发进阶篇系列,MySQL事务特性

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。