文档章节

数据库锁机制与事务隔离级别

左手在打字
 左手在打字
发布于 2017/07/26 18:07
字数 2474
阅读 47
收藏 0

精选30+云产品,助力企业轻松上云!>>>

锁机制:

共享锁:由读表操作加上的锁,加锁后其他用户只能获取该表或行的共享锁,不能获取排它锁,也就是说只能读不能写

排它锁:由写表操作加上的锁,加锁后其他用户不能获取该表或行的任何锁,典型是mysql事务中的

锁的范围:

行锁: 对某行记录加上锁

表锁: 对整个表加上锁

这样组合起来就有,行级共享锁,表级共享锁,行级排他锁,表级排他锁

Mysql测试:

 

执行完这句以后

1)当其他事务想要获取共享锁,比如事务隔离级别为SERIALIZABLE的事务,执行

将会被挂起,因为SERIALIZABLE的select语句需要获取共享锁

2)当其他事务执行

也会被挂起,因为for update会获取这一行数据的排它锁,需要等到前一个事务释放该排它锁才可以继续进行

注意:要记住mysql有一个autocommit参数,默认是on,他的作用是每一条单独的查询都是一个事务,并且自动开始,自动提交(执行完以后就自动结束了,如果你要适用select for update,而不手动调用 start transaction,这个for update的行锁机制等于没用,因为行锁在自动提交后就释放了),所以事务隔离级别和锁机制即使你不显式调用start transaction,这种机制在单独的一条查询语句中也是适用的,分析锁的运作的时候一定要注意这一点

效果图:

接下来执行update写操作:

 

我们发现sql一直无法自动提交,因为上面的sql:select *from model_refresh_config a where a.table_name='hx_online_call_return_policy' for UPDATE;一直持有该行的排它锁,因此无法进行写操作。

oracle测试:

 

 

 

可以看出update语句已经等待锁4分钟,此时执行commit

 

我们可以看到在锁释放后,update语句就执行完了。

事务:

SQL 标准用三个必须在并行的事务之间避免的现象定义了四个级别的事务隔离。 这些不希望发生的现象是: 

脏读(dirty reads):一个事务读取了另一个未提交的并行事务写的数据。 

(事务T1更新了一行记录的内容,但是并没有提交所做的修改。事务T2读取更新后的行,然后T1执行回滚操作,取消了刚才所做的修改。现在T2所读取的行就无效了。)

exp:

小明的分数为89,事务A中把他的分数改为98,但事务A尚未提交。

与此同时,

事务B正在读取小明的分数,读取到小明的分数为98。

随后,

事务A发生异常,而回滚了事务。小明的分数又回滚为89。

最后,

事务B读取到的小明的分数为98的数据即为脏数据,事务B做了一次脏读。

(大部分数据库缺省的事物隔离级别都不会出现这种状况)

 

 

不可重复读(non-repeatable reads):一个事务重新读取前面读取过的数据, 发现该数据已经被另一个已提交的事务修改过。 

(事务T1读取一行记录,紧接着事务T2修改了T1刚才读取的那一行记录。然后T1又再次读取这行记录,发现与刚才读取的结果不同。这就称为“不可重复”读,因为T1原来读取的那行记录已经发生了变化。)

exp

在事务A中,读取到小明的分数为89,操作没有完成,事务还没提交。

与此同时,

事务B把小明的分数改为98,并提交了事务。

随后,

在事务A中,再次读取小明的分数,此时工资变为98。在一个事务中前后两次读取的结果并不致,导致了不可重复读。

 

幻读(phantom read):一个事务重新执行一个查询,返回一套符合查询条件的行, 发现这些行因为其他最近提交的事务而发生了改变。

(事务T1读取一条指定的WHERE子句所返回的结果集。然后事务T2新插入 一行记录,这行记录恰好可以满足T1所使用的查询条件中的WHERE 子句的条件。然后T1又使用相同的查询再次对表进行检索,但是此时却看到了事务T2刚才插入的新行。这个新行就称为“幻像”,因为对T1来说这一行就像突 然出现的一样。)

exp

目前分数为90分以上的的学生有15人,事务A读取所有分数为90分以上的的学生人数有15人。

此时,事务B插入一条分数为99的学生记录。

这是,事务A再次读取90分以上的的学生,记录为16人。此时产生了幻读。

(大部分数据库缺省的事物隔离级别都会出现这种状况,此种事物隔离级别将带来表级锁)

事务隔离级别描述:

 

离级别

脏读(Dirty Read)

不可重复读(NonRepeatable Read)

幻读(Phantom Read)

读未提交(Read uncommitted)

可能

可能

可能

读已提交(Read committed)

不可能

可能

可能

可重复读(Repeatable read)

不可能

不可能

可能

可串行化(Serializable )

不可能

不可能

不可能

 

ORACLE默认的是 READ COMMITTED。

MYSQL默认的是 REPEATABLE READ。

 如果数据库的隔离级别为REAE_UNCOMMITTED, 则其他线程可以看到未提交的数据, 因此就出现脏读;

 如果数据库隔离级别设为READ_COMMITTED,即没提交的数据别人是看不见的,就避免了脏读;但是,正在读取的数据只获得了读取锁,读完之后就解锁,不管当前事务有没有结束,这样就容许其他事务修改本事务正在读取的数据。导致不可重复读。

REPEATABLE READ因为对正在操作的数据加锁,并且只有等到事务结束才放开锁, 则可以避免不可重复读;

REPEATABLE READ只能保证正在被本事务操作的数据不被其他事务修改,却无法保证有其他事务提交新的数据。 则有可能线程1在操作表T1的时候(特别是统计性的事务),其他线程仍然可以提交新数据到表T1,这样会导致线程1两次统计的结果不一致,就像发生幻觉一样。

  SERIALIZABLE因为获得范围锁,且事务是一个接着一个串行执行,则保证了不会发生幻读。

  由此可见,隔离级别越高,受其他事物干扰越少,并发性能越差。

在MySQL中默认事务隔离级别是可重复读(Repeatable read).可通过SQL语句查询:

查看系统级别的事务隔离级别

  mysql> SELECT @@global.tx_isolation;

结果:

查看会话级别的事务隔离级别:

mysql> SELECT @@tx_isolation;

结果:

修改事务隔离级别

      mysql> set global transaction isolation level read committed;

      mysql> set session transaction isolation level read committed;

     下面来说说不同的事务隔离级别的实例效果,例子开启两个回话窗口A,B,在A中修改事务隔离级别,在B中开启事务并修改数据,然后在A中的事务查看B的事务修改效果:

1.READ-UNCOMMITTED(读取未提交内容)级别

  1)A修改事务级别并开始事务,对user表做一次查询

   

 

  2)B更新一条记录

   

 

  3)此时B事务还未提交,A在事务内做一次查询,发现查询结果已经改变

   

           而没有修改事务级别的窗口数据是没有变化的(默认事务级别REPEATABLE-READ

         

 

  4)B进行事务回滚

      

  5)A再做一次查询,查询结果又变回去了

   

 

  6)A表对user表数据进行修改

   

 

  7)B表重新开始事务后,对user表记录进行修改,修改被挂起,直至超时。 

 

   

        可以看出READ-UNCOMMITTED隔离级别,当两个事务同时进行时,即使事务没有提交,所做的修改也会对事务内的查询做出影响,这种级别显然很不安全。

2. READ-COMMITTED(读取提交内容)

1)设置A的事务隔离级别,并进入事务做一次查询

   

 

  2)B开始事务,并对记录进行修改

   

 

  3)A再对user表进行查询,发现记录没有受到影响

   

 

  4)B提交事务

   

 

  5)A再对user表查询,发现记录被修改

   

 

  6)A对user表进行修改

   

 

  7)B重新开始事务,并对user表同一条进行修改,发现修改被挂起,直到超时。

 

   

 

          READ-COMMITTED事务隔离级别,只有在事务提交后,才会对另一个事务产生影响。

 

3. REPEATABLE-READ(可重读)

  1)A设置事务隔离级别,进入事务后查询一次

   

 

  2)B开始事务,并对user表进行修改

   

 

  3)A查看user表数据,数据未发生改变

   

 

  4)B提交事务

   

 

  5)A再进行一次查询,结果还是没有变化

   

 

  6)A提交事务后,再查看结果,结果已经更新

   

 

  7)A重新开始事务,并对user表进行修改

   

   

  8)B表重新开始事务,并对user表进行修改,修改被挂起,直到超时。

   

       REPEATABLE-READ事务隔离级别,当两个事务同时进行时,其中一个事务修改数据对另一个事务不会造成影响,即使修改的事务已经提交也不会对另一个事务造成影响。

       事务隔离级别使用待补充。。。

左手在打字
粉丝 1
博文 3
码字总数 7020
作品 0
丰台
程序员
私信 提问
加载中
请先登录后再评论。
事务和锁机制是什么关系? 开启事务就自动加锁了吗?

数据库锁 因为数据库要解决并发控制问题。在同一时刻,可能会有多个客户端对同一张表进行操作,比如有的在读取该行数据,其他的尝试去删除它。为了保证数据的一致性,数据库就要对这种并发操...

osc_1t8zhli7
2019/11/22
6
0
MySQL 事务的隔离级别

MySQL 事务隔离级别查看及修改 参考:mysql修改事务隔离级别 查看MySQL隔离级别 修改MySQL 隔离级别 注意:默认的行为(不带session和global)是为下一个(未开始)事务设置隔离级别。如果你...

lin_zone
04/16
0
0
Mysql InnoDB锁机制

一、MySQL锁机制概述: (一)什么是锁,以及为什么使用锁和锁的运作? 锁是计算机协调多个进程或纯线程并发访问某一资源的机制。在数据库中,除传统的计算资源(CPU、RAM、I/O)的争用以外,...

皮蛋和
2017/12/31
0
0
mysql事务和锁 SELECT FOR UPDATE

事务: 当然有的人用begin /begin work .推荐用START TRANSACTION 是SQL-99标准启动一个事务。 当用set autocommit = 0 的时候,你以后所有的sql都将作为事务处理,直到你用commit确认或 ro...

SibylY
2016/08/22
754
0
sybase锁机制以及死锁现象避免

sybase锁分类 sybase隔离级别 sybase死锁 隔离级别对死锁的影响 sybase锁升级 总结 在数据库实现中,通过锁定机制控制数据库的并发访问,保证数据库访问的正确性。根据定义: 锁定是一种并发...

wcczrx
2017/03/16
0
0

没有更多内容

加载失败,请刷新页面

加载更多

将字符串拆分为具有多个单词边界定界符的单词 - Split Strings into words with multiple word boundary delimiters

问题: I think what I want to do is a fairly common task but I've found no reference on the web. 我认为我想做的是一项相当普通的任务,但是我在网络上找不到任何参考。 I have text ...

fyin1314
今天
9
0
jQuery选择器中的通配符 - Wildcards in jQuery selectors

问题: I'm trying to use a wildcard to get the id of all the elements whose id begin with "jander". 我正在尝试使用通配符来获取id以“jander”开头的所有元素的id。 I tried $('#jand......

法国红酒甜
今天
19
0
唐山5.1级地震 百度人工智能技术帮上忙:成都减灾所提前30秒让北京、天津市民收到预警

本文作者:y****n 7月12日6时38分在河北唐山市古冶区发生5.1级地震,成都高新减灾研究所与应急管理部门联合建成的大陆地震预警网成功预警该地震,给唐山市提前3秒预警,给天津市提前33秒预警...

百度开发者中心
昨天
30
0
如何使用PHP发送POST请求? - How do I send a POST request with PHP?

问题: Actually I want to read the contents that come after the search query, when it is done. 实际上,我想阅读搜索查询之后的内容,完成之后。 The problem is that the URL only a......

javail
今天
14
0
如何从Java读取文件夹中的所有文件? - How to read all files in a folder from Java?

问题: 如何通过Java读取文件夹中的所有文件? 解决方案: 参考一: https://stackoom.com/question/7jt2/如何从Java读取文件夹中的所有文件 参考二: https://oldbug.net/q/7jt2/How-to-rea...

富含淀粉
今天
25
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部