`
loamy
  • 浏览: 325273 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

Consistent Non-Locking Reads 与Locking Reads的区别

阅读更多

一直以来,都认为mysql 在普通的select下会根据主键或索引锁定某些行,如:select *** where id* ,但是这两天发现,并不是这样的,
mysql的read分为Consistent Non-Locking Reads(普通的select) 跟 Locking Reads(FOR UPDATE or LOCK IN SHARE MODE),
在Consistent Non-Locking Reads中他是不锁定的,现在来分别分析下
参照:http://dev.mysql.com/doc/refman/ ... onsistent-read.html
运行 环境 : 5.1.39-log Source distribution
isolation level: REPEATABLE READ
表结构:
CREATE TABLE `file` (                    
`fid` int(10) unsigned NOT NULL,        
`dmid` smallint(5) unsigned NOT NULL,   
`length` int(10) unsigned DEFAULT NULL,
`classid` tinyint(3) unsigned NOT NULL,
`devcount` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`fid`)                     
) ENGINE=InnoDB                           

1:Consistent Non-Locking Reads

(表明session B未加S锁)   




可以清楚的看到上面的select是没有加锁的,只有update的时候,加了X锁,最后结果中,session B覆盖了 session A的update结果,
像上面的例子中:如果我们要根据devcount实时结果来做处理一系列,然后根据处理结果跟新的话,就会导致最后跟新的结果的不正确。在这种情况下,我们可以用Locking Reads来解决

2. Locking Reads(FOR UPDATE or LOCK IN SHARE MODE)   
1)FOR UPDATE

(session B得到的是session A 跟新后的结果,这样在计算跟新中,可以确保数据 的一致性)

可以清楚的看到, FOR UPDATE,显式的给read的行加了X锁定。确保其他transaction不能读取数据。
2) LOCK IN SHARE MODE   


很明显   LOCK IN SHARE MODE 显式的给read加上了S锁。

分享到:
评论

相关推荐

    DB - Spanner, TrueTime and The CAP Theorem.pdf

    It assigns globally consistent real-time timestamps to every datum written to it, and clients can do globally consistent reads across the entire database without locking. This leads to three kinds ...

    Spanner, TrueTime & the CAP Theorem.pdf

    It assigns globally consistent real-time timestamps to every datum written to it, and clients can do globally consistent reads across the entire database without locking. The CAP theorem [Bre12] says...

    微软内部资料-SQL性能优化3

    These include lost updates, dirty reads, non-repeatable reads, and phantoms. ANSI SQL Isolation Levels An isolation level determines the degree to which data is isolated for use by one process ...

    微软内部资料-SQL性能优化5

    Because the trees are balanced, finding any record requires about the same amount of resources, and retrieval speed is consistent because the index has the same depth throughout. Clustered and ...

Global site tag (gtag.js) - Google Analytics