LOCK IN ACCESS SELECT timestamp effectiveness

Database
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