LOCK IN ACCESS SELECT timestamp effectiveness

Database
Highlighted
Enthusiast

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

4 REPLIES
Junior Contributor

Re: LOCK IN ACCESS SELECT timestamp effectiveness

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

Enthusiast

Re: LOCK IN ACCESS SELECT timestamp effectiveness

Hi Dieter,

Thank you for quick answer !

Pierre

Enthusiast

Re: LOCK IN ACCESS SELECT timestamp effectiveness

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

Junior Contributor

Re: LOCK IN ACCESS SELECT timestamp effectiveness

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