当前位置：Gxlcms > 数据库问题 > MySQL事务特性,隔离级别

MySQL事务特性,隔离级别

时间：2021-07-01 10:21:17 帮助过：21人阅读

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

事务隔离级别

技术分享

技术分享

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

技术分享

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)

 

不可重复读

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)

 


 

修改查看隔离级别

在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

 

MySQL事务特性,隔离级别

标签：pass   bsp   可重复   cin   lsp   rom   img   ant   start   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！