Concurrent Update of Table by muliple sessions

Database

Concurrent Update of Table by muliple sessions

We have a situation where a table is getting updated by 60 sessions at one time. All the 60 sessions are updating different row on the same table. This is a very common problem in relational databases especially in OLTP environments where you can expect the no of concurrent updates goes anywhere from a single table update to 10,000 at the same time.

In our environment the table gets locked. So I have following question and I will appreciate if someone has any experience to share.

1. Can Teradata handle multiple updates at the same time without getting into a deadlock situation?
2. If the locking is dependent on the # of concurrent updates, what that threshold is after which the concurrent session with updates cause deadlocks?

2 REPLIES
Senior Apprentice

Re: Concurrent Update of Table by muliple sessions

Teradata locks only on two levels:
Full Table Scans & NUSI access locks the full table, whereas PI/USI-access locks a rowhash.

If you get rowhash locks you might do multiple read/write-operations on the same table.

So just check Explain to see how the table is accessed, if there are no lock steps at the beginning there'll be rowhash locks.

Dieter

Re: Concurrent Update of Table by muliple sessions

We have the row-hash lock but still we are seeing the session failures because of locking problem. I am not sure if someone has tried successfully updating the same tables with 60 sessions or more and did it successfully then I will appreciate the learning from their experience.