dirty read record

General
Enthusiast

dirty read record

In access lock when dirty read happens.where exactly the records are stored. Is it in spool space?

Thanks

5 REPLIES
Supporter

Re: dirty read record

I don't understand the question.

Access lock allows to read a table where an write lock is placed. The table is stored on disk or might be available in cache - where you have no controll about this.

Results of a read will be stored in spool.

Enthusiast

Re: dirty read record

My Question is when user A is inserting one record to table1, At that point of time if user B runs a select query to see all the records using ACCESS lock then where does the record inserted by user A exist.

Supporter

Re: dirty read record

Lets assume you have a big table A - a full table scan needs 10 min with where condition colX = 1 .

User A starts a select count(*) with condition colX = 1 with lock for access

after 5 Min a single row will be added - value of colX = 1. It is done within a sec.

Your question is will be the new row reflected in the result set or not?

At least my understanding would be - you don't know. 
Can be , can be not. This is a dirty read. If the full table scan already passed the block where the one row will be placed I would assume not. If the full table scan passes this block later it will.

If you need certainty - don't use lock row for access. The insert will wait until the select finished.

There are even more interesting questions like what will happen if the 1 more row results in a block split. Could it be that the select will read some rows twice? - I am not sure about this maybe some from TD can explain this.

Teradata Employee

Re: dirty read record

When an insert is performed to a table on Teradata, the row(s) is inserted directly into its position in the table. There is no separate place where rows are stored while being inserted. Some processes that insert volumes of rows put them in temporary places first (INSERT SELECT, MERGE, Multiload acquisition phase, Fastload phase 1) but once the rows are being inserted into the target they are placed where they will stay.

This results in the behavior described by Ulrich above where if a scan is running and a row is inserted before the current scan position it will not be seen by the scan, but if it is inserted after the current scan position it will be included.

I do not like calling this dirty read because it will not be affected by operations such as block split, cylinder split,... There will not be missing rows while update or bulk DML operations are going on. More correct is to call Access Locks "Read Uncommitted" which means that the access lock reader may see things that are in the process of being inserted or updated even though that operation has not committed.

There is one exception - An update operation that updates the PI will be implemented internally as two separate steps: a delete for the old rows and an insert of the updated rows. During the interim, it is possible for there to be rows logically missing from the table. It is advisable to perform this type of opertion with an Exclusive lock to ensure access lock readers do not get unexpected results.

Enthusiast

Re: dirty read record

Thanks a lot for the information...