All Forums Database
LUCAS 56 posts Joined 06/09
15 Oct 2015
LOCK IN ACCESS SELECT timestamp effectiveness

Hi,
i'm in trouble when trying to distinguish transaction isolation and access on data with SELECT: what image of rows will be accessed ?
A LOCK ROW IN ACCESS SELECT will ensure access on rows in a "dirty read" mode (or Read uncommitted).  What about such a SELECT transaction beginning à Timestamp T1, running simultaneously with another INSERT transaction on the same table that started juste before T1 ? does the rows returned will always be the rows in table before T1 ? or before the beginning of INSERT TRANSACTION ? or an unpredictible result with some rows inserted after T1 ?
What is the dependency between transactions and the state of data ? All of the documentation is clear about locks and concurrency concepts, i couldn't find an answer about the time of transactions impact.
Thanks for Help,
Pierre

dnoeth 4628 posts Joined 11/04
15 Oct 2015

Hi Pierre,
"before the beginning of INSERT TRANSACTION" would be READ COMMITED, but ACCESS LOCK means READ UNCOMMITTED, i.e. you might read a block with already inserted rows = "an unpredictible result with some rows inserted after T1"
See https://en.wikipedia.org/wiki/Isolation_%28database_systems%29
 

Dieter

LUCAS 56 posts Joined 06/09
16 Oct 2015

Hi Dieter,
Thank you for quick answer !
Pierre

Robinhood 7 posts Joined 09/11
16 Oct 2015

Hi Dieter ,
I am running below query 
SELECT ABC.COL1,
ABC.COL2,
ABC.COL3
FROM 
DATABASENAME.ABCD ;
ABC is also tablename which exists in same database where ABCD resides.I am running above query in SQLA 15.0 .First of all , it is not getting failed .I am totally got surprised  to see this behaviour and other thing if i am looking at explain plan then it is making product join between the tables ABC and ABCD .
I am not sure about this functionality at all .
 
Thanks,
RS

dnoeth 4628 posts Joined 11/04
16 Oct 2015

Hi RS,
please post new questions a new topic.
Regarding the product join:
http://forums.teradata.com/forum/general/recursive-query-spool-space-error#comment-136853

Dieter

You must sign in to leave a comment.