文档章节

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

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

锁机制:

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

排它锁:由写表操作加上的锁,加锁后其他用户不能获取该表或行的任何锁,典型是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
丰台
程序员
mysql事务和锁 SELECT FOR UPDATE

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

SibylY
2016/08/22
172
0
Mysql InnoDB锁机制

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

皮蛋和
2017/12/31
0
0
mysql 开发进阶篇系列 6 锁问题

一.概述   在数据库中,数据是属于共享资源,为了保证并发访问的一致性,有效性,产生了锁。接下来重点讨论mysql锁机制的特点,常见的锁问题,以及解决mysql锁问题的一些方法或建议。 相比...

花阴偷移
07/23
0
0
数据库的锁机制

在MySQL事务隔离中已经提到了事务隔离级别和脏读、不可重复读、幻读产生的原因。为了应对这些问题,主流数据库都提供了锁机制,并引入了事务隔离级别的概念。 并发控制 在计算机科学,特别是...

Hosee
2016/06/01
189
0
脏读、不可重复读 共享锁、悲观锁 和 事务五种隔离级别

一、脏读、不可重复读、幻读 1、脏读:脏读就是指当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这个数据。...

嘻哈开发者
2015/12/25
192
0

没有更多内容

加载失败,请刷新页面

加载更多

laravel 微信支付

1.composer加载laravel微信支付第三方文件 composer require "overtrue/laravel-wechat:~4.0" composer require simplesoftwareio/simple-qrcode 1.3.* //composer生成二维码文件 2.改confi......

vio小黑
35分钟前
1
0
学习设计模式——抽象工厂模式

1. 认识抽象工厂模式 1. 定义:提供一个创建一系列相关或互相依赖的对象的接口,而无需指定它们具体的类。 2. 组成结构: AbstractFactory:抽象工厂类,定义创建一系列对象的操作接口 Fact...

江左煤郎
36分钟前
2
0
ES6的let块级作用域和变量不可提升导致一个比较容易出现的错误

今天在写NodeJS代码的时候出现一个变量一直提示未定义,简化后的代码如下: let param = 1;{ console.log(param);} 就在想,不至于啊。不是继承上层的声明吗? 继续看下去,发现原来...

MKjy
42分钟前
2
0
50:nginx访问日记|日记切割|静态文件不记录日记和过期时间

1、nginx访问日记: 日记格式:在主配置文件nginx.conf里搜索log_format; [root@localhost_001 conf]# vim nginx.conflog_format combined_realip '$remote_addr $http_x_forwarded_for ......

芬野de博客
46分钟前
1
0
前后端正常交互的流程

1、评审阶段:产品召集前后端进行需求评审,前后端各自捋清楚自己的业务量以及联调之间工作量,从而进行开发时间评估。 2、开发准备阶段:前后端一起商量需求中需要联调的部分,进行接口的口...

Jack088
46分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部