Error -7423 while selecting

Database
Enthusiast

Error -7423 while selecting

Users1 is encountering error “7423” while selecting data from a table which has been locked by user2 at table level .

User2 is performing insert/delete on the table. We believe that the User2 has applied the lock with nowait.

Could someone let me know how we can avoid the User2 from locking at table level.
7 REPLIES
Enthusiast

Re: Error -7423 while selecting

Below is the Object LOCK error message that we are facing while more than one session try to perform INSERT operation on the same table at same time.

[NCR][ODBC Teradata][Teradata Database] Object already locked and NOWAIT. Transaction Aborted.Unable to get catalog string.
>------------------------------------
db_process SQLExecute:[NCR][ODBC Teradata][Teradata Database] Object already locked and NOWAIT. Transaction Aborted.Unable to get catalog string.
SQLSTATE=HY000
NATIVE ERROR CODE=-7423

Please let us know whether we can avoid this error message by specifying any clause in the INSERT statement or this can be handled from Teradata database admin side.
Enthusiast

Re: Error -7423 while selecting

You can enforce row level lock while selecting from a table, instead of table level lock.
Use "LOCKING ROW FOR ACCESS select * from table_name ......... ".
In Teradata, locking is by default table level. So, even a simple select query will lock the entire table.
Always lock the table row level while selecting.
Enthusiast

Re: Error -7423 while selecting

Thanks a lot for your response.

Is there any method to apply only row level lock in the insert statement

or is it possible to have any setting at database level to avoid table level locking while performing insert.

Thanks in advance
Enthusiast

Re: Error -7423 while selecting

I would like to reframe my sentence.

Is it possible to avoid table level lock while performing insert/update. If yes, how to do the same and whether it can be done at database level setting or change in the insert/update statement.

Thanks in advance.
Enthusiast

Re: Error -7423 while selecting

Upendra's answer only gave part of the picture.
When inserting or updating rows from a query (Insert/ Select or Update where the primary index is not specified), a Write lock is placed on the table.
If you Insert Values or Update where PIVal = 12345 (via SQL or Multiload) then a Write lock is placed at the row level.

If you do a select without specifying the PI values, a Read lock is placed at table level. A Read Lock will not read through an existing write lock (and vice versa) so the one who gets in second will be delayed or bounced if NOWAIT is specified.
If you put a LOCKING ROW (or Tablename) FOR ACCESS on the Select query, the Select will read through any Write lock at row or table level. (So-called "Dirty Read".) This only applies to Select - a Write lock cannot be downgraded to an Access Lock.
This is why most sites only allow read access through Views - they put the LOCING FOR ACCESS clause in the views.

So yu can either go to this method, or do the Insert or Update by Multiload - extract a file of what you want to update and feed it back. (Not as silly as it sounds for very high data availability systems.)

Hope its clear enough!

Re: Error -7423 while selecting

Not exactly :). There is this scenario where I want to insert to the same table using a select:

User1: Insert into tableA(col1)select col1 from tableB where col1=3;
User2: Insert into tableA(col1)select col1 from tableB where col1=1;

What lock do I set on tableA?
Enthusiast

Re: Error -7423 while selecting

If User1 and User2 do not have the NOWAIT option set, the inserts will run consecutively. First one to send the statement gets in first; the second waits until the other user completes.
You cannot downgrade a Write lock, so they cannot run concurrently.