All Forums Database
Santanu84 122 posts Joined 04/13
16 Jun 2014
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

Fred 1096 posts Joined 08/04
17 Jun 2014

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).

Santanu84 122 posts Joined 04/13
18 Jun 2014

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

Fred 1096 posts Joined 08/04
18 Jun 2014

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

Santanu84 122 posts Joined 04/13
18 Jun 2014

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

You must sign in to leave a comment.