Hybrid Query Cache messed up transaction settings

[Weishiun Tsai]

With HYBRID QUERY CACHE turned on, SET TRANSACTION READ ONLY ends up having no effect. As shown in the following example, the 2nd insert statement was expecting error 3141, but the insert went through even after the TRANSACTION was set to READ ONLY. This problem goes away once we turn the CQD HYBRID QUERY CACHE to ‘OFF’.

This is seen on the v0110_0830 build. It is a regression introduced sometime around the end of December.

----------------------------------------------

Here is the entire script to reproduce it:

drop schema mytest cascade;
create schema mytest;
set schema mytest;
create table t (a int);
insert into t values (1);
SET TRANSACTION READ ONLY;
insert into t values (2);
select * from t;

----------------------------------------------

Here is the execution output with the CQD HYBRID_QUERY_CACHE turned on (it’s on by default)

>>drop schema mytest cascade;

*** ERROR[1003] Schema TRAFODION.MYTEST does not exist.

--- SQL operation failed with errors.
>>create schema mytest;

--- SQL operation complete.
>>set schema mytest;

--- SQL operation complete.
>>create table t (a int);

--- SQL operation complete.
>>insert into t values (1);

--- 1 row(s) inserted.
>>SET TRANSACTION READ ONLY;

--- SQL operation complete.
>>insert into t values (2);

--- 1 row(s) inserted.
>>select * from t;

A
-----------

          1
          2

--- 2 row(s) selected.

----------------------------------------------

Here is the execution output with the CQD HYBRID_QUERY_CACHE turned off

>>cqd HYBRID_QUERY_CACHE 'OFF';

--- SQL operation complete.
>>drop schema mytest cascade;

--- SQL operation complete.
>>create schema mytest;

--- SQL operation complete.
>>set schema mytest;

--- SQL operation complete.
>>create table t (a int);

--- SQL operation complete.
>>insert into t values (1);

--- 1 row(s) inserted.
>>SET TRANSACTION READ ONLY;

--- SQL operation complete.
>>insert into t values (2);

*** ERROR[3141] The transaction access mode cannot be READ ONLY for an INSERT, UPDATE, DELETE, or DDL statement.

*** ERROR[8822] The statement was not prepared.

>>select * from t;

A
-----------

          1

--- 1 row(s) selected.

[Julie Thai (julie-y-thai)]

Verified fix on qinhao_hqc_utt_0124:

>>drop schema mytest cascade;

*** ERROR[1003] Schema TRAFODION.MYTEST does not exist.

--- SQL operation failed with errors.
>>create schema mytest;

--- SQL operation complete.
>>set schema mytest;

--- SQL operation complete.
>>create table t (a int);

--- SQL operation complete.
>>insert into t values (1);

--- 1 row(s) inserted.
>>SET TRANSACTION READ ONLY;

--- SQL operation complete.
>>insert into t values (2);

*** ERROR[3141] The transaction access mode cannot be READ ONLY for an INSERT, UPDATE, DELETE, or DDL statement.

*** ERROR[8822] The statement was not prepared.

>>select * from t;

A
-----------

          1

--- 1 row(s) selected.

Also verified on pcec_hqc_utt:

>>drop schema mytest cascade;

--- SQL operation complete.
>>create schema mytest;

--- SQL operation complete.
>>set schema mytest;

--- SQL operation complete.
>>create table t (a int);

--- SQL operation complete.
>>insert into t values (1);

--- 1 row(s) inserted.
>>SET TRANSACTION READ ONLY;

--- SQL operation complete.
>>insert into t values (2);

*** ERROR[3141] The transaction access mode cannot be READ ONLY for an INSERT, UPDATE, DELETE, or DDL statement.

*** ERROR[8822] The statement was not prepared.

>>select * from t;

A
-----------

          1

--- 1 row(s) selected.
>>exit;

[Julie Thai (julie-y-thai)]

verified fix on hqc_utt_0304:

MY_SQROOT=/opt/home/trafodion/hqc_utt_0304
who@host=trafodion@amethyst-hn1
JAVA_HOME=/usr/java/jdk1.7.0_67
linux=2.6.32-279.el6.x86_64
redhat=6.3
Release 1.1.0 (Build release [qinhao], branch 087e0e4-hqc-bug-fix, date 04Mar15)

From TRAFCI:
SQL>drop schema mytest cascade;

*** ERROR[1003] Schema TRAFODION.MYTEST does not exist. [2015-03-09 20:22:45]

SQL>create schema mytest;

--- SQL operation complete.

SQL>set schema mytest;

--- SQL operation complete.

SQL>create table t (a int);

--- SQL operation complete.

SQL>insert into t values (1);

--- 1 row(s) inserted.

SQL>SET TRANSACTION READ ONLY;

--- SQL operation complete.

SQL>insert into t values (2);

*** ERROR[3141] The transaction access mode cannot be READ ONLY for an INSERT, UPDATE, DELETE, or DDL statement. [2015-03-09 20:22:48]

SQL>select * from t;

A
-----------
          1

--- 1 row(s) selected.

[Weishiun Tsai (wei-shiun-tsai)]

Verified on the v0324 build installed on a workstation. This problem has bee fixed:

>>create schema mytest;

--- SQL operation complete.
>>set schema mytest;

--- SQL operation complete.
>>create table t (a int);

--- SQL operation complete.
>>insert into t values (1);

--- 1 row(s) inserted.
>>SET TRANSACTION READ ONLY;

--- SQL operation complete.
>>insert into t values (2);

*** ERROR[3141] The transaction access mode cannot be READ ONLY for an INSERT, UPDATE, DELETE, or DDL statement.

*** ERROR[8822] The statement was not prepared.

>>select * from t;

A
-----------

          1

--- 1 row(s) selected.