精华内容
下载资源
问答
  • 一文看懂 MySQL事务隔离级别与锁

    千次阅读 多人点赞 2018-04-10 20:32:55
    数据库 共享(Shared lock) 例1: ---------------------------------------- T1: select * from table (请想象它需要执行1个小时之久,后面的sql语句请都这么想象) T2: update table set column1='hello' ...

    数据库锁

    共享锁(Shared lock)

    例1:

    ----------------------------------------

    T1: select * from table (请想象它需要执行1个小时之久,后面的sql语句请都这么想象)

    T2: update table set column1='hello'

     

    过程:T1运行 (加共享锁)

    T2运行等待T1运行完之后再运行T2

     

    之所以要等,是因为T2在执行update前,试图对table表加一个排他锁,而数据库规定同一资源上不能同时共存共享锁和排他锁。所以T2必须等T1执行完,释放了共享锁,才能加上排他锁,然后才能开始执行update语句。

     

    例2:

    ----------------------------------------

    T1: select * from table

    T2: select * from table

     

    这里T2不用等待T1执行完,而是可以马上执行。

     

    分析:

     

    T1运行,则table被加锁,比如叫lockA

    T2运行,再对table加一个共享锁,比如叫lockB。

     

    两个锁是可以同时存在于同一资源上的(比如同一个表上)。这被称为共享锁与共享锁兼容。这意味着共享锁不阻止其它session同时读资源,但阻止其它session update

     

    例3:

    ----------------------------------------

    T1: select * from table

    T2: select * from table

    T3: update table set column1='hello'

     

    这次,T2不用等T1运行完就能运行,T3却要等T1和T2都运行完才能运行。因为T3必须等T1和T2的共享锁全部释放才能进行加排他锁然后执行update操作。

     

    例4:(死锁的发生)

    ----------------------------------------

     

    T1:begin tran

    select * from table (holdlock) (holdlock意思是加共享锁,直到事务结束才释放)

    update table set column1='hello'

     

    T2:begin tran

    select * from table(holdlock)

    update table set column1='world'

     

    假设T1和T2同时达到select,T1对table加共享锁,T2也对加共享锁,当T1的select执行完,准备执行update时,根据锁机制,T1的共享锁需要升级到排他锁才能执行接下来的update.在升级排他锁前,必须等table上的其它共享锁释放,但因为holdlock这样的共享锁只有等事务结束后才释放,所以因为T2的共享锁不释放而导致T1等(等T2释放共享锁,自己好升级成排他锁),同理,也因为T1的共享锁不释放而导致T2等。死锁产生了。

     

    例5:

    ----------------------------------------

    T1:begin tran

    update table set column1='hello' where id=10

     

    T2:begin tran

    update table set column1='world' where id=20

     

    这种语句虽然最为常见,很多人觉得它有机会产生死锁,但实际上要看情况,如果id是主键上面有索引,那么T1会一下子找到该条记录(id=10的记录),然后对该条记录加排他锁,T2,同样,一下子通过索引定位到记录,然后对id=20的记录加排他锁,这样T1和T2各更新各的,互不影响。T2也不需要等。

     

    但如果id是普通的一列,没有索引。那么当T1对id=10这一行加排他锁后,T2为了找到id=20,需要对全表扫描,那么就会预先对表加上共享锁或更新锁或排他锁(依赖于数据库执行策略和方式,比如第一次执行和第二次执行数据库执行策略就会不同)。但因为T1已经为一条记录加了排他锁,导致T2的全表扫描进行不下去,就导致T2等待。

     

    死锁怎么解决呢?一种办法是,如下:

    例6:

    ---------------------------------------

    T1:begin tran

    select * from table(xlock) (xlock意思是直接对表加排他锁)

    update table set column1='hello'

     

    T2:begin tran

    select * from table(xlock)

    update table set column1='world'

     

    这样,当T1的select 执行时,直接对表加上了排他锁,T2在执行select时,就需要等T1事务完全执行完才能执行。排除了死锁发生。但当第三个user过来想执行一个查询语句时,也因为排他锁的存在而不得不等待,第四个、第五个user也会因此而等待。在大并发情况下,让大家等待显得性能就太友好了,所以,这里引入了更新锁。

     

    更新锁(Update lock)

    为解决死锁,引入更新锁

     

    例7:

    ----------------------------------------

    T1:begin tran

    select * from table(updlock) (加更新锁)

    update table set column1='hello'

     

    T2:begin tran

    select * from table(updlock)

    update table set column1='world'

     

    更新锁的意思是:“我现在只想读,你们别人也可以读,但我将来可能会做更新操作,我已经获取了从共享锁(用来读)到排他锁(用来更新)的资格”。一个事务只能有一个更新锁获此资格。

     

    T1执行select,加更新锁。

    T2运行,准备加更新锁,但发现已经有一个更新锁在那儿了,只好等。(T2加的是更新锁,更新锁与更新锁不兼容, 如果加的是共享锁, 共享锁和更新锁可以兼容,即T1,T2不可同时进行,但是T3,T4,T5只要不是事务,还是可以正常查询)

     

    当后来有user3、user4...需要查询table表中的数据时,并不会因为T1的select在执行就被阻塞,照样能查询,相比起例6,这提高了效率

     

    例8:

    ----------------------------------------

    T1:begin

    select * from table(updlock) (加更新锁)

    update table set column1='hello' (重点:这里T1做update时,不需要等T2释放什么,而是直接把更新锁升级为排他锁,然后执行update)

     

    T2:begin

    select * from table (T1加的更新锁不影响T2读取)

    update table set column1='world' (T2的update需要等T1的update做完才能执行)

     

    我们以这个例子来加深更新锁的理解,

     

    第一种情况:T1先达,T2紧接到达;在这种情况中,T1先对表加更新锁,T2对表加共享锁,假设T2的select先执行完,准备执行update,发现已有更新锁存在,T2等。T1执行这时才执行完select,准备执行update,更新锁升级为排他锁,然后执行update,执行完成,事务结束,释放锁,T2才轮到执行update。

     

    第二种情况:T2先达,T1紧接达;在这种情况,T2先对表加共享锁,T1达后,T1对表加更新锁,假设T2 select先结束,准备update,发现已有更新锁,则等待,后面步骤就跟第一种情况一样了。这个例子是说明:排他锁与更新锁是不兼容的,它们不能同时加在同一子资源上。

     

    排他锁(独占锁,Exclusive Locks)

    这个简单,即其它事务既不能读,又不能改排他锁锁定的资源。

    例9

    T1: update table set column1='hello' where id<1000

    T2: update table set column1='world' where id>1000

     

    假设T1先达,T2随后至,这个过程中T1会对id<1000的记录施加排他锁.但不会阻塞T2的update。

     

    例10 (假设id都是自增长且连续的)

    T1: update table set column1='hello' where id<1000

    T2: update table set column1='world' where id>900

     

    如同例9,T1先达,T2立刻也到,T1加的排他锁会阻塞T2的update.

     

    意向锁(Intent Locks)

    意向锁就是说在屋(比如代表一个表)门口设置一个标识,说明屋子里有人(比如代表某些记录)被锁住了。另一个人想知道屋子里是否有人被锁,不用进屋子里一个一个的去查,直接看门口标识就行了。

     

    当一个表中的某一行被加上排他锁后,该表就不能再被加表锁。数据库程序如何知道该表不能被加表锁?一种方式是逐条的判断该表的每一条记录是否已经有排他锁,另一种方式是直接在表这一层级检查表本身是否有意向锁,不需要逐条判断。显然后者效率高。

     

    例11:

    ----------------------------------------

    T1: begin tran

    select * from table (xlock) where id=10 --意思是对id=10这一行强加排他锁

    T2: begin tran

    select * from table (tablock) --意思是要加表级锁  

     

    假设T1先执行,T2后执行,T2执行时,欲加表锁,为判断是否可以加表锁,数据库系统要逐条判断table表每行记录是否已有排他锁,如果发现其中一行已经有排他锁了,就不允许再加表锁了。只是这样逐条判断效率太低了。

     

    实际上,数据库系统不是这样工作的。当T1的select执行时,系统对表table的id=10的这一行加了排他锁,还同时悄悄的对整个表加了意向排他锁(IX),当T2执行表锁时,只需要看到这个表已经有意向排他锁存在,就直接等待,而不需要逐条检查资源了。

     

     

    计划锁(Schema Locks)

    例12:

    ----------------------------------------

    alter table .... (加schema locks,称之为Schema modification (Sch-M) locks

     

    DDL语句都会加Sch-M锁,该锁不允许任何其它session连接该表。连都连不了这个表了,当然更不用说想对该表执行什么sql语句了。

     

    事务隔离级别(MySQL仅InnoDB引擎支持事务,所以也只有InnoDB有事务隔离级别)

    Read Uncommit (未提交读。允许脏读)

    事例:老板要给程序员发工资,程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字,按成3.9万/月,该钱已经打到程序员的户口,但是事务还没有提交,就在这时,程序员去查看自己这个月的工资,发现比往常多了3千元,以为涨工资了非常高兴。但是老板及时发现了不对,马上回滚差点就提交了的事务,将数字改成3.6万再提交。

    分析:实际程序员这个月的工资还是3.6万,但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读

     

    一级封锁协议:

    一级封锁协议,事务在对需要修改的数据上面(就是在发生修改的瞬间) 对其加共享锁(其他事务不能更改,但是可以读取-导致“脏读”),直到事务结束才释放。

     

    Read committed(读提交,顾名思义,就是一个事务要等另一个事务提交后才能读取数据。)

    事例:程序员拿着信用卡去享受生活(卡里当然是只有3.6万),当他埋单时(程序员事务开启),收费系统事先检测到他的卡里有3.6万,就在这个时候!!程序员的妻子要把钱全部转出充当家用,并提交。当收费系统准备扣款时,再检测卡里的金额,发现已经没钱了(第二次检测金额当然要等待妻子转出金额事务提交完)。程序员就会很郁闷,明明卡里是有钱的…

    分析:这就是读提交,若有事务对数据进行更新(UPDATE)操作时,读操作事务要等待这个更新操作事务提交后才能读取数据,可以解决脏读问题。但在这个事例中,出现了一个事务范围内两个相同的查询却返回了不同数据,这就是不可重复读

     

    二级封锁协议:

    1)事务 在对需要更新的数据 上(就是发生更新的瞬间) 加 排他锁 (直到事务结束) , 防止其他事务读取未提交的数据,这样,也就避免了 “脏读” 的情况。2)事务 对当前被读取的数据 上面加共享锁(当读到时加上共享锁),一旦读完该行,立即 释放该 该行的共享锁

         二级封锁协议除防止了“脏读”数据,但是不能避免 丢失更新,不可重复读,幻读 。

         但在二级封锁协议中,由于读完数据后立即 释放共享锁,所以它不能避免可重复读 ,同时它也不能避免 丢失更新 ,如果事务A、B同时获取资源X,然后事务A先发起更新记录X,那么 事务B 将等待事务 A 执行完成,然后获得记录X 的排他锁,进行更改。这样事务 A 的更新将会被丢失。

    具体情况如下:

     

    事务A

    事务B

    读取X=100(同时上共享锁)

    读取X=100(同时上共享锁)

    读取成功(释放共享锁)

    读取成功(释放共享锁)

    UPDATE X=X+100 (上排他锁)

     

     

    UPDATING A(等待事务A释放对X的排他锁)

    事务成功(释放排他锁)X=200

     

     

    UPDATE X=X+200(成功上排他锁)

     

    事务成功(释放排他锁)X=300

     

    由此可以看到,事务A的提交被事务B覆盖了

     

    Repeatable read(重复读,就是在开始读取数据(事务开启)时,不再允许修改操作)

    事例:程序员拿着信用卡去享受生活(卡里当然是只有3.6万),当他埋单时(事务开启,不允许其他事务的UPDATE修改操作),收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。

    分析:重复读可以解决不可重复读问题。写到这里,应该明白的一点就是,不可重复读对应的是修改,即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作,而不是UPDATE操作

     

    什么时候会出现幻读?

    事例:程序员某一天去消费,花了2千元,然后他的妻子去查看他今天的消费记录(全表扫描FTS,妻子事务开启),看到确实是花了2千元,就在这个时候,程序员花了1万买了一部电脑,即新增INSERT了一条消费记录,并提交。当妻子打印程序员的消费记录清单时(妻子事务提交),发现花了1.2万元,似乎出现了幻觉,这就是幻读。

     

    三级封锁协议:

    三级封锁协议是:二级封锁协议加上事务 在读取数据的瞬间 必须先对其加 共享锁 ,但是 直到事务结束才释放 ,这样保证了可重复读(既是其他的事务职能读取该数据,但是不能更新该数据)。

    三级封锁协议除防止了“脏”数据 和不可重复读 。但是这种情况不能避免 幻读 和 丢失更新 的情况,在事务 A 没有完成之前,事务 B 可以新增数据,那么 当事务 A 再次读取的时候,事务B 新增的数据会被读取到

     

    进阶:repeatable read 导致死锁的情况,参考上面讲解共享锁的文章

     

    Serializable 序列化

    Serializable 是最高的事务隔离级别,在该级别下,事务串行化顺序执行,可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下,比较耗数据库性能,一般不使用。

     

    四级封锁协议:

    四级封锁协议是对三级封锁协议的增强,其实现机制也最为简单,直接对 事务中 所 读取 或者 更改的数据所在的表加表锁,也就是说,其他事务不能 读写 该表中的任何数据。这样所有的 脏读,不可重复读,幻读 ,都得以避免!

     

    值得一提的是:大多数数据库默认的事务隔离级别是Read committed,比如Sql Server , Oracle。Mysql的默认隔离级别是Repeatable read。

     

    乐观锁( Optimistic Locking ) 相对悲观锁而言,乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现,以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销,特别是对长事务而言,这样的开销往往无法承受。而乐观锁机制在一定程度上解决了这个问题。乐观锁,大多是基于数据版本( Version )记录机制实现。何谓数据版本?即为数据增加一个版本标识,在基于数据库表的版本解决方案中,一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时,将此版本号一同读出,之后更新时,对此版本号加一。此时,将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对,如果提交的数据版本号大于数据库表当前版本号,则予以更新,否则认为是过期数据。

    悲观锁(Pessimistic Lock),正如其名,具有强烈的独占和排他特性。它指的是对数据被外界(包括本系统当前的其他事务,以及来自外部系统的事务处理)修改持保守态度,因此,在整个数据处理过程中,将数据处于锁定状态。悲观锁的实现,往往依靠数据库提供的锁机制(也只有数据库层提供的锁机制才能真正保证数据访问的排他性,否则,即使在本系统中实现了加锁机制,也无法保证外部系统不会修改数据)。

    事务的特性: 

    1.原子性  事务是数据库逻辑的工作单元,事务包括的所有操作,要么都做,要么都不做。 

    2.一致性  事务执行的结果是使数据库从一个一致性状态变成另一个一致性状态。一致性与原子性是密切相关的。 

    3.隔离性  一个事务的执行不能被其他事务干扰。 

    4.持久性  一个事务一旦提交,它对数据库中数据的改变应该是永久性的。

     

    MyISAM 和 InnoDB 区别:

    1、MyISAM类型的表强调的是性能,但是不支持事务、及外部键等高级功能。

    1. MySQL默认采用的是MyISAM。
    2. MyISAM不支持事务,所以MyISAM就不存在事务隔离级别了,而InnoDB支持,所有有4种事务隔离级别,InnoDB的AUTOCOMMIT默认是打开的,即每条SQL语句会默认被封装成一个事务,自动提交,这样会影响速度,所以最好是把多条SQL语句显示放在begin和commit之间,组成一个事务去提交。
    3. InnoDB支持数据行锁定,MyISAM不支持行锁定,只支持锁定整个表。即MyISAM同一个表上的读锁和写锁是互斥的,MyISAM并发读写时如果等待队列中既有读请求又有写请求,默认写请求的优先级高,即使读请求先到,所以MyISAM不适合于有大量查询和修改并存的情况,那样查询进程会长时间阻塞。因为MyISAM是锁表,所以某项读操作比较耗时会使其他写进程饿死。
    4. InnoDB支持外键,MyISAM不支持。
    5. InnoDB的主键范围更大,最大是MyISAM的2倍。
    6. InnoDB不支持全文索引,而MyISAM支持。全文索引是指对char、varchar和text中的每个词(停用词除外)建立倒排序索引。MyISAM的全文索引其实没啥用,因为它不支持中文分词,必须由使用者分词后加入空格再写到数据表里,而且少于4个汉字的词会和停用词一样被忽略掉。
    7. MyISAM支持GIS数据,InnoDB不支持。即MyISAM支持以下空间数据对象:Point,Line,Polygon,Surface等。
    8. 没有where的count(*)使用MyISAM要比InnoDB快得多。因为MyISAM内置了一个计数器,count(*)时它直接从计数器中读,而InnoDB必须扫描全表。所以在InnoDB上执行count(*)时一般要伴随where,且where中要包含主键以外的索引列。为什么这里特别强调“主键以外”?因为InnoDB中primary index是和raw data存放在一起的,而secondary index则是单独存放,然后有个指针指向primary key。所以只是count(*)的话使用secondary index扫描更快,而primary key则主要在扫描索引同时要返回raw data时的作用较大。
    9. MyISAM和InnoDB锁的不同: https://blog.csdn.net/zhanghongzheng3213/article/details/51753189

    2、并发

    MyISAM读写互相阻塞:不仅会在写入的时候阻塞读取,MyISAM还会在读取的时候阻塞写入,但读本身并不会阻塞另外的读

    InnoDB 读写阻塞与事务隔离级别相关

    3、场景选择

    MyISAM

    • 不需要事务支持(不支持)
    • 并发相对较低(锁定机制问题)
    • 数据修改相对较少(阻塞问题),以读为主
    • 数据一致性要求不是非常高
    1. 尽量索引(缓存机制)
    2. 调整读写优先级,根据实际需求确保重要操作更优先
    3. 启用延迟插入改善大批量写入性能
    4. 尽量顺序操作让insert数据都写入到尾部,减少阻塞
    5. 分解大的操作,降低单个操作的阻塞时间
    6. 降低并发数,某些高并发场景通过应用来进行排队机制
    7. 对于相对静态的数据,充分利用Query Cache可以极大的提高访问效率
    8. MyISAM的Count只有在全表扫描的时候特别高效,带有其他条件的count都需要进行实际的数据访问

    InnoDB 

    • 需要事务支持(具有较好的事务特性)
    • 行级锁定对高并发有很好的适应能力,但需要确保查询是通过索引完成
    • 数据更新较为频繁的场景
    • 数据一致性要求较高
    • 硬件设备内存较大,可以利用InnoDB较好的缓存能力来提高内存利用率,尽可能减少磁盘 IO
    1. 主键尽可能小,避免给Secondary index带来过大的空间负担
    2. 避免全表扫描,因为会使用表锁
    3. 尽可能缓存所有的索引和数据,提高响应速度
    4. 在大批量小插入的时候,尽量自己控制事务而不要使用autocommit自动提交
    5. 合理设置innodb_flush_log_at_trx_commit参数值,不要过度追求安全性
    6. 避免主键更新,因为这会带来大量的数据移动

    4、其它细节

    1)InnoDB 中不保存表的具体行数,注意的是,当count(*)语句包含 where条件时,两种表的操作是一样的

    2)对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中,可以和其他字段一起建立联合索引, 如果你为一个表指定AUTO_INCREMENT列,在数据词典里的InnoDB表句柄包含一个名为自动增长计数器的计数器,它被用在为该列赋新值。自动增长计数器仅被存储在主内存中,而不是存在磁盘

    3)DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除

    4)LOAD TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用

    5)如果执行大量的SELECT,MyISAM是更好的选择,如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑,应该使用InnoDB表

    5、为什么MyISAM会比Innodb 的查询速度快

    InnoDB 在做SELECT的时候,要维护的东西比MYISAM引擎多很多;

    1)InnoDB 要缓存数据和索引,MyISAM只缓存索引块,这中间还有换进换出的减少

    2)innodb寻址要映射到块,再到行,MyISAM记录的直接是文件的OFFSET,定位比INNODB要快

    3)InnoDB 还需要维护MVCC一致;虽然你的场景没有,但他还是需要去检查和维护

    MVCC ( Multi-Version Concurrency Control )多版本并发控制

    InnoDB :通过为每一行记录添加两个额外的隐藏的值来实现MVCC,这两个值一个记录这行数据何时被创建,另外一个记录这行数据何时过期(或者被删除)。但是InnoDB并不存储这些事件发生时的实际时间,相反它只存储这些事件发生时的系统版本号。这是一个随着事务的创建而不断增长的数字。每个事务在事务开始时会记录它自己的系统版本号。每个查询必须去检查每行数据的版本号与事务的版本号是否相同。让我们来看看当隔离级别是REPEATABLE READ时这种策略是如何应用到特定的操作的

    SELECT InnoDB必须每行数据来保证它符合两个条件

    1、InnoDB必须找到一个行的版本,它至少要和事务的版本一样老(也即它的版本号不大于事务的版本号)。这保证了不管是事务开始之前,或者事务创建时,或者修改了这行数据的时候,这行数据是存在的。

    2、这行数据的删除版本必须是未定义的或者比事务版本要大。这可以保证在事务开始之前这行数据没有被删除。

    展开全文
  • Mysql事务隔离级别与锁

    千次阅读 2016-05-29 12:49:31
    数据库提供了几种隔离级别来供选择,本文通过解析InnoDB的加锁机制是如何实现几种隔离级别,来更深刻的理解mysql。  两阶段  首先,事务的所操作分为两个阶段:加锁和解锁,两者不想交。因为事务开始...
        数据库的事务有几种特性,例如一致性和隔离性,一般通过加锁来实现。同时数据库又是一个高并发的应用,如果加锁过度或者不当将严重影响性能。数据库提供了几种隔离级别来供选择,本文通过解析InnoDB的加锁机制是如何实现几种隔离级别,来更深刻的理解mysql的锁。

        两阶段锁
        首先,事务的所操作分为两个阶段:加锁和解锁,两者不想交。因为事务开始时,并不知道会用到哪些数据,所以加锁阶段随着事务的执行,可能一直在执行。事务结束时,一起将锁释放。注意: 不相交!这是两阶段锁的原则,但是有时为了效率也会违反,后面再详述。这种方法由于加锁不是一次获取全部的锁,可能出现死锁,但是事务的并发调度是串行化的。

        四种隔离级别:
    • Read Uncommit:未提交读。允许脏读。
    • Read Commit : 提交读。只能读到其他事务已提交的内容。允许不重复读。
    • Repeated Read : 可重复读。同一个事务内的查询与事务开始时是一致的。允许幻读。Mysql默认的级别。
    • Serializable : 串行化的读。每次读都要获取表级锁,读写互相阻塞。
        Read Uncommit一般不会使用到,并且没有加锁,所以不讨论。
        Serializable也比较简单粗暴,串行化的读写就不会有问题,但是效率低下,称为悲观锁。相对于悲观锁,乐观锁在一定程度上环节了效率的问题。乐观锁大多是基于八本纪录机制实现的,在mysql中为MVCC(多版本并发控制)。我们主要来谈MVCC和RC、RR。

        InnoDB总为每一行后面加入了两个隐藏的列,来实现MVCC。这两个列分别纪录了数据最后一次被哪个事务创建、更新的事务号;该事物是否被删除,被删除的事务的事务号。事务号是递增的。虽然这格外增加的存储空间,每一行都要存储额外的历史版本,而且还要定期删除。但是多版本的实现,为大多数的读惭怍提供了方便:读数据只需要根据事务号来读取某一历史版本,不用再担心并发读写的问题而加锁,效率高,并且可以实现隔离级别中要求的只读取符合条件的值。我们称这种读叫快照读,相反如果读取当前的最新数据叫当前读。mysql在RC/RR下的普通select都为快照读,不用加锁。
        在RR级别下,各种操作在MVCC下会有怎么样的效果呢?
    • select时,读取版本号<=当前事务号并且删除版本号为空或>当前事务号的行。
    • insert,保存事务号为当前事务号。
    • delete,保存当前事务号为删除版本号。
    • update,创建新的一行,保存事务号为当前事务号。

        快照读/当前读
        说完MVCC,我们回到隔离级别。上面说的隔离级别的介绍中,关于RR不能解决幻读的问题,是耳熟能详的,到处都是这么写的。但是实际呢?经过实验,session A select * from test where age = 12; 然后Session B插入一个age=12的行,Session A再次select,并没有返回新插入的行。由此可见Mysql中幻读的读问题已经解决了。原理就是上文提到的快照读。但是,当前读的冲突问题呢?
    • 快照读:
      • select * from test where ... ;
    • 当前读:
      • select * from test where ... in share mode ;
      • select * from test where ... for update ;
      • insert
      • update
      • delete
        事务的隔离级别实际上是定义了当前读的级别。为了减少锁竞争,引入了快照读,使得普通的select不同加锁。其他操纵还是需要加锁的。
        为了解决当前读的幻读问题,Mysql使用了Next-key锁。就是GAP(间隙锁)和行锁的合并。行锁可以避免并发修改同一行带来的问题,但是插入操作呢?间隙锁GAP就是为了解决这个问题。
        在RC级别中,表test,age为主键:
    1. A:select * from test where age = 10;  返回一条记录
    2. A:update test set name = ‘a’ where age = 10;
    3. B:insert into test values(10,‘b’);
    4. B:commit;
    5. A:select * from test where age = 10;  返回两条记录
    6. A:commit ;
        在这个例子中,步骤二虽然进行了update,对age=10的记录加了行锁。但是session B插入了新纪录,并提交。A就可以读到。
        在RR级别中,重新进行这个实验,A的第二次读,仍然返回一条记录。因为在步骤二中,不止对age=10的行加了行锁,还有间隙锁。session B的插入将被阻塞,等待获取锁,A提交后才能被执行。
        
        InnoDB行锁
        上面说到,InnoDB当前读,会对行加锁,防止并发问题。这里有个前提:where条件是索引,可以通过索引来过滤行来对指定行加锁。如果不是索引,InnoDB会对所有行加锁,但是为了提供并发效率。等存储系统返回数据后,会过滤后再对不符合条件的行释放锁。还记得本文开头介绍两阶段锁时,说过有时为了效率会违反这个原则么。就是现在所说的这种情况。先获取全部锁,再释放掉多余的锁。

        特殊情况
        这里举一个例子:
    1. A:select * from test where age = 10;  返回一条记录
    2. B:insert into test values(10,‘b’);
    3. B:commit;
    4. A:update test set name = ‘qwe’where age = 10;
    5. A:select * from test where age = 10;  返回两条记录
        看到这个结果,有人不禁起了疑问:不是说InnoDB在RR级别解决了幻读问题么?怎么同一个事务中两次读读到了不同的行数。
        这里A的前后两次读,均为快照读,而且是在同一个事务中。但是B先插入直接提交,此时A再update,update属于当前读,所以可以作用于新插入的行,并且将改行的当前版本号设为A的事务号,所以第二次的快照读,是可以读取到的,因为同事务号。这种情况符合MVCC的规则,如果要称为一种幻读也非不可,算为一个特殊情况来看待吧。
    展开全文
  •  我们都知道事务的几种性质,数据库为了维护这些性质,尤其是一致性和隔离性,一般使用加锁这种方式。同时数据库又是个高并发的应用,同一时间会有大量的并发访问,如果加锁过度,会极大的降低并发处理能力。所以...
  • 查看事务隔离级别MySQL 中,可以通过show variables like ‘%tx_isolation%’或select @@tx_isolation;语句来查看当前事务隔离级别。 查看当前事务隔离级别的 SQL 语句和运行结果如下: mysql> show variables ...
  • 作者:伞U ...好久没碰数据库了,只是想起自己当时在搞数据库的...为了说明问题,我们打开两个控制台分别进行登录来模拟两个用户(暂且成为用户 A 和用户 B 吧),并设置当前 MySQL 会话的事务隔离级别。 一. read unco
  • MySQL事务隔离级别详解,这个面试经常会面到,必会呀,哈哈
  • 详解mysql事物隔离级别与锁机制

    千次阅读 多人点赞 2019-01-21 17:34:34
    事物隔离级别 首先看下数据库事物四大特性,...mysql具有四种事物隔离级别,隔离力度依次递增,高度隔离会限制可并行执行的事务数,所以一些应用程序降低隔离级别以换取更大的吞吐量。不同业务场景下使用不同的数据...

    事物隔离级别

    首先看下数据库事物四大特性,ACID,原子性,一致性,隔离性,持久性。

    隔离性:由并发事务所作的修改必须与任何其它并发事务所作的修改隔离,互相不受影响。同一时间,只允许一个事务请求同一数据,不同的事务之间彼此没有任何干扰。mysql具有四种事物隔离级别,隔离力度依次递增,高度隔离会限制可并行执行的事务数,所以一些应用程序降低隔离级别以换取更大的吞吐量。不同业务场景下使用不同的数据库事物隔离性,部分关键业务采用隔离性高的隔离级别,以保证数据正确性。

    msyql四种事物隔离级别:

    1. Read Uncommitted(读未提交)事物能读到不同事物没有提交(未commit)的数据结果,实际应用比较少,会产生脏读,事物已经读到其他事物未提交的数据,但数据被回滚,称为脏读。
    2. Read Committed(读已提交)事物读取其他事物已经提交的数据,读取到的是最新的数据,所以会出现在同一事物中select读取到的数据前后不一致,会出现不可重复读问题,不可重复读问题就是我们在同一个事务中执行完全相同的select语句时可能看到不一样的结果。
    3. Repeatable Read(可重复读)mysql默认事物隔离级别,在同一事物中多次读取同样的数据结果是一样的,解决了不可重复读的问题,此级别会出现幻读的问题,幻读:当用户读取某一范围的数据行时,另一个事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的“幻影” 行。
    4. Serializable(串行化)最高的事物隔离级别,串行化强制事物排序阻塞,避免事物冲突,解决了上述所有的问题,它使用了共享锁,执行效率低下,会导致大量的超时和锁切换竞争现象,实际开发应用很少。

    数据库事务并发可能出现的问题:

    脏读:在隔离级别读未提交中可能会出现,一个事务读取另外一个事务还没有提交的数据叫脏读,事物A读取了事物B更新的事物,事物B没有commit并且回滚,此时就事物A产生脏读,应用也没保证数据的正确性。

    • 新建表,就以财务转账为例子来演示出现的问题
    create table account(id int(11) primary key auto_increment,name varchar(16),money float);
    •  设定mysql事物隔离级别

    mysql默认的事物隔离级别为可重复读(Repeatable Read),可使用SELECT @@tx_isolation查看。

    查看mysql默认事物隔离级别

     

    设定事物隔离级别,mysql存储引擎InnoDB支持事物,MyISAM不支持事物,当前mysql默认存储引擎是InnoDB。

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

    这里,我们开启两个mysql的session会话,把隔离级别都设定为读未提交: 

    设定隔离级别为读未提交

     

    • 脏读

    向表account中插入一条记录,zhangsan金额为1000

    记录

     开启一个事物A,在该事物中把zhangsan的金额扣除300,暂时不提交和回滚:

    begin;
    update account set money=money-300 where id=1;
    事物A

     

    另开一个事物B,在事物B中查看zhangsan当前金额数:

    begin;
    select * from account where id=1;
    事物B

     此时在事物B中读取到的数据就为脏数据,事物B读取到了事物A未提交(commit)的数据,当事物A回滚(rollback)时,事物B之前读取到的数据会给应用带来数据错误。

     

    不可重复读:在隔离级别读已提交中可能会出现的问题,事物能读取其他事物对同一数据的更改。事务 A 多次读取同一数据,事务 B在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果不一致。

    •  设定mysql事物隔离级别为读已提交
    set session transaction isolation level read committed;
    •  不可重复读

    在A中开启一个事物A,第一次读取到zhangsan的金额数为1000:

    begin;
    select * from account where id=1;
    事物A

    然后另外开启一个事物B,添加金额200,并且提交事物:

    begin;
    update account set money=money+200 where id=1;
    commit;

     

    事物B

    在事物B提交之后再在事物A中读取zhangsan的金额数:

    事物A

     

    可以看到事物A读取到的数据与之前读取到的不一致,此时就产生了不可重复读的现象。

     

     

    幻读:在隔离级别可重复读中可能出现的问题,幻读是针对按范围读取多条数据的现象。同一事务A多次查询,若另一事务B只是update,则A事务多次查询结果相同;若B事务insert/delete数据,则A事务多次查询就会发现新增或缺少数据,出现幻读。

    • 设定mysql事物隔离级别为可重复读
    set session transaction isolation level repeatable read;
    • 幻读

    在事物A中查找表数据:

    事物A

    然后在B中开启新事物,插入一条数据,并提交事物:

    begin;
    insert into account value(5,'l5',500);
    commit;
    事物B

    接下来我们在事物A中查询,发现查询不到第5条记录,但是我们在A中插入id为5的记录时会出问题:

    事物A,插入数据

    此时就产生了幻读现象,在事物A中查询不到数据,却被告知数据已经存在,解决幻读一般需要锁表。

     

    综上,四种隔离级别分别会出现的问题,读未提交未解决任何问题,串行化避免了所有的问题,mysql默认可重复读:

    √表示存在问题

     

    MySql锁机制

    mysql默认存储引擎是InnoDB,InnoDB与Myisam相比,InnoDB支持事物,支持多种锁机制,有行锁和表锁,Myisam只支持表锁,且不支持事物。

    上述详细的介绍了mysql事物隔离级别,那么,mysql事物隔离是怎么实现的呢?底层原理是什么?

    锁是用来实现数据库事物隔离级别的重要机制,mysql提供了多种锁机制,有共享锁、排他锁、意向共享锁、意向排他锁等,其中共享锁和排他锁都是行锁,意向锁是表锁,意向锁系统控制,人为操作不了。

    数据库实现事务隔离的方式有两种:

    1. 一种是在读取数据前,对其加锁,阻止其他事务对数据进行修改,简称为LBCC
    2. 另一种是不用加任何锁,通过一定机制生成一个数据请求时间点的一致性数据快照(Snapshot),并用这个快照来提供一定级别(语句级或事务级)的一致性读取,简称MVCC。

    锁的概念

    在mysql中,锁分为表锁、行锁,行级锁有共享锁、排他锁。表锁有意向锁,意向共享锁和意向排他锁。

    1. 共享锁,又称为S锁、读锁,顾名思义,共享锁是多个事务对于同一数据可以共享一把锁,都能访问到数据,但是只能读不能修改。普通查询不会加任何锁,手动加共享锁使用select ... lock in share mode语句。
    2. 排他锁,又称为X锁、写锁,排他锁两个事物不能共存。如一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的其他锁,包括共享锁和排他锁,但是获取排他锁的事务是可以对数据就行读取和修改,所以排他锁会阻塞其他事物的读操作和写操作,直到释放排他锁。在 InnoDB中,update,delete,insert都会自动给涉及到的数据加上排他锁,select查询语句可使用select ...for update加排他锁,事物结束或者rollback/commit就会释放锁。
    3. 意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁,反之亦然。
    4. 意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁,反之亦然。意向共享锁和意向排他锁都是mysql自身管理,人为干预不了。

    在可重复读中,幻读是通过什么方式解决的?

    innoDB存储引擎的锁的算法有三种:

    1. Record lock:单个行记录上的锁
    2. Gap lock:gap锁,间隙锁,锁定一个范围,不包括记录本身
    3. Next-key lock:record+gap 锁定一个范围,包含记录本身

    Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内,而这会导致幻读问题的产生。原理是对行数据操作时,gap锁会对相邻范围的行数据加锁,阻塞,直到操作完成,针对的是非唯一性索引数据。如果是有索引的数据则会使用recored lock行锁,相对来说锁的范围较小,阻塞程度小。

    为什么要存在意向锁?

    innodb的意向锁主要用户多粒度的锁并存的情况。比如事务A要在一个表上加S锁,如果表中的一行已被事务B加了X锁,那么该锁的申请也应被阻塞。如果表中的数据很多,逐行检查锁标志的开销将很大,系统的性能将会受到影响。举个例子,如果表中记录1亿,事务A把其中有几条记录上了行锁了,这时事务B需要给这个表加表级锁,如果没有意向锁的话,那就要去表中查找这一亿条记录是否上锁了。如果存在意向锁,那么假如事务A在更新一条记录之前,先加意向锁,再加X锁,事务B先检查该表上是否存在意向锁,存在的意向锁是否与自己准备加的锁冲突,如果有冲突,则等待直到事务A释放,而无须逐条记录去检测。

    乐观锁、悲观锁

    锁从使用方式来分可分为乐观锁和悲观锁,乐观锁和悲观锁在很多应用当中都存在的概念,并不是实质存在的锁叫乐观锁悲观锁,在mysql数据中有,在hibernate、java等当中也有。

    乐观锁是在遇到事物并发问题时,想法很乐观,每次去拿数据的时候都认为别人不会修改,所以不会上锁,认为这次的操作不会导致冲突,当出现事物并发问题时再处理。乐观锁由于加锁少,所以性能开销比较小,吞吐量大。

    悲观锁的特点是先获取锁,再进行业务操作。每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁,开始就默认会出现事物并发问题,所以在进行每次操作数据时都要通过获取锁才能进行对相同数据的操作,悲观锁需要耗费较多的时间,处理并发问题也相对严谨,在核心业务的关键之处可使用悲观锁,开销相对来说大。


    经典高并发 ,如何避免库存超发问题

    平时在购买商品操作库存流程大概如下:

    基本购买流程

    假如当前库存量为5,第一个购买请求数量为4个,顺利购买,当前剩余1,第二次购买4个,此时库存不足,程序直接返回库存不足,购买失败。

    上述情况不是在高并发情况下,在高并发情况下,可能有多个请求同时购买,同时读取库存更新存库,两次请求同时读取到的库存都是5,然后都执行购买操作,减库存,就出现了库存超发现象,库存减至-3。

    类似问题可以用乐观锁、悲观锁来解决。

    1、悲观锁解决方案

    出现超发的问题根本原因就是共享的数据被多个线程同时修改,如果单独的一个线程想要修改数据,在读取数据时将数据锁定,加排他锁,不允许其他线程读取和修改数据,直到存库修改完成释放锁,就不会出现超发现象。

    //开始事物
    select id,productid,stock from zproduct where id=? for update;
    
    update zproduct set stock=stock-? where id=?;
    //结束事物

    使用for update给行数据加排他锁,其他事物就不能对它读和写,避免了数据同时被多个事物修改,此解决方案是实现比较简单,缺点是加排他锁影响系统的并发性能。

    乐观锁解决方案

    2、乐观锁解决方案

    悲观锁很容易产生线程阻塞,为了提高性能,出现了乐观锁方案,不使用数据库锁,不阻塞线程并发。

    实现方法:给商品添加一个version字段,代码行修改版本号,读取库存是拿到这个version版本号,在更新时再对比version版本号,如果版本号相同,说明库存没有被其他线程修改过,可以正常操作,同时把version数值加1。如果版本号不同,代表被别的线程修改过,则取消修改库存操作,返回购买失败。

    update zproduct set stock=stock-?,version=version+1 where id=? and version=?;

    为进一步完善,可以借鉴中间件的重试机制,更新失败后,重新来一遍,重新读取、修改,超过一定时间一定重试次数后,还不成功就放弃,返回失败,否则成功。

     

     

     

     

    展开全文
  • MySQL事务隔离级别与锁

    千次阅读 2017-08-23 16:42:01
    脏读:数据1;A事务修改2未提交,B事务查看2,A事务ROLLBACK,B事务看到不正确数据。 不可重复读:A事务查看,B事务修改提交,A事务再次查看,数据不一样。 reaptable red可重读:A事务查看,B事务不...四种隔离级别: 1
    脏读:数据1;A事务修改2未提交,B事务查看2,A事务ROLLBACK,B事务看到不正确数据。
    
    不可重复读:A事务查看,B事务修改提交,A事务再次查看,数据不一样。
    reaptable red可重读:A事务查看,B事务不能提交修改。
    幻读:A事务修改全部行,B事务插入新行,A事务查看新行没被修改
    serializable:事务B只能等到A提交才能插入新数据。


    四种隔离级别:
    1、read uncommited读未提交数据:所有的事务都可以“看到”未提交事务的执行结果;会导致脏读、不可重复读和幻读的问题的出现
    2、read commited读已提交数据:一个事务在开始时,只能"看见"已提交事务所做的改变;Oracle和sql server默认的级别,可以避免脏读,但不可重复读和幻读问题仍然会出现。
    3、Repeatable red可重读:确保同一事务的多个实例在并发读取数据时,会看到同样的行。
    事务在读取某数据的瞬间(就是开始读取的瞬间),必须先对其加 行级共享锁,直到事务结束才释放;
    事务在更新某数据的瞬间(就是发生更新的瞬间),必须先对其加 行级排他锁,直到事务结束才释放。
    4、Serializable可串行化:最高的事务隔离级别,每个读的数据上加上共享锁
    事务在读取数据时,必须先对其加 表级共享锁 ,直到事务结束才释放;
    事务在更新数据时,必须先对其加 表级排他锁 ,直到事务结束才释放。


    隔离级 脏读可能性 不可重复读可能性 幻读可能性 加锁读
    READ UNCOMMITED
    READ COMMITED
    REAPTABLE READ
    SERILIZABLE


    事务的隔离级别通过锁机制实现


    共享锁(S锁,读锁):若事务T对数据对象A添加S锁,则事务T允许读取A但不允许修改A,其他事务只能对A加X锁
    由读表操作加上的锁,加锁之后其他用户只能获取该表和行的共享锁,不能获取排它锁,也就是只能读不能写。


    排它锁(X锁,写锁):若事务T对数据对象A添加X锁,则只允许T读取和修改A,其他事务不能再对A添加任何类型的锁,直到T释放此锁。
    由写表操作加上的锁,加锁之后其他用户只能对表进行读操作,而不能进行写操作,直到事务结束才释放。


    四种隔离级别,开启事务,默认对表数据加上共享锁,其他事务只能对表进行读操作,而不能进行写操作,直到事务结束才会被释放。
    展开全文
  • 主要介绍了MySQL中Innodb的事务隔离级别的关系讲解教程,来自于美团技术团队的经验实际经验分享,需要的朋友可以参考下
  • 主要介绍了MySQL事务及Spring隔离级别实现原理详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
  • MySQL 四种事务隔离级别详解及对比 按照SQL:1992 事务隔离级别,InnoDB默认是可重复读的(REPEATABLE READ)。MySQL/InnoDB 提供SQL标准所描述的所有四个事务隔离级别。你可以在命令行用–transaction-isolation选项...
  • 深入理解Mysql事务隔离级别与锁机制

    千次阅读 2021-09-24 11:16:34
    这些问题的本质都是数据库的多事务并发问题,为了解决事务并发问题,数据库设计了事务隔离机制、机制、MVCC多版本并发控制隔离机制,用一整套机制来解决多事务并发问题。 事务及其ACID属性 原子性:操作的不可...
  • 主要介绍了Mysql事务隔离级别原理实例解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
  • 主要介绍了mysql的事务,隔离级别用法,结合实例形式分析了MySQL事务隔离级别相关原理、用法及操作注意事项,需要的朋友可以参考下
  • 36谈谈MySQL支持的事务隔离级别,以及悲观和乐观的原理和应用场景?
  • 数据库事务隔离级别 数据库事务的隔离级别有4个,由低到高依次为 Read uncommitted:允许脏读。 Read committed: 防止脏读,最常用的隔离级别,并且是大多数数据库的默认隔离级别。 Repeatable read:可以防止脏...
  • 之前在网上查询mysql事务隔离相关资料,一直只是脑子里有一个印象,久而久之还是会忘记,忘记后又要到网上查找相关资料,但是没实践过就对mysql事务隔离级别理解不是特别的深入,现在自己亲手实践体验一下这个这四个...
  • Mysql隔离级别与锁

    2020-12-14 21:42:43
    1.MySQL参数autocommit生产环境设1还是0?为什么?  在生产环境中,为了事务一致性,需要把autocommit 设置为 0  这样可以进行事务全部成功后,在后一次性提交,如果某一步出错,可以rollback。  在会话进修改...
  • 查询mysql事务隔离级别

    千次阅读 2019-10-11 16:37:59
    3、事务隔离级别越高,越能保证数据的完整性,但是对并发性能影响大,就比如隔离级别为串行化时,读取数据会住整张表。 4、不可重复读和幻读很相似,两者整体上都是两次读的数据不相同,但是不可重复读一般时更新...
  • MySQL事务隔离级别

    2019-03-21 01:54:28
    NULL 博文链接:https://cuishuangjia.iteye.com/blog/964885
  • 机制 NOLOCK和READPAST的区别。...NOLOCK表明没有对数据表添加共享以阻止其它事务对数据表数据的修改。 SELECT * FROM Customer 这条语句将一直死锁,直到排他解除或者超时为止。(注:设置超时
  • 这里以mysql为例,先明确以下几个问题: 一.一般项目如果不自己配置事务的话,一般默认的是autocommit,即执行完一个操作后自动commit,提交事务。 (注:事务是绑定在数据库操作上的,也就是当程序执行(statement....
  • MYSQL事务隔离与锁

    2021-06-22 02:45:51
    本套课程主要是对mysql事务隔离级别以及相关知识进行深入讲解其中包括:mysql四大特性的深入理解、四种隔离级别的实现原理、一致性读的实现原理、全局、表锁、行锁,间隙的介绍分析,对于难点和重点会使用...
  • 目录 1. 的概述 1.1 的定义 1.2 的分类 2. 三种(表锁、行锁、间隙) 2.1 表锁(偏向于读操作) ...2.1.2 案例分析(加读) ...2.1.3 案例分析(加写) ...2.2.1 事务的相关使用方法 ...
  • 查询:默认事务隔离级别 mysql> select @@tx_isolation;当前会话的默认事务隔离级别 mysql> select @@session.tx_isolation;当前会话的默认事务隔离级别 mysql> select @@global.tx_isolation;全局的事务隔离级别
  • 查看mysql 事务隔离级别 mysql> show variables like '%isolation%'; +---------------+----------------+ | Variable_name | Value | +---------------+----------------+ | tx_isolation | READ-COMMITTED | +---...
  • MySQL的四种事务隔离级别 事务的四大特性ACID 1.原子性(Atomicity):一个事务要么全部执行,要么全部不执行,如果执行过程中出现异常则回滚;犹如化学中的原子一样具有不可分割性(别跟我较真啊,虽然原子又可以...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 90,743
精华内容 36,297
关键字:

mysql事务隔离级别与锁

mysql 订阅