You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Selvaganesan Govindarajan (JIRA)" <ji...@apache.org> on 2015/11/19 01:34:10 UTC
[jira] [Updated] (TRAFODION-1638) Transaction should be used even
for select statement in read uncommitted access when it is done within a
transaction
[ https://issues.apache.org/jira/browse/TRAFODION-1638?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Selvaganesan Govindarajan updated TRAFODION-1638:
-------------------------------------------------
Affects Version/s: 1.0 (pre-incubation)
Description:
The following test case shows the inserted rows are not shown when select is done with read uncommitted access
create table tselva (c1 int, c2 int);
>>begin work ;
--- SQL operation complete.
>>insert into tselva values (2,2) ;
--- 1 row(s) inserted.
>>select * from tselva ;
C1 C2
----------- -----------
1 2
2 2
--- 2 row(s) selected.
>>get statistics for qid current ;
Qid MXID11000026567212314627721939132000000000206U3333300_336___SQLCI_DML_LAST__
Compile Start Time 2015/11/18 17:29:34.154042
Compile End Time 2015/11/18 17:29:34.154849
Compile Elapsed Time 0:00:00.000807
Execute Start Time 2015/11/18 17:29:34.154977
Execute End Time 2015/11/18 17:29:34.165413
Execute Elapsed Time 0:00:00.010436
State DEALLOCATED
Rows Affected 0
SQL Error Code 100
Stats Error Code 0
Query Type SQL_SELECT_NON_UNIQUE
Sub Query Type SQL_STMT_NA
Estimated Accessed Rows 0
Estimated Used Rows 0
Parent Qid NONE
Parent Query System NONE
Child Qid NONE
Number of SQL Processes 1
Number of Cpus 1
Transaction Id 57357
Source String select * from tselva ;
SQL Source Length 22
Rows Returned 2
First Row Returned Time 2015/11/18 17:29:34.164801
Last Error before AQR 0
Number of AQR retries 0
Delay before AQR 0
No. of times reclaimed 0
Cancel Time -1
Last Suspend Time -1
Stats Collection Type OPERATOR_STATS
SQL Process Busy Time 3,540
UDR Process Busy Time 0
SQL Space Allocated 32 KB
SQL Space Used 5 KB
SQL Heap Allocated 15 KB
SQL Heap Used 8 KB
EID Space Allocated 0 KB
EID Space Used 0 KB
EID Heap Allocated 0 KB
EID Heap Used 0 KB
Processes Created 0
Process Create Time 0
Request Message Count 0
Request Message Bytes 0
Reply Message Count 0
Reply Message Bytes 0
Scr. Overflow Mode UNKNOWN
Scr File Count 0
Scr. Buffer Blk Size 0
Scr. Buffer Blks Read 0
Scr. Buffer Blks Written 0
Scr. Read Count 0
Scr. Write Count 0
Table Name
Records Accessed Records Used HBase/Hive HBase/Hive HBase/Hive IO HBase/Hive IO
Estimated/Actual Estimated/Actual IOs IO MBytes Sum Time Max Time
TRAFODION.SCH.TSELVA
0 100
2 2 2 0 8,789 8,789
--- SQL operation complete.
>>fc sele
>>select * from tselva ;
.. ifor read uncommitted access ;
>>select * from tselva for read uncommitted access ;;
..
C1 C2
----------- -----------
1 2
--- 1 row(s) selected.
Row is missing though executed under the same transaction
>>fc get statistics
>>get statistics for qid current ;
..
Qid MXID11000026567212314627721939132000000000206U3333300_338___SQLCI_DML_LAST__
Compile Start Time 2015/11/18 17:30:04.043650
Compile End Time 2015/11/18 17:30:04.051613
Compile Elapsed Time 0:00:00.007963
Execute Start Time 2015/11/18 17:30:04.051768
Execute End Time 2015/11/18 17:30:04.061414
Execute Elapsed Time 0:00:00.009646
State DEALLOCATED
Rows Affected 0
SQL Error Code 100
Stats Error Code 0
Query Type SQL_SELECT_NON_UNIQUE
Sub Query Type SQL_STMT_NA
Estimated Accessed Rows 0
Estimated Used Rows 0
Parent Qid NONE
Parent Query System NONE
Child Qid NONE
Number of SQL Processes 1
Number of Cpus 1
Transaction Id 57357
Source String select * from tselva for read uncommitted access ;
SQL Source Length 50
Rows Returned 1
First Row Returned Time 2015/11/18 17:30:04.060778
Last Error before AQR 0
Number of AQR retries 0
Delay before AQR 0
No. of times reclaimed 0
Cancel Time -1
Last Suspend Time -1
Stats Collection Type OPERATOR_STATS
SQL Process Busy Time 3,935
UDR Process Busy Time 0
SQL Space Allocated 32 KB
SQL Space Used 5 KB
SQL Heap Allocated 15 KB
SQL Heap Used 8 KB
EID Space Allocated 0 KB
EID Space Used 0 KB
EID Heap Allocated 0 KB
EID Heap Used 0 KB
Processes Created 0
Process Create Time 0
Request Message Count 0
Request Message Bytes 0
Reply Message Count 0
Reply Message Bytes 0
Scr. Overflow Mode UNKNOWN
Scr File Count 0
Scr. Buffer Blk Size 0
Scr. Buffer Blks Read 0
Scr. Buffer Blks Written 0
Scr. Read Count 0
Scr. Write Count 0
Table Name
Records Accessed Records Used HBase/Hive HBase/Hive HBase/Hive IO HBase/Hive IO
Estimated/Actual Estimated/Actual IOs IO MBytes Sum Time Max Time
TRAFODION.SCH.TSELVA
0 100
1 1 2 0 7,725 7,725
--- SQL operation complete.
>>
Component/s: sql-exe
> Transaction should be used even for select statement in read uncommitted access when it is done within a transaction
> --------------------------------------------------------------------------------------------------------------------
>
> Key: TRAFODION-1638
> URL: https://issues.apache.org/jira/browse/TRAFODION-1638
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-exe
> Affects Versions: 1.0 (pre-incubation)
> Reporter: Selvaganesan Govindarajan
>
> The following test case shows the inserted rows are not shown when select is done with read uncommitted access
> create table tselva (c1 int, c2 int);
> >>begin work ;
> --- SQL operation complete.
> >>insert into tselva values (2,2) ;
> --- 1 row(s) inserted.
> >>select * from tselva ;
> C1 C2
> ----------- -----------
> 1 2
> 2 2
> --- 2 row(s) selected.
> >>get statistics for qid current ;
> Qid MXID11000026567212314627721939132000000000206U3333300_336___SQLCI_DML_LAST__
> Compile Start Time 2015/11/18 17:29:34.154042
> Compile End Time 2015/11/18 17:29:34.154849
> Compile Elapsed Time 0:00:00.000807
> Execute Start Time 2015/11/18 17:29:34.154977
> Execute End Time 2015/11/18 17:29:34.165413
> Execute Elapsed Time 0:00:00.010436
> State DEALLOCATED
> Rows Affected 0
> SQL Error Code 100
> Stats Error Code 0
> Query Type SQL_SELECT_NON_UNIQUE
> Sub Query Type SQL_STMT_NA
> Estimated Accessed Rows 0
> Estimated Used Rows 0
> Parent Qid NONE
> Parent Query System NONE
> Child Qid NONE
> Number of SQL Processes 1
> Number of Cpus 1
> Transaction Id 57357
> Source String select * from tselva ;
> SQL Source Length 22
> Rows Returned 2
> First Row Returned Time 2015/11/18 17:29:34.164801
> Last Error before AQR 0
> Number of AQR retries 0
> Delay before AQR 0
> No. of times reclaimed 0
> Cancel Time -1
> Last Suspend Time -1
> Stats Collection Type OPERATOR_STATS
> SQL Process Busy Time 3,540
> UDR Process Busy Time 0
> SQL Space Allocated 32 KB
> SQL Space Used 5 KB
> SQL Heap Allocated 15 KB
> SQL Heap Used 8 KB
> EID Space Allocated 0 KB
> EID Space Used 0 KB
> EID Heap Allocated 0 KB
> EID Heap Used 0 KB
> Processes Created 0
> Process Create Time 0
> Request Message Count 0
> Request Message Bytes 0
> Reply Message Count 0
> Reply Message Bytes 0
> Scr. Overflow Mode UNKNOWN
> Scr File Count 0
> Scr. Buffer Blk Size 0
> Scr. Buffer Blks Read 0
> Scr. Buffer Blks Written 0
> Scr. Read Count 0
> Scr. Write Count 0
> Table Name
> Records Accessed Records Used HBase/Hive HBase/Hive HBase/Hive IO HBase/Hive IO
> Estimated/Actual Estimated/Actual IOs IO MBytes Sum Time Max Time
> TRAFODION.SCH.TSELVA
> 0 100
> 2 2 2 0 8,789 8,789
> --- SQL operation complete.
> >>fc sele
> >>select * from tselva ;
> .. ifor read uncommitted access ;
> >>select * from tselva for read uncommitted access ;;
> ..
> C1 C2
> ----------- -----------
> 1 2
> --- 1 row(s) selected.
> Row is missing though executed under the same transaction
> >>fc get statistics
> >>get statistics for qid current ;
> ..
> Qid MXID11000026567212314627721939132000000000206U3333300_338___SQLCI_DML_LAST__
> Compile Start Time 2015/11/18 17:30:04.043650
> Compile End Time 2015/11/18 17:30:04.051613
> Compile Elapsed Time 0:00:00.007963
> Execute Start Time 2015/11/18 17:30:04.051768
> Execute End Time 2015/11/18 17:30:04.061414
> Execute Elapsed Time 0:00:00.009646
> State DEALLOCATED
> Rows Affected 0
> SQL Error Code 100
> Stats Error Code 0
> Query Type SQL_SELECT_NON_UNIQUE
> Sub Query Type SQL_STMT_NA
> Estimated Accessed Rows 0
> Estimated Used Rows 0
> Parent Qid NONE
> Parent Query System NONE
> Child Qid NONE
> Number of SQL Processes 1
> Number of Cpus 1
> Transaction Id 57357
> Source String select * from tselva for read uncommitted access ;
> SQL Source Length 50
> Rows Returned 1
> First Row Returned Time 2015/11/18 17:30:04.060778
> Last Error before AQR 0
> Number of AQR retries 0
> Delay before AQR 0
> No. of times reclaimed 0
> Cancel Time -1
> Last Suspend Time -1
> Stats Collection Type OPERATOR_STATS
> SQL Process Busy Time 3,935
> UDR Process Busy Time 0
> SQL Space Allocated 32 KB
> SQL Space Used 5 KB
> SQL Heap Allocated 15 KB
> SQL Heap Used 8 KB
> EID Space Allocated 0 KB
> EID Space Used 0 KB
> EID Heap Allocated 0 KB
> EID Heap Used 0 KB
> Processes Created 0
> Process Create Time 0
> Request Message Count 0
> Request Message Bytes 0
> Reply Message Count 0
> Reply Message Bytes 0
> Scr. Overflow Mode UNKNOWN
> Scr File Count 0
> Scr. Buffer Blk Size 0
> Scr. Buffer Blks Read 0
> Scr. Buffer Blks Written 0
> Scr. Read Count 0
> Scr. Write Count 0
> Table Name
> Records Accessed Records Used HBase/Hive HBase/Hive HBase/Hive IO HBase/Hive IO
> Estimated/Actual Estimated/Actual IOs IO MBytes Sum Time Max Time
> TRAFODION.SCH.TSELVA
> 0 100
> 1 1 2 0 7,725 7,725
> --- SQL operation complete.
> >>
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)