Archives of the TeradataForum

Message Posted: Tue, 30 Aug 2005 @ 16:48:21 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Tuesday, August 30, 2005 12:39 -->

We use after-image journaling on many of our databases. When we backup these tables we use the "USE GROUP READ LOCK" clause so, in theory, there should be no contention and/or blocking problems (aside from a brief period of time when the backup has a "rolling" read lock on a particular block). However, occasionally our backups run longer than normal and, as a result, they are running concurrently with an application that is updating the same tables that are being backed up. When this occurs, we sometimes encounter what appears to be a "deadlock" between the backup and the update transaction. One more piece of information: the backup job is performing a CLUSTER level backup.

This situation occurred recently and while it was in progress I captured information from SHOWLOCKS and LOKDISP. LOKDISP clearly showed that a BLOCKing situation existed between the backup (read lock) and the update SQL (write lock).

IS THERE A KNOWN PROBLEM REGARDING CONTENTION BETWEEN HUT GROUP READ LOCKS AND SQL WRITE LOCKS?

I found a Knowledge Repository article that describes how the group read lock works. Here is a quote from that article: "ARC releases the read lock on the data block and read lock will be placed on the next data block. At this moment a pending write lock request will be MOST LIKELY (emphasis is mine) granted and an update to the data block can take place." MOST LIKELY??? I don't know if that was simply a poor choice of words or was the author aware of a problem where, under certain conditions, the write lock is NOT granted?

Here is my theory: in order to prevent deadlocks between SQL statements, Teradata designates one AMP as the "lock controlling AMP" (called a "pseudo table" in explain plan). Locks are obtained on this AMP first. Once the lock request is granted on this one AMP, the lock is then granted on all other AMPs for that table. This prevents deadlocking by preventing SQL1 from getting a lock on AMP10, while SQL2 is getting a lock on AMP8 for the same table. I'm thinking that CLUSTER backups do not use the "pseudo table" to control locking/deadlocking because cluster backups only backup a portion of the database. Or perhaps ARC does not use the "pseudo table", regardless of whether or not a cluster backup is involved?

Has anyone else run into deadlocking issues between ARC and SQL?

	<Prev	Next>		<<First	<Prev	Next>	Last>>