苏州设计公司有哪些,深圳网站优化计划,网站文章怎么做分享qq,近期新闻热点事件及评论mysql数据库中默认的隔离级别为repeat-read. innodb默认使用了next-gap算法#xff0c;这种算法结合了index-row锁和gap锁。正因为这样的锁算法#xff0c;innodb在可重复读这样的默认隔离级别上#xff0c;可以避免幻象的产生。 innodb_locks_unsafe_for_binlog最主要的作用… mysql数据库中默认的隔离级别为repeat-read. innodb默认使用了next-gap算法这种算法结合了index-row锁和gap锁。正因为这样的锁算法innodb在可重复读这样的默认隔离级别上可以避免幻象的产生。 innodb_locks_unsafe_for_binlog最主要的作用就是控制innodb是否对gap加锁。 注意该参数如果是enable的则是unsafe的此时gap不会加锁反之如果disable掉该参数则gap会加锁。当然对于一些和数据完整性相关的定义如外键和唯一索引含主键需要对gap进行加锁那么innodb_locks_unsafe_for_binlog的设置并不会影响gap是否加锁。 在5.1.15的时候innodb引入了一个概念叫做“semi-consistent”这样会在innodb_locks_unsafe_for_binlog的状态为ennable时在一定程度上提高update并发性。 幻读Phantom Read): 是指当用户读取某一范围的数据行时B事务在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的“幻影”行。InnoDB和Falcon存储引擎通 过多版本并发控制机制解决了幻读问题。 Consider the following example, beginning with this table: CREATE TABLE t (a INT NOT NULL, b INT) ENGINE InnoDB;INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);COMMIT;In this case, table has no indexes, so searches and index scans use the hidden clustered index for record locking (seeSection 14.8.2.1, “Clustered and Secondary Indexes”). Suppose that one client performs an UPDATE using these statements: SET autocommit 0;UPDATE t SET b 5 WHERE b 3;Suppose also that a second client performs an UPDATE by executing these statements following those of the first client: SET autocommit 0;UPDATE t SET b 4 WHERE b 2;As InnoDB executes each UPDATE, it first acquires an exclusive lock for each row, and then determines whether to modify it. If InnoDB does not modify the row and innodb_locks_unsafe_for_binlog is enabled, it releases the lock. Otherwise, InnoDBretains the lock until the end of the transaction. This affects transaction processing as follows. If innodb_locks_unsafe_for_binlog is disabled, the first UPDATE acquires x-locks and does not release any of them: x-lock(1,2); retain x-lockx-lock(2,3); update(2,3) to (2,5); retain x-lockx-lock(3,2); retain x-lockx-lock(4,3); update(4,3) to (4,5); retain x-lockx-lock(5,2); retain x-lockThe second UPDATE blocks as soon as it tries to acquire any locks (because the first update has retained locks on all rows), and does not proceed until the first UPDATE commits or rolls back: x-lock(1,2); block and wait for first UPDATE to commit or roll backIf innodb_locks_unsafe_for_binlog is enabled, the first UPDATE acquires x-locks and releases those for rows that it does not modify: x-lock(1,2); unlock(1,2)x-lock(2,3); update(2,3) to (2,5); retain x-lockx-lock(3,2); unlock(3,2)x-lock(4,3); update(4,3) to (4,5); retain x-lockx-lock(5,2); unlock(5,2)For the second UPDATE, InnoDB does a “semi-consistent” read, returning the latest committed version of each row to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE: x-lock(1,2); update(1,2) to (1,4); retain x-lock x-lock(2,3); unlock(2,3) x-lock(3,2); update(3,2) to (3,4); retain x-lock x-lock(4,3); unlock(4,3) x-lock(5,2); update(5,2) to (5,4); retain x-lock --------------------- 作者彭薄 来源CSDN 原文https://blog.csdn.net/cxl0921/article/details/77623439 版权声明本文为博主原创文章转载请附上博文链接 转载于:https://www.cnblogs.com/DataArt/p/10176983.html
