Transaction Access Mode_READ WRITE | READ ONLY

2019独角兽企业重金招聘Python工程师标准>>>

Transaction Access Mode_READ 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 transaction access mode may be specified with SET TRANSACTION. By default, a transaction takes place in read/write mode , with both reads and writes permitted（允许） to tables used in the transaction. This mode may be specified explicitly using an access mode of READ WRITE.

If the transaction access mode is set to READ ONLY, changes to tables are prohibited（禁止）. This may enable storage engines to make performance improvements that are possible when writes are not permitted.

It is not permitted to specify both READ WRITE and READ ONLY in the same statement. 

In read-only mode, it remains possible to change tables created with the TEMPORARY（临时的） keyword using DML statements. Changes made with DDL statements are not permitted, just as with permanent（永久的） tables.

The READ WRITE and READ ONLY access modes also may be specified for an individual transaction using the START TRANSACTION statement.

使用SET TRANSACTION设置access mode

SET [GLOBAL | SESSION] TRANSACTION

    transaction_characteristic [, transaction_characteristic] ...

transaction_characteristic:

   READ WRITE | READ ONLY

设置access mode = read write

mysql> SET TRANSACTION read write;
Query OK, 0 rows affected (0.00 sec)

mysql> update people set first_name = '12121212' where person_id = 2;
Query OK, 1 row affected (0.18 sec)
Rows matched: 1  Changed: 1  Warnings: 0

设置access mode = read only

mysql> SET TRANSACTION read only;
Query OK, 0 rows affected (0.00 sec)

mysql> update people set first_name = 'uuuuuu' where person_id = 2;
ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.

可以看到当设置只读事务时，不允许写数据。。

使用START TRANSACTION设置access mode

START TRANSACTION

    [transaction_characteristic [, transaction_characteristic] ...]

transaction_characteristic:

    WITH CONSISTENT SNAPSHOT | READ WRITE | READ ONLY

示例：

mysql> set autocommit = 0 ;
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION read only;
Query OK, 0 rows affected (0.00 sec)

mysql> update people set first_name = 'uuuuuu' where person_id = 2;
ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>

START TRANSACTION permits several modifiers that control transaction characteristics. To specify multiple modifiers, separate them by commas.

WITH CONSISTENT SNAPSHOT 

总结一句话：只有在InnoDB存储引擎隔离级别为REPEATABLE READ情况下，才能一致性读，也就是这条设置才能生效。。

The WITH CONSISTENT SNAPSHOT modifier starts a consistent read for storage engines that are capable（有能力的） of it. This applies only to InnoDB. The effect is the same as issuing a START TRANSACTION followed by a SELECT from any InnoDB table. See Section 14.2.4, “Consistent Nonlocking Reads”. The WITH CONSISTENT SNAPSHOT modifier does not change the current transaction isolation level, so it provides a consistent snapshot only if the current isolation level is one that permits a consistent read. The only isolation level that permits a consistent read is REPEATABLE READ. For all other isolation levels, the WITH CONSISTENT SNAPSHOT clause is ignored.

READ WRITE and READ ONLY 

The READ WRITE and READ ONLY modifiers set the transaction access mode. They permit （允许）or prohibit（禁止） changes to tables used in the transaction. The READ ONLY restriction（限制） prevents the transaction from modifying or locking both transactional and nontransactional tables that are visible（可见） to other transactions; the transaction can still modify or lock temporary tables. These modifiers are available as of MySQL 5.6.5.

MySQL enables extra optimizations（优化） for queries on InnoDB tables when the transaction is known to be read-only. Specifying READ ONLY ensures these optimizations are applied in cases where the read-only status cannot be determined automatically. 

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

转载于:https://my.oschina.net/xinxingegeya/blog/342764