Forum

Teradata Deadlock P...
 

Teradata Deadlock Prevention  

  RSS

Roland Wenzlofsky
(@rwenzlofsky)
Estimable Member
Joined: 6 years ago
Posts: 203
19/02/2017 9:46 pm  

 

What are Deadlocks?

Deadlocks occur when each of two transactions holds the lock on a database object the other transaction needs next.

Here is an example:

Transaction 1 locks some rows in table 1, and transaction 2 locks some rows in table 2. The next step of transaction 1 is to lock rows in table 2  and transaction 2 needs to lock rows in table 1.

We just described a deadlock situation. Without deadlock handling, both transactions would wait forever, or until they are aborted. On Teradata, deadlocks can happen on one AMP (local deadlock) or across different AMPs (global deadlock), and for various reasons.

Luckily, Teradata uses a queuing strategy which is serializing locking requests to avoid deadlocks.

There was a change in the naming convention for this locking strategy in Teradata 15.10.

Until Teradata 14.10, this locking strategy was called "pseudo table locks" independently if the lock was on row hash level (for dictionary tables) or table level.

Since Teradata 15.10, table level and partition locking (a new feature) are called "proxy locking", and  rows hash locking on dictionary tables is called "pseudo table locking."

In my opinion, the strategy is still the same (but what's new since Teradata 15.10 is partition locking). Just the wording changed.

The "Proxy" or "Pseudo Table" Lock Strategy

Without a proper locking strategy, and two transactions asking for a write lock on the same table, it could happen that the first transaction gets the lock for the table on some of the AMPs, and the second query takes the locks on another set of AMPs.

None of the transactions would be able to finish its task. Both requests would wait forever (for completeness: there is an NOWAIT lock modifier available, which aborts the request if the lock can't be obtained immediately).

A typical global deadlock situation (involving several AMPs).

The "proxy lock" or "pseudo table" strategy avoids such deadlocks, by serializing the requests.

For sure many of you all have seen the term "to prevent global deadlock" when explaining a query (the explain plan is from a Teradata 15.10 system):

Explain UPDATE CUSTOMER SET AGE = 40;

1) First, we lock DWHPRO.CUSTOMER for write
on a reserved RowHash to prevent global deadlock.
2) Next, we lock DWHPRO.CUSTOMER for write.
3) We do an all-AMPs UPDATE from DWHPRO.CUSTOMER by way of an
all-rows scan with no residual conditions. The size is estimated
with high confidence to be 100,000 rows. The estimated time for
this step is 0.21 seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> No rows are returned to the user as the result of statement 1.
The total estimated time is 0.21 seconds.

Teradata Deadlock Prevention in Action

 

teradata deadlocks

Here is an example, which shows the deadlock handling strategy in action:

Two update statements execute at almost the same time ('Update 1' and 'Update 2'), and they want to update the same table. Each update intends to have the write lock:

UPDATE Customer SET Age=40 ;
UPDATE Customer SET Gender = 'M';

"Pseudo table" or "Proxy" locking ensures that each request has to get the pseudo lock on a reserved rowhash, before obtaining the required lock.

For each table, "proxy" or "pseudo table" locking defines an AMP which is the gatekeeper to the locks. The gatekeeper AMP for each table is found by hashing its "table id" value.

Hashing happens in the same way like primary index hashing. By hashing the "table id", the gatekeeper AMP is found.

As the hashing algorithm is stable, the rows hash for a specific "table id" always is the same, and as long as the system configuration is not changed, there is a 1:1 relation between table and gatekeeper AMP.

In our example, there are two update statements which want a write lock on the same table.

Assuming that "update 1" is slightly faster than "update 2", it will get the "proxy" or "pseudo table" lock on AMP 2, which is the gatekeeper for the table "Customer." "Update 2" has to wait in the "gatekeeper" queue of AMP 2 and will be next, as soon as "update 2" finished and released the write lock.

Limitations

Not all kind of deadlocks can be avoided with the above-described strategy. It only works if both participating transactions work with table locks. If one or both request use row-hash locking, deadlocks still can happen.

Furthermore, deadlock detection takes time. Teradata checks by default for global deadlocks every four minutes. Local deadlocks are searched every 30 seconds.

Usually, these times are ok, but in some particular cases, you might want to decrease global deadlock detection intervals.

One of my clients uses a lot of join indexes, which are causing many global deadlocks (the join indexes are needed for primary index access, i.e. row hash locks are used).

The join indexes are utilized by tactical workload requests, to keep execution times below a couple of seconds.

Having global deadlock detection set to four minutes is counterproductive in this case.

Questions?
If you have any questions about all this, please ask in the comments! I’ll be paying close attention and answering as many as I can. Thank you for reading. Whatever this blog has become, I owe it all to you.

This topic was modified 2 weeks ago by DWH Pro Admin

Quote
D
 D
(@D)
Guest
Joined: 3 years ago
Posts: 1
20/02/2017 7:57 am  

Roland, what about "proxy lock", which appeared in teradata 15.10. What are differences between that mechanism and "pseudo lock"?


ReplyQuote
Deepak
 Deepak
(@Deepak)
Guest
Joined: 3 years ago
Posts: 1
20/02/2017 12:49 pm  

This is a good explanation on a very much needed topic


ReplyQuote
Madhu
 Madhu
(@Madhu)
Guest
Joined: 3 years ago
Posts: 1
20/02/2017 2:56 pm  

excellent but got confused with pseudo locks..so please explain me in detail


ReplyQuote
DWH Pro Admin
(@oshun123)
Member Admin
Joined: 5 years ago
Posts: 81
20/02/2017 6:04 pm  

Hi, I included the answers to your question in the post


ReplyQuote
DWH Pro Admin
(@oshun123)
Member Admin
Joined: 5 years ago
Posts: 81
20/02/2017 6:18 pm  

I was improving the post a little bit. Please let me know if you still have questions, and thanks to everybody for the feedback!


ReplyQuote
David Wellman
 David Wellman
(@David Wellman)
Guest
Joined: 3 years ago
Posts: 1
21/02/2017 8:52 am  

Hi Roland,
A good simple post to explain this bit of processing. A couple of points that I'd like to make:

1) The wording for this processing changed in TD 15.10. before that you would see something like the following:
First, we lock a distinct workdb."pseudo table" for read on a
RowHash to prevent global deadlock for DWHPRO.CUSTOMER.
My point is that the processing hasn't changed, it is ** only ** the words in the Explain output that have changed.

2) Even with this processing in place it doesn't prevent all deadlocks in a Teradata system. Row hash locks do not use the "pseudo table" mechanism, so you can get deadlocks between two transactions if at least one of them uses a row-hash lock.

3) Finally the time it takes for Teradata to resolve global deadlocks is tunable (via DBSControl). The default is 240 seconds (4 minutes) which in my experience most customers use and that seems to be fine. Unlike on earlier types of Nodes, the current Node types (more specifically the CPU's that they're using) mean that you can set this quite low.

Cheers,
Dave


ReplyQuote
DWH Pro Admin
(@oshun123)
Member Admin
Joined: 5 years ago
Posts: 81
21/02/2017 9:34 pm  

Thanks, Dave!

I enriched the article with your comments!


ReplyQuote
Aleksei Svitin
 Aleksei Svitin
(@Aleksei Svitin)
Guest
Joined: 3 years ago
Posts: 6
24/03/2017 3:24 pm  

Hello.

Thank you for a helpful article.

I'm not sure that difference between proxy lock(15.10) and pseudo table lock is just wording change.
As I understand, a pseudo table lock is only one per instance. That fact may possible leads to collisions if hash(TableId) is lead to the same AMP for two different TableID, and in turn it is decrease concurrency in some degree.

But a proxy lock is more granular. It is one per table or maybe is one per a table partition(in case when table is row partitioning).

If i understand documentation correctly, each table or row partition of table contains a "something"(most probably a row) with reserved hash code 0xFFFFFFFF or 0xFFFFFFFFFFFFFFFF( those hash codes can not be generated for hashing functions). And it("something") is used as global serialization queue per table or per partition in the same way as pseudo table locks are used before 15.10(except the fact that it is more granular than pseudo table locks).

So the main idea of Proxy Locks looks to be an increasing concurrency by making locks more granular.

Best regards,
Aleksei.


ReplyQuote
Mano
 Mano
(@Mano)
Guest
Joined: 1 year ago
Posts: 1
16/11/2018 10:59 am  

Hi Roland, Thank you for the explanation. Could you tell us how to avoid the deadlock when we replace the view, while at same time some sessions reading the table?


ReplyQuote
Atharva
 Atharva
(@Atharva)
Guest
Joined: 11 months ago
Posts: 1
11/12/2018 3:31 pm  

Is there any way we can make optimizer to apply table level lock for a query instead of rowhash level ?

This post was modified 7 days ago by DWH Pro Admin

ReplyQuote
Share:
>

Please Login or Register