文档章节

InnoDB Read-Only Transactions_只读事务

秋风醉了
 秋风醉了
发布于 2014/11/10 16:46
字数 366
阅读 89
收藏 0

InnoDB Read-Only Transactions_只读事务

官方文档:http://dev.mysql.com/doc/refman/5.6/en/innodb-performance-ro-txn.html


As of(自从) MySQL 5.6.4, InnoDB can avoid the overhead associated with setting up the transaction ID (TRX_ID field) for transactions that are known to be read-only. A transaction ID is only needed for a transaction that might perform write operations or locking reads such as SELECT ... FOR UPDATE. Eliminating(排除) unnecessary transaction IDs(不必要的transaction id) reduces(降低) the size of internal data structures that are consulted(商量,顾及) each time a query or DML(数据操纵语言) statement constructs a read view. 


Currently, InnoDB detects(发现,发觉) read-only transactions when:

  • The transaction is started with the START TRANSACTION READ ONLY statement.  In this case, attempting to make changes to the database (for InnoDB, MyISAM, or other types of tables) causes an error, and the transaction continues in read-only state: 

    ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.

    You can still make changes to session-specific temporary(临时的) tables in a read-only transaction, or issue locking queries for them, because those changes and locks are not visible to any other transaction.

  • The autocommit setting is turned on(打开), so that the transaction is guaranteed to be a single statement, and the single statement making up the transaction is a “non-locking” SELECT statement. That is, a SELECT that does not use a FOR UPDATE or LOCK IN SHARED MODE clause. 


InnoDB默认的read only有两种方式:

  • The transaction is started with the START TRANSACTION READ ONLY statement.

  • The autocommit setting is turned on(打开). 


Thus, for a read-intensive(加强的,强烈的) application such as a report generator, you can tune (调整使协调)a sequence of InnoDB queries by grouping them inside START TRANSACTION READ ONLY and COMMIT, or by turning on the autocommit setting before running the SELECT statements, or simply by avoiding any DML statements interspersed(散开的) with the queries. 


==================END==================


© 著作权归作者所有

共有 人打赏支持
秋风醉了
粉丝 236
博文 577
码字总数 418437
作品 0
朝阳
程序员
MYSQL之一步到位之InnoDB

快速认识InnoDB InnoDB是MySQL下使用最广泛的引擎,它是基于MySQL的高可扩展性和高性能存储引擎,从5.5版本开始,它已经成为了默认引擎。 InnODB引擎支持众多特性: a) 支持ACID,简单地说就...

SANSOM
2013/04/23
0
1
Transaction Access Mode_READ WRITE | READ ONLY

Transaction Access ModeREAD WRITE | READ ONLY msyql官方手册 http://dev.mysql.com/doc/refman/5.6/en/set-transaction.html Transaction Access Mode As of MySQL 5.6.5, the transacti......

秋风醉了
2014/11/10
0
0
MySQL备份工具——Xtrabackup之简介

MySQL备份工具——Xtrabackup之简介 一、简介 1、xtrabackup是percona公司开发的一款基于MySQL的开源备份工具,且优于ibbackup。 2、xtradb存储引擎也是percona公司为MySQL开发的,是InnoDB的...

perofu
2014/12/05
0
0
4种事务的隔离级别,InnoDB如何巧妙实现?

事务ACID特性,其中I代表隔离性(Isolation)。 什么是事务的隔离性? 隔离性是指,多个用户的并发事务访问同一个数据库时,一个用户的事务不应该被其他用户的事务干扰,多个并发事务之间要相互...

大数据之路
2012/09/03
0
1
InnoDB Record, Gap, and Next-Key Locks

InnoDB Record, Gap, and Next-Key Locks http://dev.mysql.com/doc/refman/5.7/en/innodb-record-level-locks.html InnoDB has several types of record-level locks including record lock......

秋风醉了
2015/11/13
0
0

没有更多内容

加载失败,请刷新页面

加载更多

你为什么在Redis里读到了本应过期的数据

一个事故的故事 晚上睡的正香突然被电话吵醒,对面是开发焦急的声音:我们的程序在访问redis的时候读到了本应过期的key导致整个业务逻辑出了问题,需要马上解决。 看到这里你可能会想:这是不...

IT--小哥
今天
2
0
祝大家节日快乐,阖家幸福! centos GnuTLS 漏洞

yum update -y gnutls 修复了GnuTLS 漏洞。更新到最新 gnutls.x86_64 0:2.12.23-22.el6 版本

yizhichao
昨天
5
0
Scrapy 1.5.0之选择器

构造选择器 Scrapy选择器是通过文本(Text)或 TextResponse 对象构造的 Selector 类的实例。 它根据输入类型自动选择最佳的解析规则(XML vs HTML): >>> from scrapy.selector import Sele...

Eappo_Geng
昨天
4
0
Windows下Git多账号配置,同一电脑多个ssh-key的管理

Windows下Git多账号配置,同一电脑多个ssh-key的管理   这一篇文章是对上一篇文章《Git-TortoiseGit完整配置流程》的拓展,所以需要对上一篇文章有所了解,当然直接往下看也可以,其中也有...

morpheusWB
昨天
5
0
中秋快乐!!!

HiBlock
昨天
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部