DEADLOCK forever ...

Database
N/A

DEADLOCK forever ...

Hi all,

trying to explain a deadlock issue is definitely not so easy.

A session S1 (TMODE)  starting with a BT transaction is sending sequentially a lot of UPDATE orders on Table T1,

and a session S2 is coming right in the middle with a "SELECT * from T1" (no locking modifier).

The fact is that S2 query is blocked due to an attempt of LOCK READ , but it is aborted after 4 minutes because of a deadlock.

Viewpoint show that S1  is blocked by S2 and blocking S2,

and that S2 is blocked by S1 and blocking S1: why blocking S1 ? dbqlog shows that UPDATEs are going on (until an abort issued by the user)..

Is the current transaction (BT) of S1 the only reason why a deadlock is evaluated, in spite of running UPDATEs ? 

Thanks for your comments,

Pierre 

 

15 REPLIES
N/A

Re: DEADLOCK forever ...

Was this job a single session using a high PACK factor?

You better apply LOCK TABLE WRITE instead of the default ROW locks which happily cause deadlocks :-)

Teradata Employee

Re: DEADLOCK forever ...

Locks are complicated in a massively parallel system...

 

When a transaction is doing multiple single row operations, each of those operations gets a write lock on the specific rowhash as the execution of the individual row operation is executed. Further row locks are acquired if USIs or JIs need to be updated as a result of the row update. Each of those rowhash locks may be on a different AMP. 

 

When the SELECT requests a full table read lock, that request is sent to every AMP. On AMPs which have not yet seen an update request, the request is granted. On AMPs which already have done an update operation and have a rowhash lock in place, the read lock has to wait. 

 

When another update then happens in the transaction and is routed to an AMP that has not done an update yet, then when the rowhash lock is requested it is blocked by the granted read lock and a deadlock has occurred. Teradata has a deadlock resolution process that runs on a periodic basis, looks for these situations and chooses a transaction to abort to resolve the ddeadlock.

 

Dieter mentions one solution. Placing a LOCKING table FOR WRITE prior to the update operations will lock the table on all AMPs and the select will simply wait until the update is done. 

Another solution would be to use a LOCKING FOR ACCESS modifier on the SELECT. In this case, the select will read the current state of the data without waiting for the write locks from the transaction.

Or the updates can be done in smaller transactions and commit them quicker to narrow the window in which the deadlock can occur.

 

Also, there is a DBS Control parameter that adjusts the deadlock timeout if resolving the deadlock in 4 minutes is not quick enough.

N/A

Re: DEADLOCK forever ...

Hi,

 

I have to deal with the following situation: My client developed a system consisting of a web frontend and Teradata as the database. Performance is terrible. Hundreds of people can use the tool at the same time, each time they change a field in the web frontend and navigate to another screen a stored procedure is called,  which contains about ten updates, delete and insert steps. 

The client expects stable run times, not longer than a couple of seconds. As many full table scans are done, the only chance I saw was working with indexes.

I started to optimize, by adding a lot of join indexes (I have to say, that all the tables are tiny), to give direct access to the data. As the tools need different paths to the data, each of the join indexes contains the same data but a different primary index. 

The tool does a lot of single item updates, deletes and inserts. While this improved performance a lot, I ran into another problem: I suddenly get a huge amount of deadlocks in the stored procedure, especially when several people use the tool at the same time.

 

Is there any chance that I can avoid the deadlocks? If I put WRITE LOCKS on the update statements, could this be a solution?

 

Thanks in Advance,

Roland

Roland Wenzlofsky
Teradata Employee

Re: DEADLOCK forever ...

It really is best if you start another topic for a new conversation, this is related but not really the same as the original...

 

The deadlocks could be eliminated by placing table level write locks on all tables that will be accessed in the transaction. That will need to be done in a single request with all the requested locks eg:

LOCKING TABLE T1 FOR WRITE LOCKING TABLE T2 FOR WRITE ...

 

Alternately a single table could be selected as the sequencing table for the entire application and a single LOCKING sequencing_table FOR WRITE could preceed any operations in any of the transactions. This would simplify the locking statements significantly and make it so that every execution would simply queue on that lock.

 

The tradeoff for doing one of these is that the transactions will be serialized. If the transactions are fast enough and the arrival rate is not too high and there is no waiting for user input or something before the transaction is committed, then it may be sufficient to run them sequentially. Of course if they are deadlocking, then they must be accessing some of the same records for each transaction and thus they have to be sequential anyway.

 

How tiny is tiny? There are some tricks - like forcing a table onto a single AMP, that can be quite effective for very tiny tables.

N/A

Re: DEADLOCK forever ...

Thanks Todd, 

thanks Dieter,

reall appreciate your wise and clear comments;

 

Pierre

N/A

Need to resolve Deadlock issue !!

we have a situation of frequent deadlocks. informatica workflows are executed,and shell scripts are used for batch_control table updation and insertion.

the flow is:

batch_control table has table_id,batch_run_id,inc_start_dt_inc_end_dt,audit_dt,batch_status_cd etc.

after the workflow loads the table, batch control end script runs to close that batch_run_id by giving batch_status_cd ='complete' and inserting new record batch_status_cd ='New' . Sample like below:

BT;

update db.ETL_BATCH_CONTROL

set

BATCH_END_TS = current_timestamp(0), BATCH_STATUS_CD = 'C', AUDIT_DML_TS = current_timestamp(0)

where TABLE_ID = 12345

and INCREMENTAL_LOAD_END_TS is NOT NULL and BATCH_START_TS is NOT NULL and BATCH_END_TS is NULL

and BATCH_STATUS_CD = 'R'

and BATCH_RUN_ID = (sel max(BATCH_RUN_ID) from db_view.NDW_ETL_BATCH_CONTROL where TABLE_ID = 12345)

;

.if ERRORCODE <> 0 then .quit 121

.if ACTIVITYCOUNT <> 1 then .quit 122

ins into db.ETL_BATCH_CONTROL

(

TABLE_ID,BATCH_RUN_ID,INCREMENTAL_LOAD_START_TS,BATCH_STATUS_CD

)

sel TABLE_ID ,BATCH_RUN_ID + 1

,case when 'C' = 'F' then INCREMENTAL_LOAD_START_TS else INCREMENTAL_LOAD_END_TS end

,'N'

from db_view.ETL_BATCH_CONTROL

where TABLE_ID = 12345

and BATCH_RUN_ID = (sel max(BATCH_RUN_ID) from db_view.NDW_ETL_BATCH_CONTROL where TABLE_ID = 12345)

;

.if ERRORCODE <> 0 then .quit 123

.if ACTIVITYCOUNT <> 1 then .quit 124

ET;

 

We are facing deadlock in insert block. also need to mention that there are multiple runs in a day, but teradata should send the requests in queue, if multiple request comes in same time, Not sure of the reason. can anyone please advice.

Tags (2)
Teradata Employee

Re: Need to resolve Deadlock issue !!

Look at the EXPLAINs. I suspect the UPDATE is PI access, so acquires only a RowHash lock, but holds it because of the BT/ET. Then the INSERT/SELECT requests a Table lock.

 

One possible solution: add LOCKING TABLE db.ETL_BATCH_CONTROL FOR WRITE modifier to the UPDATE so you acquire the highest level lock up front.

N/A

Re: Need to resolve Deadlock issue !!

Thanks for the reply!

 

Yes, the table 's PI is (table_id,batch_run_id).

Strange thing is that all the time it is not failing fue to deadlock.. Mostly it gets success.

Yes, I thought of adding locking table for write in both UPDATE and insert. Can i use that in bt et block ? pls suggest

Teradata Employee

Re: Need to resolve Deadlock issue !!

This process will work better if the table is changed to have a PI of just table_id. Then all the operations can be single AMP, sinlge NUPI operations. As long as many years of history are not saved in the table, the NUPI will not get too many duplicates to be an issue.