Type of Lock During a Multiple statement Request-

Database

Type of Lock During a Multiple statement Request-

Hi all,

I have a doubt related to the type of locks during processing the no. of statements in a MSR,just want to clear it.

for eg-

BT

INSERT INTO <tab_C> SELeCt.... ;   --statement 1

UPDATE <tabl_C>...........;   --statement 2

Select * from table_C;  - statement 3

ET

What type of lock would be there on the table when select statement is getting processed.

As this is a multiple statement request then all the 3 statements would be processed as a 

single transaction.. and all locks are relaesed only when the tranaction is completed..

if we look at above that - statement 1 will apply a WRITE lock , statement 2 will again apply WRITE LOCK and statment 3 should ideally apply a READ Lock ..

but as WRITE lock cannot be downgraded to READ lock so ideally statment 3 should have a WRITE LOCK applied..

Please correct me i am wrong....or if some one wants to add some thing here...

Cheers!

Nishant

3 REPLIES
N/A

Re: Type of Lock During a Multiple statement Request-

Hi Nishant,

within a transaction locks may be upgraded, but never downgraded. 

In your case the 1st statement will place a write lock and statements #2 and #3 don't need to set an additional lock.

If you started with #3 you might result in a (worst case dead-) lock when another session also requested a read lock on the same table.

You should avoid BT/ET and use Multi Statement Request instead, thus the optimizer knows about all locks in advance.

Multiple statements form one request when the next statement starts on the same line as the previous semicolon:

This is one transaction consisting of 5 seperate requests, i.e. 5 messages send across the network and independently optimized:

BT;
INSERT INTO <tab_C> SELeCt.... ; --statement 1
UPDATE <tabl_C>...........; --statement 2
Select * from table_C; - statement 3
ET;

But this is one transaction consisting of 3 statements within a single request, i.e. one message send across the network and optimized all together:

INSERT INTO <tab_C> SELeCt....    --statement 1
;UPDATE <tabl_C>........... --statement 2
;Select * from table_C; - statement 3

Dieter

Re: Type of Lock During a Multiple statement Request-

Thanks Dieter for your pefect response..

Just  want to know the difference in type of lock applied at statement 3 in MSR vs BT ET ..

In BT ET,  i understand it would be WRITE Lock at statement 3  and in MSR it would be READ lock ..

Pls correct me if i am wrong.. or add some thing here...

Cheers!

Nishant

N/A

Re: Type of Lock During a Multiple statement Request-

Hi Nishant,

in both cases it's a write lock, because a write lock "includes" a read lock and the most restrictive lock is set at the beginning of the request.

Just Explain the MSR...

Dieter