List:Commits« Previous MessageNext Message »
From:Jon Olav Hauglid Date:February 9 2012 4:45pm
Subject:bzr push into mysql-trunk-wl5968 branch (jon.hauglid:3836 to 3837) WL#5968
View as plain text  
 3837 Jon Olav Hauglid	2012-02-09
      WL#5968: Implement START TRANSACTION READ (WRITE|ONLY);
        
      This worklog implements support for explicitly or implicitly
      starting read-only transactions. During a read-only transaction
      all DDL statements as well as inserts, updates and deletions of
      data in non-temporary tables will return an error message:
      "Cannot execute statement in a READ ONLY transaction."
      
      Read-only transactions can be explicitly started using the
      new START TRANSACTION READ ONLY syntax. Similarly read-write
      transactions can be explicitly started using START TRANSACTION
      READ WRITE.
      
      Read-only transaction can be implicitly started by first changing
      the default access mode to read-only using:
      *) SET [GLOBAL|SESSION] TRANSACTION READ ONLY
         (READ WRITE is also supported)
      *) Global/session dynamic server variable "tx_read_only".
         This has the same effect as SET GLOBAL/SESSION TRANSACTION.
      *) Server startup option --transaction-read-only
      and then starting a transaction without explicit access mode.
      Note that read-write is the default access mode (matching
      existing behavior before this worklog).
      
      The worklog also updates the client/server protocol by adding
      a new SERVER_STATUS_IN_TRANS_READONLY server status flag.
      This allows clients to examine the access mode of any current
      transaction.
      
      SET TRANSACTION is now a separate statement from SET. This
      means that it is now longer possible to execute e.g.
      SET @a= 1, TRANSACTION ISOLATION LEVEL SERIALIZABLE.

    added:
      mysql-test/suite/sys_vars/r/tx_read_only_basic.result
      mysql-test/suite/sys_vars/t/tx_read_only_basic.test
    modified:
      include/mysql/plugin.h
      include/mysql/plugin_audit.h.pp
      include/mysql/plugin_auth.h.pp
      include/mysql/plugin_ftparser.h.pp
      include/mysql_com.h
      mysql-test/r/bug58669.result
      mysql-test/r/commit.result
      mysql-test/r/mysqld--help-notwin.result
      mysql-test/r/mysqld--help-win.result
      mysql-test/r/read_only.result
      mysql-test/r/sp-code.result
      mysql-test/suite/opt_trace/r/general2_no_prot.result
      mysql-test/t/commit.test
      mysql-test/t/read_only.test
      sql/handler.cc
      sql/handler.h
      sql/lex.h
      sql/lock.cc
      sql/mysqld.cc
      sql/share/errmsg-utf8.txt
      sql/sql_base.cc
      sql/sql_class.cc
      sql/sql_class.h
      sql/sql_lex.h
      sql/sql_parse.cc
      sql/sql_table.cc
      sql/sql_yacc.yy
      sql/sys_vars.cc
      sql/sys_vars.h
      sql/transaction.cc
 3836 Jon Olav Hauglid	2012-02-03
      Change tree name.

    modified:
      .bzr-mysql/default.conf
=== modified file 'include/mysql/plugin.h'
--- a/include/mysql/plugin.h	2011-09-21 11:01:41 +0000
+++ b/include/mysql/plugin.h	2012-02-09 16:44:46 +0000
@@ -545,6 +545,7 @@ const char *thd_proc_info(MYSQL_THD thd,
 void **thd_ha_data(const MYSQL_THD thd, const struct handlerton *hton);
 void thd_storage_lock_wait(MYSQL_THD thd, long long value);
 int thd_tx_isolation(const MYSQL_THD thd);
+int thd_tx_is_read_only(const MYSQL_THD thd);
 char *thd_security_context(MYSQL_THD thd, char *buffer, unsigned int length,
                            unsigned int max_query_len);
 /* Increments the row counter, see THD::row_count */

=== modified file 'include/mysql/plugin_audit.h.pp'
--- a/include/mysql/plugin_audit.h.pp	2011-09-21 11:01:41 +0000
+++ b/include/mysql/plugin_audit.h.pp	2012-02-09 16:44:46 +0000
@@ -200,6 +200,7 @@ const char *thd_proc_info(void* thd, con
 void **thd_ha_data(const void* thd, const struct handlerton *hton);
 void thd_storage_lock_wait(void* thd, long long value);
 int thd_tx_isolation(const void* thd);
+int thd_tx_is_read_only(const void* thd);
 char *thd_security_context(void* thd, char *buffer, unsigned int length,
                            unsigned int max_query_len);
 void thd_inc_row_count(void* thd);

=== modified file 'include/mysql/plugin_auth.h.pp'
--- a/include/mysql/plugin_auth.h.pp	2011-09-21 11:01:41 +0000
+++ b/include/mysql/plugin_auth.h.pp	2012-02-09 16:44:46 +0000
@@ -200,6 +200,7 @@ const char *thd_proc_info(void* thd, con
 void **thd_ha_data(const void* thd, const struct handlerton *hton);
 void thd_storage_lock_wait(void* thd, long long value);
 int thd_tx_isolation(const void* thd);
+int thd_tx_is_read_only(const void* thd);
 char *thd_security_context(void* thd, char *buffer, unsigned int length,
                            unsigned int max_query_len);
 void thd_inc_row_count(void* thd);

=== modified file 'include/mysql/plugin_ftparser.h.pp'
--- a/include/mysql/plugin_ftparser.h.pp	2011-09-21 11:01:41 +0000
+++ b/include/mysql/plugin_ftparser.h.pp	2012-02-09 16:44:46 +0000
@@ -153,6 +153,7 @@ const char *thd_proc_info(void* thd, con
 void **thd_ha_data(const void* thd, const struct handlerton *hton);
 void thd_storage_lock_wait(void* thd, long long value);
 int thd_tx_isolation(const void* thd);
+int thd_tx_is_read_only(const void* thd);
 char *thd_security_context(void* thd, char *buffer, unsigned int length,
                            unsigned int max_query_len);
 void thd_inc_row_count(void* thd);

=== modified file 'include/mysql_com.h'
--- a/include/mysql_com.h	2011-11-17 13:41:28 +0000
+++ b/include/mysql_com.h	2012-02-09 16:44:46 +0000
@@ -242,6 +242,14 @@ enum enum_server_command
 */
 #define SERVER_STATUS_METADATA_CHANGED 1024
 #define SERVER_QUERY_WAS_SLOW          2048
+/**
+  Set at the same time as SERVER_STATUS_IN_TRANS if the started
+  transaction is a read-only transaction. Cleared when the
+  transaction commits or aborts. Since this flag is sent to
+  clients in OK and EOF packets, the flag indicates the
+  transaction status at the end of command execution.
+*/
+#define SERVER_STATUS_IN_TRANS_READONLY 4096
 
 /**
   To mark ResultSet containing output parameter values.

=== modified file 'mysql-test/r/bug58669.result'
--- a/mysql-test/r/bug58669.result	2010-12-17 11:28:59 +0000
+++ b/mysql-test/r/bug58669.result	2012-02-09 16:44:46 +0000
@@ -11,6 +11,7 @@ user1@localhost
 SHOW VARIABLES LIKE "%read_only%";
 Variable_name	Value
 read_only	ON
+tx_read_only	OFF
 INSERT INTO db1.t1 VALUES (1);
 ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
 DROP DATABASE db1;

=== modified file 'mysql-test/r/commit.result'
--- a/mysql-test/r/commit.result	2010-06-08 17:47:10 +0000
+++ b/mysql-test/r/commit.result	2012-02-09 16:44:46 +0000
@@ -12,7 +12,7 @@ INSERT INTO t1 VALUES (1),(2);
 COMMIT;
 START TRANSACTION;
 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-ERROR 25001: Transaction isolation level can't be changed while a transaction is in progress
+ERROR 25001: Transaction characteristics can't be changed while a transaction is in progress
 COMMIT;
 SET @@autocommit=0;
 COMMIT;
@@ -270,3 +270,212 @@ DROP TABLE t1;
 #
 # End of test cases for Bug#20837
 #
+#
+# WL#5968 Implement START TRANSACTION READ (WRITE|ONLY);
+#
+#
+# Test 1: Check supported syntax
+START TRANSACTION;
+COMMIT;
+START TRANSACTION READ ONLY;
+COMMIT;
+START TRANSACTION READ WRITE;
+COMMIT;
+START TRANSACTION READ ONLY, READ WRITE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
+START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT;
+COMMIT;
+START TRANSACTION READ WRITE, WITH CONSISTENT SNAPSHOT;
+COMMIT;
+START TRANSACTION WITH CONSISTENT SNAPSHOT, READ ONLY;
+COMMIT;
+START TRANSACTION WITH CONSISTENT SNAPSHOT, READ WRITE;
+COMMIT;
+START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT, READ WRITE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
+SET TRANSACTION READ ONLY;
+SET TRANSACTION READ WRITE;
+SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY;
+SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ WRITE;
+SET TRANSACTION READ ONLY, ISOLATION LEVEL READ COMMITTED;
+SET TRANSACTION READ WRITE, ISOLATION LEVEL READ COMMITTED;
+# This currently does not give a parser error
+SET TRANSACTION READ ONLY, READ WRITE;
+#
+# Test 2: Check explicit and implicit setting of variable.
+SET SESSION TRANSACTION READ WRITE;
+SELECT @@tx_read_only;
+@@tx_read_only
+0
+SET SESSION TRANSACTION READ ONLY;
+SELECT @@tx_read_only;
+@@tx_read_only
+1
+# This should reset access mode to RW
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+SELECT @@tx_read_only;
+@@tx_read_only
+0
+START TRANSACTION;
+# Not allowed inside a transaction
+SET TRANSACTION READ ONLY;
+ERROR 25001: Transaction characteristics can't be changed while a transaction is in progress
+# But these are allowed.
+SET SESSION TRANSACTION READ ONLY;
+SET GLOBAL TRANSACTION READ ONLY;
+COMMIT;
+SET SESSION TRANSACTION READ WRITE;
+SET GLOBAL TRANSACTION READ WRITE;
+#
+# Test 3: Test that write operations are properly blocked.
+CREATE TABLE t1(a INT);
+CREATE TEMPORARY TABLE temp_t2(a INT);
+START TRANSACTION READ ONLY;
+# 1: DDL should be blocked, also on temporary tables.
+CREATE TABLE t3(a INT);
+ERROR HY000: Cannot execute statement in a READ ONLY transaction.
+ALTER TABLE t1 COMMENT "Test";
+ERROR HY000: Cannot execute statement in a READ ONLY transaction.
+DROP TABLE t1;
+ERROR HY000: Cannot execute statement in a READ ONLY transaction.
+CREATE TEMPORARY TABLE temp_t3(a INT);
+ERROR HY000: Cannot execute statement in a READ ONLY transaction.
+DROP TEMPORARY TABLE temp_t2;
+ERROR HY000: Cannot execute statement in a READ ONLY transaction.
+CREATE FUNCTION f1() RETURNS INT RETURN 1;
+ERROR HY000: Cannot execute statement in a READ ONLY transaction.
+DROP FUNCTION f1;
+ERROR HY000: Cannot execute statement in a READ ONLY transaction.
+CREATE PROCEDURE p1() BEGIN END;
+ERROR HY000: Cannot execute statement in a READ ONLY transaction.
+DROP PROCEDURE p1;
+ERROR HY000: Cannot execute statement in a READ ONLY transaction.
+CREATE VIEW v1 AS SELECT 1;
+ERROR HY000: Cannot execute statement in a READ ONLY transaction.
+COMMIT;
+CREATE VIEW v1 AS SELECT 1;
+START TRANSACTION READ ONLY;
+DROP VIEW v1;
+ERROR HY000: Cannot execute statement in a READ ONLY transaction.
+COMMIT;
+DROP VIEW v1;
+START TRANSACTION READ ONLY;
+RENAME TABLE t1 TO t2;
+ERROR HY000: Cannot execute statement in a READ ONLY transaction.
+TRUNCATE TABLE t1;
+ERROR HY000: Cannot execute statement in a READ ONLY transaction.
+CREATE DATABASE db1;
+ERROR HY000: Cannot execute statement in a READ ONLY transaction.
+DROP DATABASE db1;
+ERROR HY000: Cannot execute statement in a READ ONLY transaction.
+# 2: DML should be blocked on non-temporary tables.
+INSERT INTO t1 VALUES (1), (2);
+ERROR HY000: Cannot execute statement in a READ ONLY transaction.
+UPDATE t1 SET a= 3;
+ERROR HY000: Cannot execute statement in a READ ONLY transaction.
+DELETE FROM t1;
+ERROR HY000: Cannot execute statement in a READ ONLY transaction.
+INSERT INTO temp_t2 VALUES (1), (2);
+UPDATE temp_t2 SET a= 3;
+DELETE FROM temp_t2;
+# 3: Queries should not be blocked.
+SELECT * FROM t1;
+a
+SELECT * FROM temp_t2;
+a
+HANDLER t1 OPEN;
+HANDLER t1 READ FIRST;
+a
+HANDLER t1 CLOSE;
+HANDLER temp_t2 OPEN;
+HANDLER temp_t2 READ FIRST;
+a
+HANDLER temp_t2 CLOSE;
+# 4: Prepared statements
+PREPARE stmt FROM "DELETE FROM t1";
+ERROR HY000: Cannot execute statement in a READ ONLY transaction.
+PREPARE stmt FROM "DELETE FROM temp_t2";
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+# 5: Stored routines
+COMMIT;
+CREATE FUNCTION f1() RETURNS INT
+BEGIN
+DELETE FROM t1;
+RETURN 1;
+END|
+CREATE FUNCTION f2() RETURNS INT
+BEGIN
+DELETE FROM temp_t2;
+RETURN 1;
+END|
+CREATE PROCEDURE p1() DELETE FROM t1;
+CREATE PROCEDURE p2() DELETE FROM temp_t2;
+START TRANSACTION READ WRITE;
+SELECT f1();
+f1()
+1
+SELECT f2();
+f2()
+1
+CALL p1();
+CALL p2();
+COMMIT;
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+START TRANSACTION READ ONLY;
+# 6: Views
+COMMIT;
+CREATE VIEW v1 AS SELECT a FROM t1;
+START TRANSACTION READ ONLY;
+INSERT INTO v1 VALUES (1), (2);
+ERROR HY000: Cannot execute statement in a READ ONLY transaction.
+SELECT * FROM v1;
+a
+COMMIT;
+DROP VIEW v1;
+START TRANSACTION READ ONLY;
+# 7: LOCK TABLE
+LOCK TABLE t1 WRITE;
+ERROR HY000: Cannot execute statement in a READ ONLY transaction.
+LOCK TABLE t1 READ;
+UNLOCK TABLES;
+COMMIT;
+DROP TABLE temp_t2, t1;
+#
+# Test 4: SET TRANSACTION, CHAINing transactions
+CREATE TABLE t1(a INT);
+SET SESSION TRANSACTION READ ONLY;
+START TRANSACTION;
+DELETE FROM t1;
+ERROR HY000: Cannot execute statement in a READ ONLY transaction.
+COMMIT;
+START TRANSACTION READ WRITE;
+DELETE FROM t1;
+COMMIT;
+SET SESSION TRANSACTION READ WRITE;
+SET TRANSACTION READ ONLY;
+START TRANSACTION;
+DELETE FROM t1;
+ERROR HY000: Cannot execute statement in a READ ONLY transaction.
+COMMIT;
+START TRANSACTION READ WRITE;
+DELETE FROM t1;
+COMMIT;
+START TRANSACTION READ ONLY;
+SELECT * FROM t1;
+a
+COMMIT AND CHAIN;
+DELETE FROM t1;
+ERROR HY000: Cannot execute statement in a READ ONLY transaction.
+COMMIT;
+START TRANSACTION READ ONLY;
+SELECT * FROM t1;
+a
+ROLLBACK AND CHAIN;
+DELETE FROM t1;
+ERROR HY000: Cannot execute statement in a READ ONLY transaction.
+COMMIT;
+DROP TABLE t1;

=== modified file 'mysql-test/r/mysqld--help-notwin.result'
--- a/mysql-test/r/mysqld--help-notwin.result	2012-02-02 16:56:36 +0000
+++ b/mysql-test/r/mysqld--help-notwin.result	2012-02-09 16:44:46 +0000
@@ -857,6 +857,9 @@ The following options may be given as th
  --transaction-prealloc-size=# 
  Persistent buffer for transactions to be stored in binary
  log
+ --transaction-read-only 
+ Default transaction access mode. True if transactions are
+ read-only.
  --updatable-views-with-limit=name 
  YES = Don't issue an error message (warning only) if a
  VIEW without presence of a key of the underlying table is
@@ -1132,6 +1135,7 @@ tmp-table-size 16777216
 transaction-alloc-block-size 8192
 transaction-isolation REPEATABLE-READ
 transaction-prealloc-size 4096
+transaction-read-only FALSE
 updatable-views-with-limit YES
 verbose TRUE
 wait-timeout 28800

=== modified file 'mysql-test/r/mysqld--help-win.result'
--- a/mysql-test/r/mysqld--help-win.result	2012-02-02 16:56:36 +0000
+++ b/mysql-test/r/mysqld--help-win.result	2012-02-09 16:44:46 +0000
@@ -865,6 +865,9 @@ The following options may be given as th
  --transaction-prealloc-size=# 
  Persistent buffer for transactions to be stored in binary
  log
+ --transaction-read-only 
+ Default transaction access mode. True if transactions are
+ read-only.
  --updatable-views-with-limit=name 
  YES = Don't issue an error message (warning only) if a
  VIEW without presence of a key of the underlying table is
@@ -1143,6 +1146,7 @@ tmp-table-size 16777216
 transaction-alloc-block-size 8192
 transaction-isolation REPEATABLE-READ
 transaction-prealloc-size 4096
+transaction-read-only FALSE
 updatable-views-with-limit YES
 verbose TRUE
 wait-timeout 28800

=== modified file 'mysql-test/r/read_only.result'
--- a/mysql-test/r/read_only.result	2010-08-30 06:38:09 +0000
+++ b/mysql-test/r/read_only.result	2012-02-09 16:44:46 +0000
@@ -159,3 +159,27 @@ delete from mysql.columns_priv where Use
 flush privileges;
 drop database mysqltest_db1;
 set global read_only= @start_read_only;
+#
+# WL#5968 Implement START TRANSACTION READ (WRITE|ONLY);
+#
+#
+# Test interaction with read_only system variable.
+CREATE USER user1;
+SET GLOBAL read_only= 1;
+# All allowed with super privilege
+START TRANSACTION;
+COMMIT;
+START TRANSACTION READ ONLY;
+COMMIT;
+START TRANSACTION READ WRITE;
+COMMIT;
+# Explicit RW trans is not allowed without super privilege
+START TRANSACTION;
+COMMIT;
+START TRANSACTION READ ONLY;
+COMMIT;
+START TRANSACTION READ WRITE;
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+COMMIT;
+DROP USER user1;
+SET GLOBAL read_only= 0;

=== modified file 'mysql-test/r/sp-code.result'
--- a/mysql-test/r/sp-code.result	2011-09-20 12:13:07 +0000
+++ b/mysql-test/r/sp-code.result	2012-02-09 16:44:46 +0000
@@ -847,7 +847,7 @@ drop procedure if exists p_20906_b;
 create procedure p_20906_a() SET @a=@a+1, @b=@b+1;
 show procedure code p_20906_a;
 Pos	Instruction
-0	stmt 31 "SET @a=@a+1"
+0	stmt 31 "SET  @a=@a+1"
 1	stmt 31 "SET  @b=@b+1"
 set @a=1;
 set @b=1;
@@ -858,7 +858,7 @@ select @a, @b;
 create procedure p_20906_b() SET @a=@a+1, @b=@b+1, @c=@c+1;
 show procedure code p_20906_b;
 Pos	Instruction
-0	stmt 31 "SET @a=@a+1"
+0	stmt 31 "SET  @a=@a+1"
 1	stmt 31 "SET  @b=@b+1"
 2	stmt 31 "SET  @c=@c+1"
 set @a=1;

=== modified file 'mysql-test/suite/opt_trace/r/general2_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/general2_no_prot.result	2012-01-26 13:09:59 +0000
+++ b/mysql-test/suite/opt_trace/r/general2_no_prot.result	2012-02-09 16:44:46 +0000
@@ -1666,7 +1666,7 @@ insert into t1 values(3)	{
   "steps": [
   ] /* steps */
 }	0	0
-SET @a=(select count(a) from t1 where a>0)	{
+SET  @a=(select count(a) from t1 where a>0)	{
   "steps": [
     {
       "join_preparation": {
@@ -1924,7 +1924,7 @@ jump_if_not 11(15) (case_expr@0 = 2)	{
   "steps": [
   ] /* steps */
 }	0	0
-SET @b=2	{
+SET  @b=2	{
   "steps": [
   ] /* steps */
 }	0	0

=== added file 'mysql-test/suite/sys_vars/r/tx_read_only_basic.result'
--- a/mysql-test/suite/sys_vars/r/tx_read_only_basic.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/sys_vars/r/tx_read_only_basic.result	2012-02-09 16:44:46 +0000
@@ -0,0 +1,163 @@
+SET @start_global_value = @@global.tx_read_only;
+SELECT @start_global_value;
+@start_global_value
+0
+SET @start_session_value = @@session.tx_read_only;
+SELECT @start_session_value;
+@start_session_value
+0
+'#--------------------FN_DYNVARS_054_01-------------------------#'
+SET @@global.tx_read_only = ON;
+SET @@global.tx_read_only = DEFAULT;
+SELECT @@global.tx_read_only;
+@@global.tx_read_only
+0
+SET @@session.tx_read_only = ON;
+SET @@session.tx_read_only = DEFAULT;
+SELECT @@session.tx_read_only;
+@@session.tx_read_only
+0
+'#--------------------FN_DYNVARS_054_02-------------------------#'
+SET @@global.tx_read_only = DEFAULT;
+SELECT @@global.tx_read_only = 'OFF';
+@@global.tx_read_only = 'OFF'
+1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'OFF'
+SET @@session.tx_read_only = DEFAULT;
+SELECT @@session.tx_read_only = 'OFF';
+@@session.tx_read_only = 'OFF'
+1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'OFF'
+'#--------------------FN_DYNVARS_054_03-------------------------#'
+SET @@global.tx_read_only = ON;
+SELECT @@global.tx_read_only;
+@@global.tx_read_only
+1
+SET @@global.tx_read_only = OFF;
+SELECT @@global.tx_read_only;
+@@global.tx_read_only
+0
+SET @@global.tx_read_only = 0;
+SELECT @@global.tx_read_only;
+@@global.tx_read_only
+0
+SET @@global.tx_read_only = 1;
+SELECT @@global.tx_read_only;
+@@global.tx_read_only
+1
+SET @@global.tx_read_only = TRUE;
+SELECT @@global.tx_read_only;
+@@global.tx_read_only
+1
+SET @@global.tx_read_only = FALSE;
+SELECT @@global.tx_read_only;
+@@global.tx_read_only
+0
+'#--------------------FN_DYNVARS_054_04-------------------------#'
+SET @@session.tx_read_only = ON;
+SELECT @@session.tx_read_only;
+@@session.tx_read_only
+1
+SET @@session.tx_read_only = OFF;
+SELECT @@session.tx_read_only;
+@@session.tx_read_only
+0
+SET @@session.tx_read_only = 0;
+SELECT @@session.tx_read_only;
+@@session.tx_read_only
+0
+SET @@session.tx_read_only = 1;
+SELECT @@session.tx_read_only;
+@@session.tx_read_only
+1
+SET @@session.tx_read_only = TRUE;
+SELECT @@session.tx_read_only;
+@@session.tx_read_only
+1
+SET @@session.tx_read_only = FALSE;
+SELECT @@session.tx_read_only;
+@@session.tx_read_only
+0
+'#------------------FN_DYNVARS_054_05-----------------------#'
+SET @@global.tx_read_only = 'ONN';
+ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'ONN'
+SET @@global.tx_read_only = "OFFF";
+ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'OFFF'
+SET @@global.tx_read_only = TTRUE;
+ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'TTRUE'
+SET @@global.tx_read_only = FELSE;
+ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'FELSE'
+SET @@global.tx_read_only = -1024;
+ERROR 42000: Variable 'tx_read_only' can't be set to the value of '-1024'
+SET @@global.tx_read_only = 65536;
+ERROR 42000: Variable 'tx_read_only' can't be set to the value of '65536'
+SET @@global.tx_read_only = 65530.34;
+ERROR 42000: Incorrect argument type to variable 'tx_read_only'
+SET @@global.tx_read_only = test;
+ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'test'
+SET @@session.tx_read_only = ONN;
+ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'ONN'
+SET @@session.tx_read_only = ONF;
+ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'ONF'
+SET @@session.tx_read_only = OF;
+ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'OF'
+SET @@session.tx_read_only = 'OFN';
+ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'OFN'
+SET @@session.tx_read_only = -2;
+ERROR 42000: Variable 'tx_read_only' can't be set to the value of '-2'
+SET @@session.tx_read_only = 65530.34;
+ERROR 42000: Incorrect argument type to variable 'tx_read_only'
+SET @@session.tx_read_only = 65550;
+ERROR 42000: Variable 'tx_read_only' can't be set to the value of '65550'
+SET @@session.tx_read_only = test;
+ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'test'
+SELECT @@session.tx_read_only;
+@@session.tx_read_only
+0
+'#------------------FN_DYNVARS_054_06-----------------------#'
+SELECT IF(@@global.tx_read_only, "ON", "OFF") = VARIABLE_VALUE 
+FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
+WHERE VARIABLE_NAME='tx_read_only';
+IF(@@global.tx_read_only, "ON", "OFF") = VARIABLE_VALUE
+1
+'#------------------FN_DYNVARS_054_07-----------------------#'
+SELECT IF(@@session.tx_read_only, "ON", "OFF") = VARIABLE_VALUE 
+FROM INFORMATION_SCHEMA.SESSION_VARIABLES 
+WHERE VARIABLE_NAME='tx_read_only';
+IF(@@session.tx_read_only, "ON", "OFF") = VARIABLE_VALUE
+1
+'#---------------------FN_DYNVARS_001_08----------------------#'
+SET @@tx_read_only = OFF;
+SET @@global.tx_read_only = ON;
+SELECT @@tx_read_only = @@global.tx_read_only;
+@@tx_read_only = @@global.tx_read_only
+0
+'#---------------------FN_DYNVARS_001_09----------------------#'
+SET @@tx_read_only = ON;
+SELECT @@tx_read_only = @@local.tx_read_only;
+@@tx_read_only = @@local.tx_read_only
+1
+SELECT @@local.tx_read_only = @@session.tx_read_only;
+@@local.tx_read_only = @@session.tx_read_only
+1
+'#---------------------FN_DYNVARS_001_10----------------------#'
+SET tx_read_only = 1;
+SELECT @@tx_read_only;
+@@tx_read_only
+1
+SELECT local.tx_read_only;
+ERROR 42S02: Unknown table 'local' in field list
+SELECT session.tx_read_only;
+ERROR 42S02: Unknown table 'session' in field list
+SELECT tx_read_only = @@session.tx_read_only;
+ERROR 42S22: Unknown column 'tx_read_only' in 'field list'
+SET @@global.tx_read_only = @start_global_value;
+SELECT @@global.tx_read_only;
+@@global.tx_read_only
+0
+SET @@session.tx_read_only = @start_session_value;
+SELECT @@session.tx_read_only;
+@@session.tx_read_only
+0

=== added file 'mysql-test/suite/sys_vars/t/tx_read_only_basic.test'
--- a/mysql-test/suite/sys_vars/t/tx_read_only_basic.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/sys_vars/t/tx_read_only_basic.test	2012-02-09 16:44:46 +0000
@@ -0,0 +1,219 @@
+############## mysql-test\t\tx_read_only_basic.test ###########################
+#                                                                             #
+# Variable Name: tx_read_only                                                 #
+# Scope: GLOBAL & SESSION                                                     #
+# Access Type: Dynamic                                                        #
+# Data Type: boolean                                                          #
+# Default Value: OFF                                                          #
+# Range:                                                                      #
+#                                                                             #
+#                                                                             #
+# Creation Date: 2012-01-12                                                   #
+# Author:  joh                                                                #
+#                                                                             #
+# Description: Test Cases of Dynamic System Variable tx_read_only             #
+#              that checks the behavior of this variable in the following ways#
+#              * Default Value                                                #
+#              * Valid & Invalid values                                       #
+#              * Scope & Access method                                        #
+#              * Data Integrity                                               #
+#                                                                             #
+# Reference: http://dev.mysql.com/doc/refman/5.6/en/                          #
+#  server-system-variables.html                                               #
+#                                                                             #
+###############################################################################
+
+--source include/load_sysvars.inc
+
+####################################################################
+#           START OF tx_read_only TESTS                            #
+####################################################################
+
+
+#############################################################
+#                 Save initial value                        #
+#############################################################
+
+SET @start_global_value = @@global.tx_read_only;
+SELECT @start_global_value;
+SET @start_session_value = @@session.tx_read_only;
+SELECT @start_session_value;
+
+
+--echo '#--------------------FN_DYNVARS_054_01-------------------------#'
+########################################################################
+#     Display the DEFAULT value of tx_read_only                        #
+########################################################################
+
+SET @@global.tx_read_only = ON;
+SET @@global.tx_read_only = DEFAULT;
+SELECT @@global.tx_read_only;
+
+SET @@session.tx_read_only = ON;
+SET @@session.tx_read_only = DEFAULT;
+SELECT @@session.tx_read_only;
+
+
+--echo '#--------------------FN_DYNVARS_054_02-------------------------#'
+########################################################################
+#     Check the DEFAULT value of tx_read_only                          #
+########################################################################
+
+SET @@global.tx_read_only = DEFAULT;
+SELECT @@global.tx_read_only = 'OFF';
+
+SET @@session.tx_read_only = DEFAULT;
+SELECT @@session.tx_read_only = 'OFF';
+
+
+--echo '#--------------------FN_DYNVARS_054_03-------------------------#'
+##############################################################################
+# Change the value of tx_read_only to a valid value for GLOBAL Scope         #
+##############################################################################
+
+SET @@global.tx_read_only = ON;
+SELECT @@global.tx_read_only;
+SET @@global.tx_read_only = OFF;
+SELECT @@global.tx_read_only;
+SET @@global.tx_read_only = 0;
+SELECT @@global.tx_read_only;
+SET @@global.tx_read_only = 1;
+SELECT @@global.tx_read_only;
+SET @@global.tx_read_only = TRUE;
+SELECT @@global.tx_read_only;
+SET @@global.tx_read_only = FALSE;
+SELECT @@global.tx_read_only;
+
+
+
+--echo '#--------------------FN_DYNVARS_054_04-------------------------#'
+###############################################################################
+# Change the value of tx_read_only to a valid value for SESSION Scope         #
+###############################################################################
+ 
+SET @@session.tx_read_only = ON;
+SELECT @@session.tx_read_only;
+SET @@session.tx_read_only = OFF;
+SELECT @@session.tx_read_only;
+SET @@session.tx_read_only = 0;
+SELECT @@session.tx_read_only;
+SET @@session.tx_read_only = 1;
+SELECT @@session.tx_read_only;
+SET @@session.tx_read_only = TRUE;
+SELECT @@session.tx_read_only;
+SET @@session.tx_read_only = FALSE;
+SELECT @@session.tx_read_only;
+
+
+--echo '#------------------FN_DYNVARS_054_05-----------------------#'
+################################################################
+# Change the value of tx_read_only to an invalid value         #
+################################################################
+
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@global.tx_read_only = 'ONN';
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@global.tx_read_only = "OFFF";
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@global.tx_read_only = TTRUE;
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@global.tx_read_only = FELSE;
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@global.tx_read_only = -1024;
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@global.tx_read_only = 65536;
+--Error ER_WRONG_TYPE_FOR_VAR
+SET @@global.tx_read_only = 65530.34;
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@global.tx_read_only = test;
+
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@session.tx_read_only = ONN;
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@session.tx_read_only = ONF;
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@session.tx_read_only = OF;
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@session.tx_read_only = 'OFN';
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@session.tx_read_only = -2;
+--Error ER_WRONG_TYPE_FOR_VAR
+SET @@session.tx_read_only = 65530.34;
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@session.tx_read_only = 65550;
+
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@session.tx_read_only = test;
+SELECT @@session.tx_read_only;
+
+
+--echo '#------------------FN_DYNVARS_054_06-----------------------#'
+####################################################################
+#   Check if the value in GLOBAL Table matches value in variable   #
+####################################################################
+
+
+SELECT IF(@@global.tx_read_only, "ON", "OFF") = VARIABLE_VALUE 
+FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
+WHERE VARIABLE_NAME='tx_read_only';
+
+--echo '#------------------FN_DYNVARS_054_07-----------------------#'
+####################################################################
+#  Check if the value in SESSION Table matches value in variable   #
+####################################################################
+
+SELECT IF(@@session.tx_read_only, "ON", "OFF") = VARIABLE_VALUE 
+FROM INFORMATION_SCHEMA.SESSION_VARIABLES 
+WHERE VARIABLE_NAME='tx_read_only';
+
+
+--echo '#---------------------FN_DYNVARS_001_08----------------------#'
+###############################################################################
+#  Check if accessing variable with and without GLOBAL point to same variable #
+###############################################################################
+
+SET @@tx_read_only = OFF;
+SET @@global.tx_read_only = ON;
+SELECT @@tx_read_only = @@global.tx_read_only;
+
+
+--echo '#---------------------FN_DYNVARS_001_09----------------------#'
+##############################################################################
+#    Check if accessing variable with SESSION,LOCAL and without SCOPE points #
+#    to same session variable                                                #
+##############################################################################
+
+SET @@tx_read_only = ON;
+SELECT @@tx_read_only = @@local.tx_read_only;
+SELECT @@local.tx_read_only = @@session.tx_read_only;
+
+
+--echo '#---------------------FN_DYNVARS_001_10----------------------#'
+###############################################################################
+#   Check if tx_read_only can be accessed with and without @@ sign            #
+###############################################################################
+
+SET tx_read_only = 1;
+SELECT @@tx_read_only;
+--Error ER_UNKNOWN_TABLE
+SELECT local.tx_read_only;
+--Error ER_UNKNOWN_TABLE
+SELECT session.tx_read_only;
+--Error ER_BAD_FIELD_ERROR
+SELECT tx_read_only = @@session.tx_read_only;
+
+
+####################################
+#     Restore initial value        #
+####################################
+
+SET @@global.tx_read_only = @start_global_value;
+SELECT @@global.tx_read_only;
+SET @@session.tx_read_only = @start_session_value;
+SELECT @@session.tx_read_only;
+
+
+#########################################################
+#                 END OF tx_read_only TESTS             #
+#########################################################
+

=== modified file 'mysql-test/t/commit.test'
--- a/mysql-test/t/commit.test	2010-06-08 17:47:10 +0000
+++ b/mysql-test/t/commit.test	2012-02-09 16:44:46 +0000
@@ -28,7 +28,7 @@ COMMIT;
 # inside a transaction
 #
 START TRANSACTION;
---error ER_CANT_CHANGE_TX_ISOLATION
+--error ER_CANT_CHANGE_TX_CHARACTERISTICS
 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 COMMIT;
 
@@ -348,3 +348,254 @@ DROP TABLE t1;
 --echo #
 --echo # End of test cases for Bug#20837
 --echo #
+
+
+--echo #
+--echo # WL#5968 Implement START TRANSACTION READ (WRITE|ONLY);
+--echo #
+
+--echo #
+--echo # Test 1: Check supported syntax
+
+START TRANSACTION;
+COMMIT;
+
+START TRANSACTION READ ONLY;
+COMMIT;
+
+START TRANSACTION READ WRITE;
+COMMIT;
+
+--error ER_PARSE_ERROR
+START TRANSACTION READ ONLY, READ WRITE;
+
+START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT;
+COMMIT;
+
+START TRANSACTION READ WRITE, WITH CONSISTENT SNAPSHOT;
+COMMIT;
+
+START TRANSACTION WITH CONSISTENT SNAPSHOT, READ ONLY;
+COMMIT;
+
+START TRANSACTION WITH CONSISTENT SNAPSHOT, READ WRITE;
+COMMIT;
+
+--error ER_PARSE_ERROR
+START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT, READ WRITE;
+
+SET TRANSACTION READ ONLY;
+SET TRANSACTION READ WRITE;
+SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY;
+SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ WRITE;
+SET TRANSACTION READ ONLY, ISOLATION LEVEL READ COMMITTED;
+SET TRANSACTION READ WRITE, ISOLATION LEVEL READ COMMITTED;
+--echo # This currently does not give a parser error
+SET TRANSACTION READ ONLY, READ WRITE;
+
+--echo #
+--echo # Test 2: Check explicit and implicit setting of variable.
+
+SET SESSION TRANSACTION READ WRITE;
+SELECT @@tx_read_only;
+
+SET SESSION TRANSACTION READ ONLY;
+SELECT @@tx_read_only;
+
+--echo # This should reset access mode to RW
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+SELECT @@tx_read_only;
+
+START TRANSACTION;
+--echo # Not allowed inside a transaction
+--error ER_CANT_CHANGE_TX_CHARACTERISTICS
+SET TRANSACTION READ ONLY;
+--echo # But these are allowed.
+SET SESSION TRANSACTION READ ONLY;
+SET GLOBAL TRANSACTION READ ONLY;
+COMMIT;
+
+SET SESSION TRANSACTION READ WRITE;
+SET GLOBAL TRANSACTION READ WRITE;
+
+--echo #
+--echo # Test 3: Test that write operations are properly blocked.
+
+CREATE TABLE t1(a INT);
+CREATE TEMPORARY TABLE temp_t2(a INT);
+
+START TRANSACTION READ ONLY;
+
+--echo # 1: DDL should be blocked, also on temporary tables.
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+CREATE TABLE t3(a INT);
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+ALTER TABLE t1 COMMENT "Test";
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+DROP TABLE t1;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+CREATE TEMPORARY TABLE temp_t3(a INT);
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+DROP TEMPORARY TABLE temp_t2;
+
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+CREATE FUNCTION f1() RETURNS INT RETURN 1;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+DROP FUNCTION f1;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+CREATE PROCEDURE p1() BEGIN END;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+DROP PROCEDURE p1;
+
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+CREATE VIEW v1 AS SELECT 1;
+COMMIT;
+CREATE VIEW v1 AS SELECT 1;
+START TRANSACTION READ ONLY;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+DROP VIEW v1;
+COMMIT;
+DROP VIEW v1;
+START TRANSACTION READ ONLY;
+
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+RENAME TABLE t1 TO t2;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+TRUNCATE TABLE t1;
+
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+CREATE DATABASE db1;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+DROP DATABASE db1;
+
+--echo # 2: DML should be blocked on non-temporary tables.
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+INSERT INTO t1 VALUES (1), (2);
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+UPDATE t1 SET a= 3;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+DELETE FROM t1;
+
+INSERT INTO temp_t2 VALUES (1), (2);
+UPDATE temp_t2 SET a= 3;
+DELETE FROM temp_t2;
+
+--echo # 3: Queries should not be blocked.
+SELECT * FROM t1;
+SELECT * FROM temp_t2;
+
+HANDLER t1 OPEN;
+HANDLER t1 READ FIRST;
+HANDLER t1 CLOSE;
+
+HANDLER temp_t2 OPEN;
+HANDLER temp_t2 READ FIRST;
+HANDLER temp_t2 CLOSE;
+
+--echo # 4: Prepared statements
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+PREPARE stmt FROM "DELETE FROM t1";
+
+PREPARE stmt FROM "DELETE FROM temp_t2";
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+--echo # 5: Stored routines
+# Temporarily commit so that DDL can be executed.
+COMMIT;
+
+delimiter |;
+CREATE FUNCTION f1() RETURNS INT
+BEGIN
+  DELETE FROM t1;
+  RETURN 1;
+END|
+
+CREATE FUNCTION f2() RETURNS INT
+BEGIN
+  DELETE FROM temp_t2;
+  RETURN 1;
+END|
+delimiter ;|
+
+CREATE PROCEDURE p1() DELETE FROM t1;
+CREATE PROCEDURE p2() DELETE FROM temp_t2;
+
+START TRANSACTION READ WRITE;
+SELECT f1();
+SELECT f2();
+CALL p1();
+CALL p2();
+COMMIT;
+
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+START TRANSACTION READ ONLY;
+
+--echo # 6: Views
+# Temporarily commit so that DDL can be executed.
+COMMIT;
+
+CREATE VIEW v1 AS SELECT a FROM t1;
+# Not supported from temporary tables.
+
+START TRANSACTION READ ONLY;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+INSERT INTO v1 VALUES (1), (2);
+SELECT * FROM v1;
+COMMIT;
+
+DROP VIEW v1;
+START TRANSACTION READ ONLY;
+
+--echo # 7: LOCK TABLE
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+LOCK TABLE t1 WRITE;
+
+LOCK TABLE t1 READ;
+UNLOCK TABLES;
+
+COMMIT;
+DROP TABLE temp_t2, t1;
+
+--echo #
+--echo # Test 4: SET TRANSACTION, CHAINing transactions
+
+CREATE TABLE t1(a INT);
+
+SET SESSION TRANSACTION READ ONLY;
+START TRANSACTION;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+DELETE FROM t1;
+COMMIT;
+START TRANSACTION READ WRITE;
+DELETE FROM t1;
+COMMIT;
+
+SET SESSION TRANSACTION READ WRITE;
+SET TRANSACTION READ ONLY;
+START TRANSACTION;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+DELETE FROM t1;
+COMMIT;
+START TRANSACTION READ WRITE;
+DELETE FROM t1;
+COMMIT;
+
+START TRANSACTION READ ONLY;
+SELECT * FROM t1;
+COMMIT AND CHAIN;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+DELETE FROM t1;
+COMMIT;
+
+START TRANSACTION READ ONLY;
+SELECT * FROM t1;
+ROLLBACK AND CHAIN;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+DELETE FROM t1;
+COMMIT;
+
+DROP TABLE t1;

=== modified file 'mysql-test/t/read_only.test'
--- a/mysql-test/t/read_only.test	2009-03-06 14:56:17 +0000
+++ b/mysql-test/t/read_only.test	2012-02-09 16:44:46 +0000
@@ -303,6 +303,49 @@ flush privileges;
 drop database mysqltest_db1;
 set global read_only= @start_read_only;
 
+
+--echo #
+--echo # WL#5968 Implement START TRANSACTION READ (WRITE|ONLY);
+--echo #
+
+--echo #
+--echo # Test interaction with read_only system variable.
+
+CREATE USER user1;
+connect (con1, localhost, user1);
+connection default;
+
+SET GLOBAL read_only= 1;
+
+--echo # All allowed with super privilege
+START TRANSACTION;
+COMMIT;
+
+START TRANSACTION READ ONLY;
+COMMIT;
+
+START TRANSACTION READ WRITE;
+COMMIT;
+
+--echo # Explicit RW trans is not allowed without super privilege
+connection con1;
+START TRANSACTION;
+COMMIT;
+
+START TRANSACTION READ ONLY;
+COMMIT;
+
+--error ER_OPTION_PREVENTS_STATEMENT
+START TRANSACTION READ WRITE;
+COMMIT;
+disconnect con1;
+--source include/wait_until_disconnected.inc
+connection default;
+DROP USER user1;
+
+SET GLOBAL read_only= 0;
+
+
 # Wait till all disconnects are completed
 --source include/wait_until_count_sessions.inc
 

=== modified file 'sql/handler.cc'
--- a/sql/handler.cc	2012-02-02 16:56:36 +0000
+++ b/sql/handler.cc	2012-02-09 16:44:46 +0000
@@ -1014,6 +1014,8 @@ void trans_register_ha(THD *thd, bool al
   {
     trans= &thd->transaction.all;
     thd->server_status|= SERVER_STATUS_IN_TRANS;
+    if (thd->tx_read_only)
+      thd->server_status|= SERVER_STATUS_IN_TRANS_READONLY;
   }
   else
     trans= &thd->transaction.stmt;

=== modified file 'sql/handler.h'
--- a/sql/handler.h	2012-01-26 12:53:38 +0000
+++ b/sql/handler.h	2012-02-09 16:44:46 +0000
@@ -299,7 +299,9 @@
 #define HA_CACHE_TBL_TRANSACT    4
 
 /* Options of START TRANSACTION statement (and later of SET TRANSACTION stmt) */
-#define MYSQL_START_TRANS_OPT_WITH_CONS_SNAPSHOT 1
+static const uint MYSQL_START_TRANS_OPT_WITH_CONS_SNAPSHOT = 1;
+static const uint MYSQL_START_TRANS_OPT_READ_ONLY          = 2;
+static const uint MYSQL_START_TRANS_OPT_READ_WRITE         = 4;
 
 /* Flags for method is_fatal_error */
 #define HA_CHECK_DUP_KEY 1

=== modified file 'sql/lex.h'
--- a/sql/lex.h	2011-11-04 16:07:37 +0000
+++ b/sql/lex.h	2012-02-09 16:44:46 +0000
@@ -397,6 +397,7 @@ static SYMBOL symbols[] = {
   { "ON",		SYM(ON)},
   { "ONE",              SYM(ONE_SYM)},
   { "ONE_SHOT",		SYM(ONE_SHOT_SYM)},
+  { "ONLY",             SYM(ONLY_SYM)},
   { "OPEN",		SYM(OPEN_SYM)},
   { "OPTIMIZE",		SYM(OPTIMIZE)},
   { "OPTIONS",		SYM(OPTIONS_SYM)},

=== modified file 'sql/lock.cc'
--- a/sql/lock.cc	2011-11-28 07:42:18 +0000
+++ b/sql/lock.cc	2012-02-09 16:44:46 +0000
@@ -778,6 +778,13 @@ bool lock_schema_name(THD *thd, const ch
   MDL_request global_request;
   MDL_request mdl_request;
 
+  /* DDL is not allowed in a read only transaction. */
+  if (thd->tx_read_only)
+  {
+    my_error(ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION, MYF(0));
+    return true;
+  }
+
   if (thd->locked_tables_mode)
   {
     my_message(ER_LOCK_OR_ACTIVE_TRANSACTION,

=== modified file 'sql/mysqld.cc'
--- a/sql/mysqld.cc	2012-01-30 05:34:32 +0000
+++ b/sql/mysqld.cc	2012-02-09 16:44:46 +0000
@@ -6447,6 +6447,12 @@ struct my_option my_long_options[]=
    &global_system_variables.tx_isolation,
    &global_system_variables.tx_isolation, &tx_isolation_typelib,
    GET_ENUM, REQUIRED_ARG, ISO_REPEATABLE_READ, 0, 0, 0, 0, 0},
+  {"transaction-read-only", 0,
+   "Default transaction access mode. "
+   "True if transactions are read-only.",
+   &global_system_variables.tx_read_only,
+   &global_system_variables.tx_read_only, 0,
+   GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
   {"user", 'u', "Run mysqld daemon as user.", 0, 0, 0, GET_STR, REQUIRED_ARG,
    0, 0, 0, 0, 0, 0},
   {"verbose", 'v', "Used with --help option for detailed help.",

=== modified file 'sql/share/errmsg-utf8.txt'
--- a/sql/share/errmsg-utf8.txt	2012-01-19 15:55:36 +0000
+++ b/sql/share/errmsg-utf8.txt	2012-02-09 16:44:46 +0000
@@ -5985,9 +5985,8 @@ ER_WRONG_PARTITION_NAME
         eng "Incorrect partition name"
         ger "Falscher Partitionsname"
         swe "Felaktigt partitionsnamn"
-ER_CANT_CHANGE_TX_ISOLATION 25001
-        eng "Transaction isolation level can't be changed while a transaction is in progress"
-        ger "Transaktionsisolationsebene kann während einer laufenden Transaktion nicht geändert werden"
+ER_CANT_CHANGE_TX_CHARACTERISTICS 25001
+        eng "Transaction characteristics can't be changed while a transaction is in progress"
 ER_DUP_ENTRY_AUTOINCREMENT_CASE
         eng "ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '%-.192s' for key '%-.192s'"
         ger "ALTER TABLE führt zur Neusequenzierung von auto_increment, wodurch der doppelte Eintrag '%-.192s' für Schlüssel '%-.192s' auftritt"
@@ -6624,3 +6623,6 @@ ER_INNODB_FT_LIMIT
 
 ER_INNODB_NO_FT_TEMP_TABLE
   eng "Cannot create FULLTEXT index on temporary InnoDB table"
+
+ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+  eng "Cannot execute statement in a READ ONLY transaction."

=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc	2012-01-31 15:16:16 +0000
+++ b/sql/sql_base.cc	2012-02-09 16:44:46 +0000
@@ -2870,6 +2870,14 @@ bool open_table(THD *thd, TABLE_LIST *ta
 
   if (! (flags & MYSQL_OPEN_HAS_MDL_LOCK))
   {
+    /* Check if we're trying to take a write lock in a read only transaction. */
+    if (table_list->mdl_request.type >= MDL_SHARED_WRITE &&
+        thd->tx_read_only)
+    {
+      my_error(ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION, MYF(0));
+      DBUG_RETURN(true);
+    }
+
     /*
       We are not under LOCK TABLES and going to acquire write-lock/
       modify the base table. We need to acquire protection against
@@ -4763,13 +4771,28 @@ lock_table_names(THD *thd,
   for (table= tables_start; table && table != tables_end;
        table= table->next_global)
   {
-    if (table->mdl_request.type < MDL_SHARED_NO_WRITE ||
-        table->open_type == OT_TEMPORARY_ONLY ||
+    if (table->mdl_request.type < MDL_SHARED_NO_WRITE)
+      continue;
+
+    if (table->open_type == OT_TEMPORARY_ONLY ||
         (table->open_type == OT_TEMPORARY_OR_BASE && is_temporary_table(table)))
     {
+      if (table->mdl_request.type == MDL_EXCLUSIVE && thd->tx_read_only)
+      {
+        /* Temporary table DDL is not allowed in a read only transaction. */
+        my_error(ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION, MYF(0));
+        return true;
+      }
       continue;
     }
 
+    /* Write lock on normal tables is not allowed in a read only transaction. */
+    if (thd->tx_read_only)
+    {
+      my_error(ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION, MYF(0));
+      return true;
+    }
+
     if (! (flags & MYSQL_OPEN_SKIP_SCOPED_MDL_LOCK) && schema_set.insert(table))
       return TRUE;
 

=== modified file 'sql/sql_class.cc'
--- a/sql/sql_class.cc	2012-01-31 15:16:16 +0000
+++ b/sql/sql_class.cc	2012-02-09 16:44:46 +0000
@@ -641,6 +641,12 @@ int thd_tx_isolation(const THD *thd)
 }
 
 extern "C"
+int thd_tx_is_read_only(const THD *thd)
+{
+  return (int) thd->tx_read_only;
+}
+
+extern "C"
 void thd_inc_row_count(THD *thd)
 {
   thd->get_stmt_da()->inc_current_row_for_warning();
@@ -1245,6 +1251,7 @@ void THD::init(void)
 			TL_WRITE_LOW_PRIORITY :
 			TL_WRITE);
   tx_isolation= (enum_tx_isolation) variables.tx_isolation;
+  tx_read_only= variables.tx_read_only;
   update_charset();
   reset_current_stmt_binlog_format_row();
   memset(&status_var, 0, sizeof(status_var));

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2012-01-31 15:16:16 +0000
+++ b/sql/sql_class.h	2012-02-09 16:44:46 +0000
@@ -788,6 +788,7 @@ typedef struct system_variables
   */
   my_thread_id pseudo_thread_id;
 
+  my_bool tx_read_only;
   my_bool low_priority_updates;
   my_bool new_mode;
   my_bool query_cache_wlock_invalidate;
@@ -2848,6 +2849,11 @@ public:
     above.
   */
   enum_tx_isolation tx_isolation;
+  /*
+    Current or next transaction access mode.
+    See comment above regarding tx_isolation.
+  */
+  bool              tx_read_only;
   enum_check_fields count_cuted_fields;
 
   DYNAMIC_ARRAY user_var_events;        /* For user variables replication */

=== modified file 'sql/sql_lex.h'
--- a/sql/sql_lex.h	2012-01-05 10:12:47 +0000
+++ b/sql/sql_lex.h	2012-02-09 16:44:46 +0000
@@ -2260,6 +2260,7 @@ struct LEX: public Query_tables_list
   bool drop_if_exists, drop_temporary, local_file, one_shot_set;
   bool autocommit;
   bool verbose, no_write_to_binlog;
+  bool tx_read_only;
 
   enum enum_yes_no_unknown tx_chain, tx_release;
   bool safe_to_cache_query;

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2012-01-31 15:16:16 +0000
+++ b/sql/sql_parse.cc	2012-02-09 16:44:46 +0000
@@ -4002,8 +4002,9 @@ end_with_restore_list:
     }
     else
     {
-      /* Reset the isolation level if no chaining transaction. */
+      /* Reset the isolation level and access mode if no chaining transaction.*/
       thd->tx_isolation= (enum_tx_isolation) thd->variables.tx_isolation;
+      thd->tx_read_only= thd->variables.tx_read_only;
     }
     /* Disconnect the current client connection. */
     if (tx_release)
@@ -4032,8 +4033,9 @@ end_with_restore_list:
     }
     else
     {
-      /* Reset the isolation level if no chaining transaction. */
+      /* Reset the isolation level and access mode if no chaining transaction.*/
       thd->tx_isolation= (enum_tx_isolation) thd->variables.tx_isolation;
+      thd->tx_read_only= thd->variables.tx_read_only;
     }
     /* Disconnect the current client connection. */
     if (tx_release)
@@ -4556,9 +4558,10 @@ create_sp_error:
     thd->mdl_context.release_transactional_locks();
     /*
       We've just done a commit, reset transaction
-      isolation level to the session default.
+      isolation level and access mode to the session default.
     */
     thd->tx_isolation= (enum_tx_isolation) thd->variables.tx_isolation;
+    thd->tx_read_only= thd->variables.tx_read_only;
     my_ok(thd);
     break;
   case SQLCOM_XA_ROLLBACK:
@@ -4567,9 +4570,10 @@ create_sp_error:
     thd->mdl_context.release_transactional_locks();
     /*
       We've just done a rollback, reset transaction
-      isolation level to the session default.
+      isolation level and access mode to the session default.
     */
     thd->tx_isolation= (enum_tx_isolation) thd->variables.tx_isolation;
+    thd->tx_read_only= thd->variables.tx_read_only;
     my_ok(thd);
     break;
   case SQLCOM_XA_RECOVER:

=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc	2012-01-31 15:16:16 +0000
+++ b/sql/sql_table.cc	2012-02-09 16:44:46 +0000
@@ -2049,6 +2049,13 @@ bool mysql_rm_table(THD *thd,TABLE_LIST
 
   DBUG_ENTER("mysql_rm_table");
 
+  /* DDL is not allowed in a read only transaction. */
+  if (thd->tx_read_only)
+  {
+    my_error(ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION, MYF(0));
+    DBUG_RETURN(true);
+  }
+
   /* Disable drop of enabled log tables, must be done before name locking */
   for (table= tables; table; table= table->next_local)
   {

=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy	2011-12-19 12:53:11 +0000
+++ b/sql/sql_yacc.yy	2012-02-09 16:44:46 +0000
@@ -785,6 +785,90 @@ static bool add_create_index (LEX *lex,
 }
 
 
+static void option_type_value_pre(THD *thd)
+{
+  LEX *lex= thd->lex;
+
+  if (lex->sphead)
+  {
+    /*
+      If we are in SP we want have own LEX for each assignment.
+      This is mostly because it is hard for several sp_instr_set
+      and sp_instr_set_trigger instructions share one LEX.
+      (Well, it is theoretically possible but adds some extra
+      overhead on preparation for execution stage and IMO less
+      robust).
+
+      QQ: May be we should simply prohibit group assignments in SP?
+    */
+    LEX *old_lex= lex;
+    lex->sphead->reset_lex(thd);
+    lex= thd->lex;
+
+    /* Set new LEX as if we at start of set rule. */
+    lex->sql_command= SQLCOM_SET_OPTION;
+    mysql_init_select(lex);
+    lex->option_type=OPT_SESSION;
+    lex->var_list.empty();
+    lex->one_shot_set= 0;
+    lex->autocommit= 0;
+    lex->sphead->m_tmp_query= old_lex->sphead->m_tmp_query;
+    lex->option_type= old_lex->option_type;
+  }
+}
+
+
+static bool option_type_value_post(THD *thd, bool is_empty)
+{
+  LEX *lex= thd->lex;
+
+  if (lex->sphead)
+  {
+    sp_head *sp= lex->sphead;
+
+    if (!lex->var_list.is_empty())
+    {
+      /*
+        We have assignment to user or system variable or
+        option setting, so we should construct sp_instr_stmt
+        for it.
+      */
+      LEX_STRING qbuff;
+      sp_instr_stmt *i;
+      Lex_input_stream *lip= &thd->m_parser_state->m_lip;
+
+      if (!(i= new sp_instr_stmt(sp->instructions(), lex->spcont,
+                                 lex)))
+        return true;
+
+      /*
+        Extract the query statement from the tokenizer.  The
+        end is either lip->ptr, if there was no lookahead,
+        lip->tok_end otherwise.
+      */
+      if (is_empty)
+        qbuff.length= lip->get_ptr() - sp->m_tmp_query;
+      else
+        qbuff.length= lip->get_tok_end() - sp->m_tmp_query;
+
+      if (!(qbuff.str= (char*) alloc_root(thd->mem_root,
+                                          qbuff.length + 5)))
+        return true;
+
+      strmake(strmake(qbuff.str, "SET ", 4), sp->m_tmp_query,
+              qbuff.length);
+      qbuff.length+= 4;
+      i->m_query= qbuff;
+      if (sp->add_instr(i))
+        return true;
+    }
+    if (lex->sphead->restore_lex(thd))
+      return true;
+  }
+  return false;
+}
+
+
 %}
 %union {
   int  num;
@@ -849,10 +933,10 @@ bool my_yyoverflow(short **a, YYSTYPE **
 
 %pure_parser                                    /* We have threads */
 /*
-  Currently there are 164 shift/reduce conflicts.
+  Currently there are 163 shift/reduce conflicts.
   We should not introduce new conflicts any more.
 */
-%expect 164
+%expect 163
 
 /*
    Comments for TOKENS.
@@ -1229,6 +1313,7 @@ bool my_yyoverflow(short **a, YYSTYPE **
 %token  ON                            /* SQL-2003-R */
 %token  ONE_SHOT_SYM
 %token  ONE_SYM
+%token  ONLY_SYM
 %token  OPEN_SYM                      /* SQL-2003-R */
 %token  OPTIMIZE
 %token  OPTIONS_SYM
@@ -1525,8 +1610,8 @@ bool my_yyoverflow(short **a, YYSTYPE **
         table_option opt_if_not_exists opt_no_write_to_binlog
         opt_temporary all_or_any opt_distinct
         opt_ignore_leaves fulltext_options spatial_type union_option
-        start_transaction_opts
-        union_opt select_derived_init option_type2
+        start_transaction_opt_list start_transaction_opt transaction_access_mode
+        union_opt select_derived_init
         opt_natural_language_mode opt_query_expansion
         opt_ev_status opt_ev_on_completion ev_on_completion opt_ev_comment
         ev_alter_on_schedule_completion opt_ev_rename_to opt_ev_sql_stmt
@@ -7239,20 +7324,42 @@ slave:
         ;
 
 start:
-          START_SYM TRANSACTION_SYM start_transaction_opts
+          START_SYM TRANSACTION_SYM start_transaction_opt_list
           {
             LEX *lex= Lex;
             lex->sql_command= SQLCOM_BEGIN;
+            /* READ ONLY and READ WRITE are mutually exclusive. */
+            if (($3 & MYSQL_START_TRANS_OPT_READ_WRITE) &&
+                ($3 & MYSQL_START_TRANS_OPT_READ_ONLY))
+            {
+              my_parse_error(ER(ER_SYNTAX_ERROR));
+              MYSQL_YYABORT;
+            }
             lex->start_transaction_opt= $3;
           }
         ;
 
-start_transaction_opts:
+start_transaction_opt_list:
+          start_transaction_opt
+          { $$= $1; }
+        | start_transaction_opt_list ',' start_transaction_opt
+          { $$= $1 | $3; }
+        ;
+
+start_transaction_opt:
           /*empty*/ { $$ = 0; }
         | WITH CONSISTENT_SYM SNAPSHOT_SYM
           {
             $$= MYSQL_START_TRANS_OPT_WITH_CONS_SNAPSHOT;
           }
+        | READ_SYM ONLY_SYM
+          {
+            $$= MYSQL_START_TRANS_OPT_READ_ONLY;
+          }
+        | READ_SYM WRITE_SYM
+          {
+            $$= MYSQL_START_TRANS_OPT_READ_WRITE;
+          }
         ;
 
 slave_connection_opts:
@@ -13307,7 +13414,7 @@ keyword_sp:
 /* Option functions */
 
 set:
-          SET opt_option
+          SET
           {
             LEX *lex=Lex;
             lex->sql_command= SQLCOM_SET_OPTION;
@@ -13316,9 +13423,11 @@ set:
             lex->var_list.empty();
             lex->one_shot_set= 0;
             lex->autocommit= 0;
+            if (Lex->sphead)
+              Lex->sphead->m_tmp_query= YYLIP->get_ptr();
+            option_type_value_pre(YYTHD);
           }
-          option_value_list
-          {}
+          set_alternatives
         ;
 
 opt_option:
@@ -13326,104 +13435,117 @@ opt_option:
         | OPTION {}
         ;
 
-option_value_list:
-          option_type_value
-        | option_value_list ',' option_type_value
-        ;
+set_alternatives:
+          opt_option option_value option_value_list_cont
+        | TRANSACTION_SYM
+          {
+            LEX *lex=Lex;
+            lex->option_type= OPT_DEFAULT;
 
-option_type_value:
+            /* Default values */
+            lex->tx_isolation= ISO_REPEATABLE_READ;
+            lex->tx_read_only= false;
+          }
+          transaction_characteristics
           {
             THD *thd= YYTHD;
-            LEX *lex= thd->lex;
-            Lex_input_stream *lip= YYLIP;
-
-            if (lex->sphead)
-            {
-              /*
-                If we are in SP we want have own LEX for each assignment.
-                This is mostly because it is hard for several sp_instr_set
-                and sp_instr_set_trigger instructions share one LEX.
-                (Well, it is theoretically possible but adds some extra
-                overhead on preparation for execution stage and IMO less
-                robust).
-
-                QQ: May be we should simply prohibit group assignments in SP?
-              */
-              lex->sphead->reset_lex(thd);
-              lex= thd->lex;
+            LEX *lex=Lex;
+            Item *isolation_item=
+              new (thd->mem_root) Item_int((int32) lex->tx_isolation);
+            Item *accessmode_item=
+              new (thd->mem_root) Item_int((int32) lex->tx_read_only);
+            if (isolation_item == NULL || accessmode_item == NULL)
+              MYSQL_YYABORT;
+            set_var *isolation_var= new set_var(lex->option_type,
+                                                find_sys_var(thd, "tx_isolation"),
+                                                &null_lex_str,
+                                                isolation_item);
+            set_var *accessmode_var= new set_var(lex->option_type,
+                                                 find_sys_var(thd, "tx_read_only"),
+                                                 &null_lex_str,
+                                                 accessmode_item);
+            if (isolation_var == NULL || accessmode_var == NULL)
+              MYSQL_YYABORT;
+            lex->var_list.push_back(isolation_var);
+            lex->var_list.push_back(accessmode_var);
+          }
+        | opt_option option_type
+          {
+            Lex->option_type= $2;
+          }
+          set_type_alternatives
+        ;
 
-              /* Set new LEX as if we at start of set rule. */
-              lex->sql_command= SQLCOM_SET_OPTION;
-              mysql_init_select(lex);
-              lex->option_type=OPT_SESSION;
-              lex->var_list.empty();
-              lex->one_shot_set= 0;
-              lex->autocommit= 0;
-              lex->sphead->m_tmp_query= lip->get_tok_start();
-            }
+set_type_alternatives:
+          option_type_value option_value_list_cont
+        | TRANSACTION_SYM
+          {
+            /* Default values */
+            YYTHD->lex->tx_isolation= ISO_REPEATABLE_READ;
+            YYTHD->lex->tx_read_only= false;
           }
-          ext_option_value
+          transaction_characteristics
           {
             THD *thd= YYTHD;
-            LEX *lex= thd->lex;
-            Lex_input_stream *lip= YYLIP;
-
-            if (lex->sphead)
-            {
-              sp_head *sp= lex->sphead;
-
-              if (!lex->var_list.is_empty())
-              {
-                /*
-                  We have assignment to user or system variable or
-                  option setting, so we should construct sp_instr_stmt
-                  for it.
-                */
-                LEX_STRING qbuff;
-                sp_instr_stmt *i;
-
-                if (!(i= new sp_instr_stmt(sp->instructions(), lex->spcont,
-                                           lex)))
-                  MYSQL_YYABORT;
+            LEX *lex=Lex;
+            Item *isolation_item=
+              new (thd->mem_root) Item_int((int32) lex->tx_isolation);
+            Item *accessmode_item=
+              new (thd->mem_root) Item_int((int32) lex->tx_read_only);
+            if (isolation_item == NULL || accessmode_item == NULL)
+              MYSQL_YYABORT;
+            set_var *isolation_var= new set_var(lex->option_type,
+                                                find_sys_var(thd, "tx_isolation"),
+                                                &null_lex_str,
+                                                isolation_item);
+            set_var *accessmode_var= new set_var(lex->option_type,
+                                                 find_sys_var(thd, "tx_read_only"),
+                                                 &null_lex_str,
+                                                 accessmode_item);
+            if (isolation_var == NULL || accessmode_var == NULL)
+              MYSQL_YYABORT;
+            lex->var_list.push_back(isolation_var);
+            lex->var_list.push_back(accessmode_var);
+          }
+        ;
 
-                /*
-                  Extract the query statement from the tokenizer.  The
-                  end is either lip->ptr, if there was no lookahead,
-                  lip->tok_end otherwise.
-                */
-                if (yychar == YYEMPTY)
-                  qbuff.length= lip->get_ptr() - sp->m_tmp_query;
-                else
-                  qbuff.length= lip->get_tok_end() - sp->m_tmp_query;
+option_value_list_cont:
+          /* empty */
+        | ','
+          {
+            if (Lex->sphead)
+              Lex->sphead->m_tmp_query= YYLIP->get_tok_start();
+          }
+          option_value_list
+        ;
 
-                if (!(qbuff.str= (char*) alloc_root(thd->mem_root,
-                                                    qbuff.length + 5)))
-                  MYSQL_YYABORT;
+option_value_list:
+          { option_type_value_pre(YYTHD); }
+          ext_opt_value
+        | option_value_list ','
+          {
+            if (Lex->sphead)
+              Lex->sphead->m_tmp_query= YYLIP->get_tok_start();
+            option_type_value_pre(YYTHD);
+          }
+          ext_opt_value
+        ;
 
-                strmake(strmake(qbuff.str, "SET ", 4), sp->m_tmp_query,
-                        qbuff.length);
-                qbuff.length+= 4;
-                i->m_query= qbuff;
-                if (sp->add_instr(i))
-                  MYSQL_YYABORT;
-              }
-              if (lex->sphead->restore_lex(thd))
-                MYSQL_YYABORT;
-            }
+ext_opt_value:
+          option_type
+          {
+            Lex->option_type= $1;
           }
+          option_type_value
+        | option_value
         ;
 
 option_type:
-          option_type2    {}
-        | GLOBAL_SYM  { $$=OPT_GLOBAL; }
+          GLOBAL_SYM  { $$=OPT_GLOBAL; }
         | LOCAL_SYM   { $$=OPT_SESSION; }
         | SESSION_SYM { $$=OPT_SESSION; }
         ;
 
-option_type2:
-          /* empty */ { $$= OPT_DEFAULT; }
-        ;
-
 opt_var_type:
           /* empty */ { $$=OPT_SESSION; }
         | GLOBAL_SYM  { $$=OPT_GLOBAL; }
@@ -13438,36 +13560,45 @@ opt_var_ident_type:
         | SESSION_SYM '.' { $$=OPT_SESSION; }
         ;
 
-ext_option_value:
-          sys_option_value
-        | option_type2 option_value
+option_type_value:
+          internal_variable_name equal set_expr_or_default
+          {
+            THD *thd= YYTHD;
+            LEX *lex= Lex;
+
+            if ($1.var && $1.var != trg_new_row_fake_var)
+            {
+              /* It is a system variable. */
+              if (set_system_variable(thd, &$1, lex->option_type, $3))
+                MYSQL_YYABORT;
+            }
+            else
+            {
+              my_parse_error(ER(ER_SYNTAX_ERROR));
+              MYSQL_YYABORT;
+            }
+            if (option_type_value_post(thd, yychar == YYEMPTY))
+              MYSQL_YYABORT;
+          }
         ;
 
-sys_option_value:
-          option_type internal_variable_name equal set_expr_or_default
+option_value:
+          internal_variable_name equal set_expr_or_default
           {
             THD *thd= YYTHD;
             LEX *lex= Lex;
-            LEX_STRING *name= &$2.base_name;
+            LEX_STRING *name= &$1.base_name;
 
-            if ($2.var == trg_new_row_fake_var)
+            if ($1.var == trg_new_row_fake_var)
             {
               /* We are in trigger and assigning value to field of new row */
-              if ($1)
-              {
-                my_parse_error(ER(ER_SYNTAX_ERROR));
-                MYSQL_YYABORT;
-              }
-              if (set_trigger_new_row(YYTHD, name, $4))
+              if (set_trigger_new_row(YYTHD, name, $3))
                 MYSQL_YYABORT;
             }
-            else if ($2.var)
+            else if ($1.var)
             {
-              if ($1)
-                lex->option_type= $1;
-
               /* It is a system variable. */
-              if (set_system_variable(thd, &$2, lex->option_type, $4))
+              if (set_system_variable(thd, &$1, lex->option_type, $3))
                 MYSQL_YYABORT;
             }
             else
@@ -13475,37 +13606,14 @@ sys_option_value:
               sp_pcontext *spc= lex->spcont;
               sp_variable *spv= spc->find_variable(*name, false);
 
-              if ($1)
-              {
-                my_parse_error(ER(ER_SYNTAX_ERROR));
-                MYSQL_YYABORT;
-              }
-
               /* It is a local variable. */
-              if (set_local_variable(thd, spv, $4))
+              if (set_local_variable(thd, spv, $3))
                 MYSQL_YYABORT;
             }
-          }
-        | option_type TRANSACTION_SYM ISOLATION LEVEL_SYM isolation_types
-          {
-            THD *thd= YYTHD;
-            LEX *lex=Lex;
-            lex->option_type= $1;
-            Item *item= new (thd->mem_root) Item_int((int32) $5);
-            if (item == NULL)
-              MYSQL_YYABORT;
-            set_var *var= new set_var(lex->option_type,
-                                      find_sys_var(thd, "tx_isolation"),
-                                      &null_lex_str,
-                                      item);
-            if (var == NULL)
+            if (option_type_value_post(thd, yychar == YYEMPTY))
               MYSQL_YYABORT;
-            lex->var_list.push_back(var);
           }
-        ;
-
-option_value:
-          '@' ident_or_text equal expr
+        | '@' ident_or_text equal expr
           {
             Item_func_set_user_var *item;
             item= new (YYTHD->mem_root) Item_func_set_user_var($2, $4);
@@ -13515,6 +13623,8 @@ option_value:
             if (var == NULL)
               MYSQL_YYABORT;
             Lex->var_list.push_back(var);
+            if (option_type_value_post(YYTHD, yychar == YYEMPTY))
+              MYSQL_YYABORT;
           }
         | '@' '@' opt_var_ident_type internal_variable_name equal set_expr_or_default
           {
@@ -13528,6 +13638,8 @@ option_value:
             }
             if (set_system_variable(thd, &tmp, $3, $6))
               MYSQL_YYABORT;
+            if (option_type_value_post(thd, yychar == YYEMPTY))
+              MYSQL_YYABORT;
           }
         | charset old_or_new_charset_name_or_default
           {
@@ -13544,6 +13656,8 @@ option_value:
             if (var == NULL)
               MYSQL_YYABORT;
             lex->var_list.push_back(var);
+            if (option_type_value_post(thd, yychar == YYEMPTY))
+              MYSQL_YYABORT;
           }
         | NAMES_SYM equal expr
           {
@@ -13581,6 +13695,8 @@ option_value:
             if (var == NULL)
               MYSQL_YYABORT;
             lex->var_list.push_back(var);
+            if (option_type_value_post(YYTHD, yychar == YYEMPTY))
+              MYSQL_YYABORT;
           }
         | PASSWORD equal text_or_password
           {
@@ -13609,6 +13725,8 @@ option_value:
             thd->lex->autocommit= TRUE;
             if (lex->sphead)
               lex->sphead->m_flags|= sp_head::HAS_SET_AUTOCOMMIT_STMT;
+            if (option_type_value_post(thd, yychar == YYEMPTY))
+              MYSQL_YYABORT;
           }
         | PASSWORD FOR_SYM user equal text_or_password
           {
@@ -13619,6 +13737,8 @@ option_value:
             Lex->autocommit= TRUE;
             if (Lex->sphead)
               Lex->sphead->m_flags|= sp_head::HAS_SET_AUTOCOMMIT_STMT;
+            if (option_type_value_post(YYTHD, yychar == YYEMPTY))
+              MYSQL_YYABORT;
           }
         ;
 
@@ -13706,6 +13826,30 @@ internal_variable_name:
           }
         ;
 
+transaction_characteristics:
+          transaction_mode
+        | transaction_characteristics ',' transaction_mode
+        ;
+
+transaction_mode:
+          transaction_access_mode
+        | ISOLATION LEVEL_SYM isolation_types
+          {
+            YYTHD->lex->tx_isolation= $3;
+          }
+        ;
+
+transaction_access_mode:
+          READ_SYM ONLY_SYM
+          {
+            YYTHD->lex->tx_read_only= true;
+          }
+        | READ_SYM WRITE_SYM
+          {
+            YYTHD->lex->tx_read_only= false;
+          }
+        ;
+
 isolation_types:
           READ_SYM UNCOMMITTED_SYM { $$= ISO_READ_UNCOMMITTED; }
         | READ_SYM COMMITTED_SYM   { $$= ISO_READ_COMMITTED; }

=== modified file 'sql/sys_vars.cc'
--- a/sql/sys_vars.cc	2012-02-02 16:56:36 +0000
+++ b/sql/sys_vars.cc	2012-02-09 16:44:46 +0000
@@ -2681,7 +2681,7 @@ static bool check_tx_isolation(sys_var *
   if (var->type == OPT_DEFAULT && thd->in_active_multi_stmt_transaction())
   {
     DBUG_ASSERT(thd->in_multi_stmt_transaction_mode());
-    my_error(ER_CANT_CHANGE_TX_ISOLATION, MYF(0));
+    my_error(ER_CANT_CHANGE_TX_CHARACTERISTICS, MYF(0));
     return TRUE;
   }
   return FALSE;
@@ -2720,6 +2720,42 @@ static Sys_var_tx_isolation Sys_tx_isola
        tx_isolation_names, DEFAULT(ISO_REPEATABLE_READ),
        NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(check_tx_isolation));
 
+
+/**
+  Can't change the tx_read_only state if we are already in a
+  transaction.
+*/
+
+static bool check_tx_read_only(sys_var *self, THD *thd, set_var *var)
+{
+  if (var->type == OPT_DEFAULT && thd->in_active_multi_stmt_transaction())
+  {
+    DBUG_ASSERT(thd->in_multi_stmt_transaction_mode());
+    my_error(ER_CANT_CHANGE_TX_CHARACTERISTICS, MYF(0));
+    return true;
+  }
+  return false;
+}
+
+
+bool Sys_var_tx_read_only::session_update(THD *thd, set_var *var)
+{
+  if (var->type == OPT_SESSION && Sys_var_mybool::session_update(thd, var))
+    return true;
+  if (var->type == OPT_DEFAULT || !thd->in_active_multi_stmt_transaction())
+  {
+    // @see Sys_var_tx_isolation::session_update() above for the rules.
+    thd->tx_read_only= var->save_result.ulonglong_value;
+  }
+  return false;
+}
+
+
+static Sys_var_tx_read_only Sys_tx_read_only(
+       "tx_read_only", "Set transactions as read only.",
+       SESSION_VAR(tx_read_only), NO_CMD_LINE, DEFAULT(0),
+       NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(check_tx_read_only));
+
 static Sys_var_ulonglong Sys_tmp_table_size(
        "tmp_table_size",
        "If an internal in-memory temporary table exceeds this size, MySQL "

=== modified file 'sql/sys_vars.h'
--- a/sql/sys_vars.h	2011-12-02 14:19:01 +0000
+++ b/sql/sys_vars.h	2012-02-09 16:44:46 +0000
@@ -1755,6 +1755,22 @@ public:
   virtual bool session_update(THD *thd, set_var *var);
 };
 
+
+class Sys_var_tx_read_only: public Sys_var_mybool
+{
+public:
+  Sys_var_tx_read_only(const char *name_arg, const char *comment, int flag_args,
+                       ptrdiff_t off, size_t size, CMD_LINE getopt,
+                       my_bool def_val, PolyLock *lock,
+                       enum binlog_status_enum binlog_status_arg,
+                       on_check_function on_check_func)
+    :Sys_var_mybool(name_arg, comment, flag_args, off, size, getopt,
+                    def_val, lock, binlog_status_arg, on_check_func)
+  {}
+  virtual bool session_update(THD *thd, set_var *var);
+};
+
+
 /**
    A class for @@global.binlog_checksum that has
    a specialized update method.

=== modified file 'sql/transaction.cc'
--- a/sql/transaction.cc	2011-06-09 08:58:41 +0000
+++ b/sql/transaction.cc	2012-02-09 16:44:46 +0000
@@ -18,6 +18,7 @@
 #include "transaction.h"
 #include "rpl_handler.h"
 #include "debug_sync.h"         // DEBUG_SYNC
+#include "sql_acl.h"            // SUPER_ACL
 
 /* Conditions under which the transaction state must not change. */
 static bool trans_check(THD *thd)
@@ -130,7 +131,8 @@ bool trans_begin(THD *thd, uint flags)
       (thd->variables.option_bits & OPTION_TABLE_LOCK))
   {
     thd->variables.option_bits&= ~OPTION_TABLE_LOCK;
-    thd->server_status&= ~SERVER_STATUS_IN_TRANS;
+    thd->server_status&=
+      ~(SERVER_STATUS_IN_TRANS | SERVER_STATUS_IN_TRANS_READONLY);
     res= test(ha_commit_trans(thd, TRUE));
   }
 
@@ -152,6 +154,31 @@ bool trans_begin(THD *thd, uint flags)
   if (flags & MYSQL_START_TRANS_OPT_WITH_CONS_SNAPSHOT)
     res= ha_start_consistent_snapshot(thd);
 
+  // The RO/RW options are mutually exclusive.
+  if (flags & MYSQL_START_TRANS_OPT_READ_ONLY)
+  {
+    thd->tx_read_only= true;
+    thd->server_status|= SERVER_STATUS_IN_TRANS_READONLY;
+  }
+  else if (flags & MYSQL_START_TRANS_OPT_READ_WRITE)
+  {
+    /*
+      Explicitly starting a RW transaction when the server is in
+      read-only mode, is not allowed unless the user has SUPER priv.
+      Implicitly starting a RW transaction is allowed for backward
+      compatibility.
+    */
+    const my_bool user_is_super=
+      ((ulong)(thd->security_ctx->master_access & SUPER_ACL) ==
+       (ulong)SUPER_ACL);
+    if (opt_readonly && !user_is_super)
+    {
+      my_error(ER_OPTION_PREVENTS_STATEMENT, MYF(0), "--read-only");
+      DBUG_RETURN(true);
+    }
+    thd->tx_read_only= false;
+  }
+
   DBUG_RETURN(test(res));
 }
 
@@ -173,7 +200,8 @@ bool trans_commit(THD *thd)
   if (trans_check(thd))
     DBUG_RETURN(TRUE);
 
-  thd->server_status&= ~SERVER_STATUS_IN_TRANS;
+  thd->server_status&=
+    ~(SERVER_STATUS_IN_TRANS | SERVER_STATUS_IN_TRANS_READONLY);
   res= ha_commit_trans(thd, TRUE);
   /*
     if res is non-zero, then ha_commit_trans has rolled back the
@@ -216,7 +244,8 @@ bool trans_commit_implicit(THD *thd)
     /* Safety if one did "drop table" on locked tables */
     if (!thd->locked_tables_mode)
       thd->variables.option_bits&= ~OPTION_TABLE_LOCK;
-    thd->server_status&= ~SERVER_STATUS_IN_TRANS;
+    thd->server_status&=
+      ~(SERVER_STATUS_IN_TRANS | SERVER_STATUS_IN_TRANS_READONLY);
     res= test(ha_commit_trans(thd, TRUE));
   }
 
@@ -252,7 +281,8 @@ bool trans_rollback(THD *thd)
   if (trans_check(thd))
     DBUG_RETURN(TRUE);
 
-  thd->server_status&= ~SERVER_STATUS_IN_TRANS;
+  thd->server_status&=
+    ~(SERVER_STATUS_IN_TRANS | SERVER_STATUS_IN_TRANS_READONLY);
   res= ha_rollback_trans(thd, TRUE);
   (void) RUN_HOOK(transaction, after_rollback, (thd, FALSE));
   thd->variables.option_bits&= ~OPTION_BEGIN;
@@ -718,7 +748,8 @@ bool trans_xa_commit(THD *thd)
 
   thd->variables.option_bits&= ~OPTION_BEGIN;
   thd->transaction.all.reset_unsafe_rollback_flags();
-  thd->server_status&= ~SERVER_STATUS_IN_TRANS;
+  thd->server_status&=
+    ~(SERVER_STATUS_IN_TRANS | SERVER_STATUS_IN_TRANS_READONLY);
   xid_cache_delete(&thd->transaction.xid_state);
   thd->transaction.xid_state.xa_state= XA_NOTR;
 
@@ -765,7 +796,8 @@ bool trans_xa_rollback(THD *thd)
 
   thd->variables.option_bits&= ~OPTION_BEGIN;
   thd->transaction.all.reset_unsafe_rollback_flags();
-  thd->server_status&= ~SERVER_STATUS_IN_TRANS;
+  thd->server_status&=
+    ~(SERVER_STATUS_IN_TRANS | SERVER_STATUS_IN_TRANS_READONLY);
   xid_cache_delete(&thd->transaction.xid_state);
   thd->transaction.xid_state.xa_state= XA_NOTR;
 

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk-wl5968 branch (jon.hauglid:3836 to 3837) WL#5968Jon Olav Hauglid10 Feb