All Forums Analytics
ANIMESH.DUTTA 35 posts Joined 05/09
04 Aug 2009
Release Lock from a Table

I'm selecting rows from a table and then Inserting a row to that table.But after selecting - the table get locked - I don't know what kind of lock!And the Insert got hanged - neither failing nor showing any errors.If I abort that Insert - and going from another select - the query got hanged - means locked table!!!What should I do to release lock from a table - and why it's getting locked whenever select is happening?Any Idea - please help....If I do RELEASE LOCKING TABLE_NAME - showing "The USER doesn't have DUMP or RESTORE access to Table_Name"BT;SELECT * FROM TABLE_NAME;.IF ERRORCODE <> 0 THEN .GOTO ERRORS.IF ACTIVITYCOUNT <> 0 THEN .GOTO LOADFAILINSERT INTO TABLE_NAMEVALUES( VAL1);.IF ERRORCODE <> 0 THEN .GOTO ERRORSET;.LOGOFF;.QUIT 0.LABEL ERRORS.QUIT ERRORCODE.LABEL LOADFAIL.QUIT 100In this Insert is not happening - even after this simple select also not happening... though only the following select is running... But No Insert....LOCKING TABLE TABLE_NAME FOR ACCESS NOWAITSEL * FROM TABLE_NAME

Animesh Dutta Teradata Certified Master
dnoeth 4628 posts Joined 11/04
04 Aug 2009

RELEASE LOCK doesn't remove SQL locks but MLoad/ARC locks. There's no way to release a lock before the transaction is finished.Do you have access to PMon to check for locks?Are there concurrent queries on that table?Try aLOCK ROW WRITE NOWAIT INSERT INTO TABLE_NAME;and check if it fails.Or better start withLOCK TABLE TABLE_NAME WRITE SELECT * FROM TABLE_NAME;Dieter

Dieter

Ghalia 11 posts Joined 12/15
24 Aug 2016

Hi Dieter,
 
After using LOCK TABLE MY_TABLE EXCLUSIVE
I have an update
Then , I want to add something like RELASE LOCK from MY_TABLE after the update,  in case of fail.
Is it possible in Teradata (14) please ?
 
Ghalia

Fred 1096 posts Joined 08/04
25 Aug 2016

If you ROLLBACK / ABORT or COMMIT the transaction, then the locks are released. And in Teradata mode, a database error causes an automatic rollback before returning the error status to the client.

You must sign in to leave a comment.