This is an automated email from the ASF dual-hosted git repository.
selva pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/trafodion.git
The following commit(s) were added to refs/heads/master by this push:
new 243b618 [TRAFODION-3311] Trafodion to start transactions for select
statements with FOR UPDATE
new 4fe4d2f Merge pull request #1843 from selvaganesang/trafodion-3311
243b618 is described below
commit 243b6184719d9fa2195b854859c7d7589af7921f
Author: selvaganesang <[email protected]>
AuthorDate: Thu May 23 17:55:03 2019 +0000
[TRAFODION-3311] Trafodion to start transactions for select statements with
FOR UPDATE
A CQD BEGIN_TRANSACTION_FOR_SELECT is introduced. This CQD can take a value
1,2 or any numeric value. Default is 1.
1 - means mark the statement as transaction required if it is SELECT ...
FOR UPDATE
2 - means mark the statement as transaction required if it is SELECT
statement with
isolation other than SKIP CONFLICT ACCESS or READ UNCOMMITTED ACCESS.
Any other value - SELECT statement is not marked to begin transaction.
Trafodion always change the isolation level to READ COMMITTED ACCESS when
it is
READ UNCOMMITTED ACCESS.
---
core/sql/generator/GenPreCode.cpp | 16 ++
core/sql/regress/core/EXPECTED116 | 298 ++++++++++++++++++++++++++++++------
core/sql/regress/core/TEST116 | 38 +++++
core/sql/sqlcomp/DefaultConstants.h | 6 +
core/sql/sqlcomp/nadefaults.cpp | 1 +
5 files changed, 311 insertions(+), 48 deletions(-)
diff --git a/core/sql/generator/GenPreCode.cpp
b/core/sql/generator/GenPreCode.cpp
index be80b05..0296d1d 100644
--- a/core/sql/generator/GenPreCode.cpp
+++ b/core/sql/generator/GenPreCode.cpp
@@ -4235,6 +4235,22 @@ RelExpr * FileScan::preCodeGen(Generator * generator,
getGroupAttr()->getCharacteristicInputs());
generator->oltOptInfo()->mayDisableOperStats(&oltOptInfo());
+ if (isHbaseTable() || isSeabaseTable()) {
+ int beginTransForSelect =
ActiveSchemaDB()->getDefaults().getAsLong(BEGIN_TRANSACTION_FOR_SELECT);
+ switch (beginTransForSelect) {
+ case 2:
+ if (accessOptions().accessType() != TransMode::SKIP_CONFLICT_ACCESS_
+ && accessOptions().accessType() !=
TransMode::READ_UNCOMMITTED_ACCESS_)
+ generator->setTransactionFlag(TRUE);
+ // no break here because the transaction is required for updatable
select when it is 2
+ case 1:
+ if (generator->updatableSelect())
+ generator->setTransactionFlag(TRUE);
+ break;
+ default:
+ break;
+ }
+ }
markAsPreCodeGenned();
return this;
} // FileScan::preCodeGen()
diff --git a/core/sql/regress/core/EXPECTED116
b/core/sql/regress/core/EXPECTED116
index c423f1a..36c09c0 100644
--- a/core/sql/regress/core/EXPECTED116
+++ b/core/sql/regress/core/EXPECTED116
@@ -442,7 +442,7 @@ A1 C1 B1
>>invoke t116t1;
-- Definition of Trafodion table TRAFODION.SCH.T116T1
--- Definition current Wed May 16 06:17:20 2018
+-- Definition current Thu May 23 14:49:17 2019
(
SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -460,7 +460,7 @@ A1 C1 B1
>>invoke t116t1;
-- Definition of Trafodion table TRAFODION.SCH.T116T1
--- Definition current Wed May 16 06:17:20 2018
+-- Definition current Thu May 23 14:49:17 2019
(
SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -475,7 +475,7 @@ A1 C1 B1
>>invoke t116v1;
-- Definition of Trafodion view TRAFODION.SCH.T116V1
--- Definition current Wed May 16 06:17:22 2018
+-- Definition current Thu May 23 14:49:18 2019
(
A INT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -492,7 +492,7 @@ A1 C1 B1
>>invoke t116t1;
-- Definition of Trafodion table TRAFODION.SCH.T116T1
--- Definition current Wed May 16 06:17:36 2018
+-- Definition current Thu May 23 14:49:22 2019
(
SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -507,7 +507,7 @@ A1 C1 B1
>>invoke t116v1;
-- Definition of Trafodion view TRAFODION.SCH.T116V1
--- Definition current Wed May 16 06:17:37 2018
+-- Definition current Thu May 23 14:49:22 2019
(
A INT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -524,7 +524,7 @@ A1 C1 B1
>>invoke t116t1;
-- Definition of Trafodion table TRAFODION.SCH.T116T1
--- Definition current Wed May 16 06:17:42 2018
+-- Definition current Thu May 23 14:49:23 2019
(
SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -539,7 +539,7 @@ A1 C1 B1
>>invoke t116v1;
-- Definition of Trafodion view TRAFODION.SCH.T116V1
--- Definition current Wed May 16 06:17:43 2018
+-- Definition current Thu May 23 14:49:24 2019
(
A INT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -557,7 +557,7 @@ A1 C1 B1
>>invoke t116t1;
-- Definition of Trafodion table TRAFODION.SCH.T116T1
--- Definition current Wed May 16 06:17:43 2018
+-- Definition current Thu May 23 14:49:24 2019
(
SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -572,7 +572,7 @@ A1 C1 B1
>>invoke t116v1;
-- Definition of Trafodion view TRAFODION.SCH.T116V1
--- Definition current Wed May 16 06:17:43 2018
+-- Definition current Thu May 23 14:49:24 2019
(
A INT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -589,7 +589,7 @@ A1 C1 B1
>>invoke t116t1;
-- Definition of Trafodion table TRAFODION.SCH.T116T1
--- Definition current Wed May 16 06:17:58 2018
+-- Definition current Thu May 23 14:49:28 2019
(
SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -604,7 +604,7 @@ A1 C1 B1
>>invoke t116v1;
-- Definition of Trafodion view TRAFODION.SCH.T116V1
--- Definition current Wed May 16 06:17:59 2018
+-- Definition current Thu May 23 14:49:28 2019
(
A INT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -622,7 +622,7 @@ A1 C1 B1
>>invoke t116t1;
-- Definition of Trafodion table TRAFODION.SCH.T116T1
--- Definition current Wed May 16 06:18:04 2018
+-- Definition current Thu May 23 14:49:29 2019
(
SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -637,7 +637,7 @@ A1 C1 B1
>>invoke t116v1;
-- Definition of Trafodion view TRAFODION.SCH.T116V1
--- Definition current Wed May 16 06:18:05 2018
+-- Definition current Thu May 23 14:49:29 2019
(
A INT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -661,8 +661,8 @@ A1 C1 B1
--- SQL operation complete.
>>
>>sh sqlci -iTEST116\(concurrent_ddl\) >> LOG116;
-Apache Trafodion Conversational Interface 2.3.0
-Copyright (c) 2015-2017 Apache Software Foundation
+Apache Trafodion Conversational Interface 2.4.0
+Copyright (c) 2015-2018 Apache Software Foundation
>>?section concurrent_ddl
>>cqd ddl_transactions 'ON';
@@ -696,7 +696,7 @@ End of MXCI Session
>>invoke t116t1;
-- Definition of Trafodion table TRAFODION.SCH.T116T1
--- Definition current Wed May 16 06:18:30 2018
+-- Definition current Thu May 23 14:49:47 2019
(
SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -831,7 +831,7 @@ T116T1
>>invoke t116t2;
-- Definition of Trafodion table TRAFODION.SCH.T116T2
--- Definition current Wed May 16 06:19:38 2018
+-- Definition current Thu May 23 14:50:31 2019
(
SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -857,7 +857,7 @@ T116T1
>>invoke t116t2;
-- Definition of Trafodion table TRAFODION.SCH.T116T2
--- Definition current Wed May 16 06:19:49 2018
+-- Definition current Thu May 23 14:50:40 2019
(
SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -985,8 +985,8 @@ T116T1
>>
>>-- insert a row using a concurrent hbase xn
>>sh sqlci -iTEST116\(concurrent_hbase_xn_ins\) >> LOG116;
-Apache Trafodion Conversational Interface 2.3.0
-Copyright (c) 2015-2017 Apache Software Foundation
+Apache Trafodion Conversational Interface 2.4.0
+Copyright (c) 2015-2018 Apache Software Foundation
>>?section concurrent_hbase_xn_ins
>>cqd traf_use_region_xn 'OFF';
@@ -1038,8 +1038,8 @@ A B
>>
>>-- insert a row using a concurrent region xn
>>sh sqlci -iTEST116\(concurrent_region_xn_ins\) >> LOG116;
-Apache Trafodion Conversational Interface 2.3.0
-Copyright (c) 2015-2017 Apache Software Foundation
+Apache Trafodion Conversational Interface 2.4.0
+Copyright (c) 2015-2018 Apache Software Foundation
>>?section concurrent_region_xn_ins
>>cqd traf_use_region_xn 'ON';
@@ -1068,8 +1068,8 @@ End of MXCI Session
>>-- should show conflict
>>commit work;
-*** ERROR[8616] A conflict was detected during commit processing. Transaction
has been aborted. Detail :org.trafodion.dtm.TransactionManagerException:
org.apache.hadoop.hbase.coprocessor.transactional.CommitConflictException:
java.io.IOException: This Transaction [[transactionId: 4642 regionTX: false
status: PENDING neverReadOnly: false scan Size: 2 write Size: 1 startSQ: 2
prepareEditSize: 0 endEditSize: 0 editSize: 1]] has a scan, scanRange[startRow:
\x80\x00\x00\x01, endRow: \x80\x00\ [...]
-org.trafodion.dtm.HBaseTxClient.prepareCommit(HBaseTxClient.java:525)
+*** ERROR[8616] A conflict was detected during commit processing. Transaction
has been aborted. Detail :org.trafodion.dtm.TransactionManagerException:
org.apache.hadoop.hbase.coprocessor.transactional.CommitConflictException:
java.io.IOException: This Transaction [[transactionId: 8126 regionTX: false
status: PENDING neverReadOnly: false scan Size: 2 write Size: 1 startSQ: 2
prepareEditSize: 0 endEditSize: 0 editSize: 1]] has a scan, scanRange[startRow:
\x80\x00\x00\x01, endRow: \x80\x00\ [...]
+org.trafodion.dtm.HBaseTxClient.prepareCommit(HBaseTxClient.java:528)ZZZZZZZ?
--- SQL operation failed with errors.
>>select * from t116t6;
@@ -1094,8 +1094,8 @@ A B
>>
>>-- upsert a row using a concurrent hbase xn
>>sh sqlci -iTEST116\(concurrent_hbase_xn_ups\) >> LOG116;
-Apache Trafodion Conversational Interface 2.3.0
-Copyright (c) 2015-2017 Apache Software Foundation
+Apache Trafodion Conversational Interface 2.4.0
+Copyright (c) 2015-2018 Apache Software Foundation
>>?section concurrent_hbase_xn_ups
>>cqd traf_use_region_xn 'OFF';
@@ -1147,8 +1147,8 @@ A B
>>
>>-- insert a row using a concurrent region xn
>>sh sqlci -iTEST116\(concurrent_region_xn_ups\) >> LOG116;
-Apache Trafodion Conversational Interface 2.3.0
-Copyright (c) 2015-2017 Apache Software Foundation
+Apache Trafodion Conversational Interface 2.4.0
+Copyright (c) 2015-2018 Apache Software Foundation
>>?section concurrent_region_xn_ups
>>cqd traf_use_region_xn 'ON';
@@ -1177,8 +1177,8 @@ End of MXCI Session
>>-- should show conflict
>>commit work;
-*** ERROR[8616] A conflict was detected during commit processing. Transaction
has been aborted. Detail :org.trafodion.dtm.TransactionManagerException:
org.apache.hadoop.hbase.coprocessor.transactional.CommitConflictException:
java.io.IOException: This Transaction [[transactionId: 4646 regionTX: false
status: PENDING neverReadOnly: false scan Size: 1 write Size: 1 startSQ: 6
prepareEditSize: 0 endEditSize: 0 editSize: 1]] has a scan, scanRange[startRow:
\x80\x00\x00\x01, endRow: \x80\x00\ [...]
-org.trafodion.dtm.HBaseTxClient.prepareCommit(HBaseTxClient.java:525)
+*** ERROR[8616] A conflict was detected during commit processing. Transaction
has been aborted. Detail :org.trafodion.dtm.TransactionManagerException:
org.apache.hadoop.hbase.coprocessor.transactional.CommitConflictException:
java.io.IOException: This Transaction [[transactionId: 8130 regionTX: false
status: PENDING neverReadOnly: false scan Size: 1 write Size: 1 startSQ: 6
prepareEditSize: 0 endEditSize: 0 editSize: 1]] has a scan, scanRange[startRow:
\x80\x00\x00\x01, endRow: \x80\x00\ [...]
+org.trafodion.dtm.HBaseTxClient.prepareCommit(HBaseTxClient.java:528)
--- SQL operation failed with errors.
>>select * from t116t6;
@@ -1206,8 +1206,8 @@ A B
>>
>>-- delete a row using a concurrent hbase xn
>>sh sqlci -iTEST116\(concurrent_hbase_xn_del\) >> LOG116;
-Apache Trafodion Conversational Interface 2.3.0
-Copyright (c) 2015-2017 Apache Software Foundation
+Apache Trafodion Conversational Interface 2.4.0
+Copyright (c) 2015-2018 Apache Software Foundation
>>?section concurrent_hbase_xn_del
>>cqd traf_use_region_xn 'OFF';
@@ -1257,8 +1257,8 @@ End of MXCI Session
>>
>>-- delete a row using a concurrent region xn
>>sh sqlci -iTEST116\(concurrent_region_xn_del\) >> LOG116;
-Apache Trafodion Conversational Interface 2.3.0
-Copyright (c) 2015-2017 Apache Software Foundation
+Apache Trafodion Conversational Interface 2.4.0
+Copyright (c) 2015-2018 Apache Software Foundation
>>?section concurrent_region_xn_del
>>cqd traf_use_region_xn 'ON';
@@ -1287,8 +1287,8 @@ End of MXCI Session
>>-- should show conflict
>>commit work;
-*** ERROR[8616] A conflict was detected during commit processing. Transaction
has been aborted. Detail :org.trafodion.dtm.TransactionManagerException:
org.apache.hadoop.hbase.coprocessor.transactional.CommitConflictException:
java.io.IOException: This Transaction [[transactionId: 4652 regionTX: false
status: PENDING neverReadOnly: false scan Size: 1 write Size: 1 startSQ: 11
prepareEditSize: 0 endEditSize: 0 editSize: 1]] has a scan, scanRange[startRow:
\x80\x00\x00\x01, endRow: \x80\x00 [...]
-org.trafodion.dtm.HBaseTxClient.prepareCommit(HBaseTxClient.java:525)
+*** ERROR[8616] A conflict was detected during commit processing. Transaction
has been aborted. Detail :org.trafodion.dtm.TransactionManagerException:
org.apache.hadoop.hbase.coprocessor.transactional.CommitConflictException:
java.io.IOException: This Transaction [[transactionId: 8136 regionTX: false
status: PENDING neverReadOnly: false scan Size: 1 write Size: 1 startSQ: 11
prepareEditSize: 0 endEditSize: 0 editSize: 1]] has a scan, scanRange[startRow:
\x80\x00\x00\x01, endRow: \x80\x00 [...]
+org.trafodion.dtm.HBaseTxClient.prepareCommit(HBaseTxClient.java:528)
--- SQL operation failed with errors.
>>select * from t116t6;
@@ -1311,8 +1311,8 @@
org.trafodion.dtm.HBaseTxClient.prepareCommit(HBaseTxClient.java:525)
>>
>>-- delete a row using a concurrent hbase xn
>>sh sqlci -iTEST116\(concurrent_hbase_xn_checkanddel\) >> LOG116;
-Apache Trafodion Conversational Interface 2.3.0
-Copyright (c) 2015-2017 Apache Software Foundation
+Apache Trafodion Conversational Interface 2.4.0
+Copyright (c) 2015-2018 Apache Software Foundation
>>?section concurrent_hbase_xn_checkanddel
>>cqd traf_use_region_xn 'OFF';
@@ -1362,8 +1362,8 @@ End of MXCI Session
>>
>>-- delete a row using a concurrent region xn
>>sh sqlci -iTEST116\(concurrent_region_xn_checkanddel\) >> LOG116;
-Apache Trafodion Conversational Interface 2.3.0
-Copyright (c) 2015-2017 Apache Software Foundation
+Apache Trafodion Conversational Interface 2.4.0
+Copyright (c) 2015-2018 Apache Software Foundation
>>?section concurrent_region_xn_checkanddel
>>cqd traf_use_region_xn 'ON';
@@ -1392,8 +1392,8 @@ End of MXCI Session
>>-- should show conflict
>>commit work;
-*** ERROR[8616] A conflict was detected during commit processing. Transaction
has been aborted. Detail :org.trafodion.dtm.TransactionManagerException:
org.apache.hadoop.hbase.coprocessor.transactional.CommitConflictException:
java.io.IOException: This Transaction [[transactionId: 4658 regionTX: false
status: PENDING neverReadOnly: false scan Size: 1 write Size: 1 startSQ: 15
prepareEditSize: 0 endEditSize: 0 editSize: 1]] has a scan, scanRange[startRow:
\x80\x00\x00\x01, endRow: \x80\x00 [...]
-org.trafodion.dtm.HBaseTxClient.prepareCommit(HBaseTxClient.java:525)
+*** ERROR[8616] A conflict was detected during commit processing. Transaction
has been aborted. Detail :org.trafodion.dtm.TransactionManagerException:
org.apache.hadoop.hbase.coprocessor.transactional.CommitConflictException:
java.io.IOException: This Transaction [[transactionId: 8142 regionTX: false
status: PENDING neverReadOnly: false scan Size: 1 write Size: 1 startSQ: 15
prepareEditSize: 0 endEditSize: 0 editSize: 1]] has a scan, scanRange[startRow:
\x80\x00\x00\x01, endRow: \x80\x00 [...]
+org.trafodion.dtm.HBaseTxClient.prepareCommit(HBaseTxClient.java:528)
--- SQL operation failed with errors.
>>select * from t116t6;
@@ -1416,8 +1416,8 @@
org.trafodion.dtm.HBaseTxClient.prepareCommit(HBaseTxClient.java:525)
>>
>>-- update row using a concurrent hbase xn
>>sh sqlci -iTEST116\(concurrent_hbase_xn_checkandupd\) >> LOG116;
-Apache Trafodion Conversational Interface 2.3.0
-Copyright (c) 2015-2017 Apache Software Foundation
+Apache Trafodion Conversational Interface 2.4.0
+Copyright (c) 2015-2018 Apache Software Foundation
>>?section concurrent_hbase_xn_checkandupd
>>cqd traf_use_region_xn 'OFF';
@@ -1472,8 +1472,8 @@ A B
>>
>>-- update row using a concurrent region xn
>>sh sqlci -iTEST116\(concurrent_region_xn_checkandupd\) >> LOG116;
-Apache Trafodion Conversational Interface 2.3.0
-Copyright (c) 2015-2017 Apache Software Foundation
+Apache Trafodion Conversational Interface 2.4.0
+Copyright (c) 2015-2018 Apache Software Foundation
>>?section concurrent_region_xn_checkandupd
>>cqd traf_use_region_xn 'ON';
@@ -1502,8 +1502,8 @@ End of MXCI Session
>>-- should show conflict
>>commit work;
-*** ERROR[8616] A conflict was detected during commit processing. Transaction
has been aborted. Detail :org.trafodion.dtm.TransactionManagerException:
org.apache.hadoop.hbase.coprocessor.transactional.CommitConflictException:
java.io.IOException: This Transaction [[transactionId: 4664 regionTX: false
status: PENDING neverReadOnly: false scan Size: 2 write Size: 1 startSQ: 20
prepareEditSize: 0 endEditSize: 0 editSize: 1]] has a scan, scanRange[startRow:
\x80\x00\x00\x01, endRow: \x80\x00 [...]
-org.trafodion.dtm.HBaseTxClient.prepareCommit(HBaseTxClient.java:525)
+*** ERROR[8616] A conflict was detected during commit processing. Transaction
has been aborted. Detail :org.trafodion.dtm.TransactionManagerException:
org.apache.hadoop.hbase.coprocessor.transactional.CommitConflictException:
java.io.IOException: This Transaction [[transactionId: 8148 regionTX: false
status: PENDING neverReadOnly: false scan Size: 2 write Size: 1 startSQ: 20
prepareEditSize: 0 endEditSize: 0 editSize: 1]] has a scan, scanRange[startRow:
\x80\x00\x00\x01, endRow: \x80\x00 [...]
+org.trafodion.dtm.HBaseTxClient.prepareCommit(HBaseTxClient.java:528)
--- SQL operation failed with errors.
>>select * from t116t6;
@@ -1515,6 +1515,208 @@ A B
--- 1 row(s) selected.
>>
+>>obey TEST116(begin_transaction_for_select);
+>>set transaction autocommit off ;
+
+--- SQL operation complete.
+>>cqd begin_transaction_for_select '2' ;
+
+--- SQL operation complete.
+>>select * from trafodion.sch.t116t6 for update ;
+
+A B
+----------- -----------
+
+ 1 111
+
+--- 1 row(s) selected.
+>>commit work;
+
+--- SQL operation complete.
+>>select * from trafodion.sch.t116t6 ;
+
+A B
+----------- -----------
+
+ 1 111
+
+--- 1 row(s) selected.
+>>commit work;
+
+--- SQL operation complete.
+>>select * from trafodion.sch.t116t6 for read committed access ;
+
+A B
+----------- -----------
+
+ 1 111
+
+--- 1 row(s) selected.
+>>commit work;
+
+--- SQL operation complete.
+>>select * from trafodion.sch.t116t6 for read uncommitted access ;
+
+A B
+----------- -----------
+
+ 1 111
+
+--- 1 row(s) selected.
+>>commit work;
+
+--- SQL operation complete.
+>>select * from trafodion.sch.t116t6 for skip conflict access ;
+
+A B
+----------- -----------
+
+ 1 111
+
+--- 1 row(s) selected.
+>>commit work;
+
+*** ERROR[8605] Committing a transaction which has not started.
+
+--- SQL operation failed with errors.
+>>cqd begin_transaction_for_select '1' ;
+
+--- SQL operation complete.
+>>select * from trafodion.sch.t116t6 for update ;
+
+A B
+----------- -----------
+
+ 1 111
+
+--- 1 row(s) selected.
+>>commit work;
+
+--- SQL operation complete.
+>>select * from trafodion.sch.t116t6 ;
+
+A B
+----------- -----------
+
+ 1 111
+
+--- 1 row(s) selected.
+>>commit work;
+
+*** ERROR[8605] Committing a transaction which has not started.
+
+--- SQL operation failed with errors.
+>>select * from trafodion.sch.t116t6 for read committed access ;
+
+A B
+----------- -----------
+
+ 1 111
+
+--- 1 row(s) selected.
+>>commit work;
+
+*** ERROR[8605] Committing a transaction which has not started.
+
+--- SQL operation failed with errors.
+>>select * from trafodion.sch.t116t6 for read uncommitted access ;
+
+A B
+----------- -----------
+
+ 1 111
+
+--- 1 row(s) selected.
+>>commit work;
+
+*** ERROR[8605] Committing a transaction which has not started.
+
+--- SQL operation failed with errors.
+>>select * from trafodion.sch.t116t6 for skip conflict access ;
+
+A B
+----------- -----------
+
+ 1 111
+
+--- 1 row(s) selected.
+>>commit work;
+
+*** ERROR[8605] Committing a transaction which has not started.
+
+--- SQL operation failed with errors.
+>>cqd begin_transaction_for_select '0' ;
+
+--- SQL operation complete.
+>>select * from trafodion.sch.t116t6 for update ;
+
+A B
+----------- -----------
+
+ 1 111
+
+--- 1 row(s) selected.
+>>commit work;
+
+*** ERROR[8605] Committing a transaction which has not started.
+
+--- SQL operation failed with errors.
+>>select * from trafodion.sch.t116t6 ;
+
+A B
+----------- -----------
+
+ 1 111
+
+--- 1 row(s) selected.
+>>commit work;
+
+*** ERROR[8605] Committing a transaction which has not started.
+
+--- SQL operation failed with errors.
+>>select * from trafodion.sch.t116t6 for read committed access ;
+
+A B
+----------- -----------
+
+ 1 111
+
+--- 1 row(s) selected.
+>>commit work;
+
+*** ERROR[8605] Committing a transaction which has not started.
+
+--- SQL operation failed with errors.
+>>select * from trafodion.sch.t116t6 for read uncommitted access ;
+
+A B
+----------- -----------
+
+ 1 111
+
+--- 1 row(s) selected.
+>>commit work;
+
+*** ERROR[8605] Committing a transaction which has not started.
+
+--- SQL operation failed with errors.
+>>select * from trafodion.sch.t116t6 for skip conflict access ;
+
+A B
+----------- -----------
+
+ 1 111
+
+--- 1 row(s) selected.
+>>commit work;
+
+*** ERROR[8605] Committing a transaction which has not started.
+
+--- SQL operation failed with errors.
+>>set transaction autocommit on ;
+
+--- SQL operation complete.
+>>
>>
>>obey TEST116(clean_up);
>>drop table t116t1;
diff --git a/core/sql/regress/core/TEST116 b/core/sql/regress/core/TEST116
index 9f380f8..1eb4f01 100755
--- a/core/sql/regress/core/TEST116
+++ b/core/sql/regress/core/TEST116
@@ -367,6 +367,7 @@ sh sqlci -iTEST116\(concurrent_region_xn_checkandupd\) >>
LOG116;
commit work;
select * from t116t6;
+obey TEST116(begin_transaction_for_select);
obey TEST116(clean_up);
log;
@@ -440,6 +441,43 @@ prepare s from update trafodion.sch.t116t6 set b = 111
where a = 1;
explain options 'f' s;
execute s;
+?section begin_transaction_for_select
+set transaction autocommit off ;
+cqd begin_transaction_for_select '2' ;
+select * from trafodion.sch.t116t6 for update ;
+commit work;
+select * from trafodion.sch.t116t6 ;
+commit work;
+select * from trafodion.sch.t116t6 for read committed access ;
+commit work;
+select * from trafodion.sch.t116t6 for read uncommitted access ;
+commit work;
+select * from trafodion.sch.t116t6 for skip conflict access ;
+commit work;
+cqd begin_transaction_for_select '1' ;
+select * from trafodion.sch.t116t6 for update ;
+commit work;
+select * from trafodion.sch.t116t6 ;
+commit work;
+select * from trafodion.sch.t116t6 for read committed access ;
+commit work;
+select * from trafodion.sch.t116t6 for read uncommitted access ;
+commit work;
+select * from trafodion.sch.t116t6 for skip conflict access ;
+commit work;
+cqd begin_transaction_for_select '0' ;
+select * from trafodion.sch.t116t6 for update ;
+commit work;
+select * from trafodion.sch.t116t6 ;
+commit work;
+select * from trafodion.sch.t116t6 for read committed access ;
+commit work;
+select * from trafodion.sch.t116t6 for read uncommitted access ;
+commit work;
+select * from trafodion.sch.t116t6 for skip conflict access ;
+commit work;
+set transaction autocommit on ;
+
?section clean_up
drop table t116t1;
drop table t116t2;
diff --git a/core/sql/sqlcomp/DefaultConstants.h
b/core/sql/sqlcomp/DefaultConstants.h
index bc2b35f..ef9dbce 100644
--- a/core/sql/sqlcomp/DefaultConstants.h
+++ b/core/sql/sqlcomp/DefaultConstants.h
@@ -3386,6 +3386,12 @@ enum DefaultConstants
HIVE_CREATE_TABLE_LIKE_PARTITION_NO_NULL,
CANCEL_QUERY_ALLOWED,
+
+ // Determines if a transaction needs to be started for select
+ // 0 - No transactions for SELECT
+ // 1 - Transaction started for SELECT .. FOR UPDATE
+ // 2 - Transaction started for SELECT with isolaton level other than skip
conflict access
+ BEGIN_TRANSACTION_FOR_SELECT,
// This enum constant must be the LAST one in the list; it's a count,
// not an Attribute (it's not IN DefaultDefaults; it's the SIZE of it)!
__NUM_DEFAULT_ATTRIBUTES
diff --git a/core/sql/sqlcomp/nadefaults.cpp b/core/sql/sqlcomp/nadefaults.cpp
index 21d8271..c7a3c00 100644
--- a/core/sql/sqlcomp/nadefaults.cpp
+++ b/core/sql/sqlcomp/nadefaults.cpp
@@ -454,6 +454,7 @@ SDDkwd__(ALLOW_DP2_ROW_SAMPLING, "SYSTEM"),
XDDkwd__(AUTO_QUERY_RETRY_WARNINGS, "OFF"),
DDkwd__(BASE_NUM_PAS_ON_ACTIVE_PARTS, "OFF"),
+ DDint__(BEGIN_TRANSACTION_FOR_SELECT, "1"),
// see comments in DefaultConstants.h
DDkwd__(BIGNUM_IO, "SYSTEM"),
