Locking Within Stored Procedure

Database

Locking Within Stored Procedure

We are trying to put exclusive lock at row level in a stored procude within a BT and ET so the expected fuctionality should be that it should prevent any other connection access to that particular row in that table till the End Transaction happens or there is a roll back. But the result is otherwise there are other connections able to read the particular row from the table even when there is a exclusive lock placed. The same SQL seems to be working fine if it is not part of the SP. The SP is called concurrenlty through multiple java threads.

Any Ideas ?

Example SP Below.

CREATE PROCEDURE TestProc(IN arg1 INTEGER)

BEGIN

BEGIN TRANSACTION;

LOCKING ROW EXCLUSIVE

SELECT col2 FROM table1 WHERE col1 = 1;

UPDATE table1 SET col3 = 5 WHERE col1 = 1 ;

END TRANSACTION;

END;

3 REPLIES
N/A

Re: Locking Within Stored Procedure

I don't know why it doesn't work as expected, but you should check if t's possible to change your code to get rid of the BT/ET: 

BEGIN REQUEST;

SELECT col2 FROM table1 WHERE col1 = 1;

UPDATE table1 SET col3 = 5 WHERE col1 = 1 ;

END REQUEST;

Now it's a MultiStatement Request, which results in 1 instead of 4 messages sent to Teradata. I'm not shure if you still need the LOCK ROW EXCLUSIVE, i think there's some internal lock when the row is modified which prevents access.

Dieter

Re: Locking Within Stored Procedure

Hi,

Is there any update about this subject? I have encontered the same problem with TDUser2011.

mike

Teradata Employee

Re: Locking Within Stored Procedure

For row(hash)-level lock to be granted, the WHERE clause must specify the Primary Index value.