Lock Override

Database
Enthusiast

Lock Override

Hi Dieter / All

I would like to know one thing.

There is one table T. A view V is created with LOCKING ROW FOR ACCESS.

Now, 3 different users (in 3 different sessions) are trying in parallel to INSERT using V (view).

1. Will this create Row Level locking on table T or override to WRITE lock?

2. If it is overridden to WRITE lock, will there be any possibility of locking contention ?

Please reply for confirmation.

Thanking You

Santanu

4 REPLIES
Teradata Employee

Re: Lock Override

1. INSERT requires a WRITE lock (the request for ACCESS lock will be ignored)

2. INSERT VALUES should still be a RowHash lock; INSERT SELECT will be a Table lock. (Table locks will show up in the EXPLAIN.)

Yes, lock contention is possible (even with RowHash locks, though less likely in that case).

Enthusiast

Re: Lock Override

Hi Fred

Thanks for your reply. Just to summarize my understanding, even if I am using a VIEW with LOCKING ROW FOR ACCESS,

1. If it is Update with PI value, it will be Row-hash Write lock

2. If it is Update with other column, it will be Table Write lock

3. If it is Insert Values, it will be Row-hash Write lock

4. If it is Insert-Select, it will be Table Write lock

5. This strategy will be same for bteq and Informatica PDO

Please let me know your response.

Thanks

Santanu

Teradata Employee

Re: Lock Override

Yes.

Assuming "with PI value" means "WHERE clause specifies values for all PI columns"

Enthusiast

Re: Lock Override

Hi Fred

Thanks for your reply. Yes, I meant all PI cols.

Santanu