Release Lock from a Table

Analytics
Enthusiast

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 LOADFAIL

INSERT INTO TABLE_NAME

VALUES

(

VAL1

);

.IF ERRORCODE <> 0 THEN .GOTO ERRORS

ET;

.LOGOFF;

.QUIT 0

.LABEL ERRORS

.QUIT ERRORCODE

.LABEL LOADFAIL

.QUIT 100

In 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 NOWAIT

SEL * FROM TABLE_NAME
3 REPLIES
Junior Contributor

Re: Release Lock from a Table

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 a
LOCK ROW WRITE NOWAIT INSERT INTO TABLE_NAME;
and check if it fails.

Or better start with
LOCK TABLE TABLE_NAME WRITE
SELECT * FROM TABLE_NAME;

Dieter

Enthusiast

Re: Release Lock from a Table

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

Teradata Employee

Re: Release Lock from a Table

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.