Archives of the TeradataForum

Message Posted: Fri, 21 Feb 2003 @ 03:30:51 GMT

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

In a nutshell, blocking and deadlocking can occur when two things (tasks, people, query, etc) are trying to access something at the same time. However, the thing being accessed is "single user" in the sense that only one thing (task, person, query) can access the something at any one time.

Be patient at the end of my post, there is an answer to your specific question.

For example, consider two people who need to make modifications to the same document (eg. a word document). Lets call them fred and mary, if mary opens up the document before fred, then fred will get the dreaded "This document is being modified by mary..." message. In this case, Fred is being blocked by Mary. All things being equal, eventually Mary will save her changes and release the document and Fred can get access.

How long will it take before Fred can get his hands on the document? Well that depends on how long Mary keeps the document open. In the case of a query, if someone has a write lock on a table (eg. they are inserting into it), then anyone selecting (unless they use locking for access) or attempting to update the content (or maybe even backing up the database) will be blocked.

As for deadlocking, lets suppose that to finish making the changes to the document, Mary needs to get a book from the library. But Fred, who is sick of Mary always getting the word document first knows that Mary is going to need the book, so he thinks to himself, I am going to make life for Mary as miserable as my life is - so he goes down to the library and gets the book before Mary.

Mary finds out that Fred has the book and says "your always causing me grief - gimme the book so I can finish my work". Fred says, "no way your always sneaking around and finishing your work before me and making me look bad - you release the document so I can get my work done first for a change. I want to look good once in a while".

Now we have a deadlock Mary can't finish the document without the book, and Fred won't give up the book unless he gets to do his work on the document.

In this case, some one has to come in and resolve the deadlock. Did I mention Fred and Mary were brother and sister? Well at this point Mum (or if you must Mom) and Dad come into the room and resolve the dispute by giving one of the two waring youngsters both the book and the word document.

In the computer sense a deadlock could occur as follows:

User 1 - Mary                                User 2 - Fred
Begin trans                                  begin trans
Insert into word document ...

                                             /* Mostly so mary can't have it Tee hee */
                                             Locking book for exclusive
                                             select * from book;

                                             Update word document    /* Attempts to lock the word doc */
                                                                     /* but is blocked by that rotten mary */

/*
** Because there is only one
** Mary has to lock it for exclusive
*/
Locking book for exclusive
Select info_I_need_to_get_done from book;

                  /* Now we have a contention going that will rival divorce court !*/

If mary cancels here select and "rollsback her transation", then Fred's update will proceed. If Fred cancels his update and roll's back his transaction, then Mary will get access to her book.

Note that for the deadlock to occur, Fred and Mary must obtain locks on the critical resource in the opposite order to each other. If Fred had attempted to update the word document first, he would have been blocked on the update and never had the opportunity to issue the lock on the book.

The bottom line. In your case, the block for 12 hours was caused because the user that you aborted had the lock. He/She wasn't attempting to access a resource you had locked and as such there scene wasn't set for a deadlock. Eventually (which was shaping up to be a long time) the first user would have eventully released the lock you were waiting for. As it turned out, after 12 hours of waiting, you aborted the other guys query which released the lock and allowed your query to complete - presumably without any further action on your behalf.

In the other case, the deadlock occurred probably along the lines described above - that is the two sessions had obtained locks on the same two (or more) resources but in different orders and both were now competing for a resource that the other guy had. If someone didn't take the initiative to "give up" the resource that they had a lock on, then Teradata would step in, clout one of the two users on the head (aborting the transaction) freeing the other to continue on.

You can see the effect I outline above using two bteq sessions. Create two tables t1 and t2 each with a single column c1. Then in one of the bteq sessions enter

Bt
Update t1 set c1=1;
Update t2 set c1=1;
Et

Then in the other bteq session

Bt
Update t2 set c1=1;
Update t1 set c1=1;
Et

Note that indivudually they work just fine. Next try entering the first command from each script in each bteq (eg. enter bt in the first bteq, then switch to the second). Then do the same thing for the first update (now the first bteq has a write lock on t1 and the second bteq has a write lock on t2). Then enter the next queries in the same fashion. The first thing that you will notice is that the first of these last updates seems to hang - because it is waiting for the other bteq to finish. If you enter et in the other bteq, then both will finish happily. However, if you enter the update into the second bteq, wait long enough (about a minute?) then one of them will get the "deadlock" message and the other will finish quite happily.

I am not sure which "logs" you are looking at, but once Teradata has knocked someone on the head, the locks (resulting in the deadlock) would have been released.

I hope this helps clarify the deadlock and block. For those that think I have too much time on my hands to write such a silly story (take note bossman), I don't - I just needed a little light relief from the tough SQL challenge I have right now.

God Luck

Glen Mc

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