| View previous topic :: View next topic |
| Author |
Message |
edkir98
Beginner
Joined: 27 Aug 2007
Posts: 102
Topics: 42
Location: Chennai
|
 Posted: Mon Dec 01, 2008 10:58 am Post subject: Difference between -904 and -911 |
 |
|
i'm not sure if this question might sound very basic.. but i'm not clear.
i read from the db2 9 reference on locks that when a particular query is not able to gain a lock with the time specified in locktimeout parameter then the query would not execute. i assume that this would give a -911. am i correct?
Again i read that
| Quote: | | "The best way to illustrate how a deadlock can occur is by example: Suppose Transaction 1 acquires an Exclusive (X) lock on Table A and Transaction 2 acquires an Exclusive (X) lock on Table B. Now, suppose Transaction 1 attempts to acquire an Exclusive (X) lock on Table B and Transaction 2 attempts to acquire an Exclusive (X) lock on Table A. Processing by both transactions will be suspended until their second lock request is granted. However, because neither lock request can be granted until one of the transactions releases the lock it currently holds (by performing a commit or rollback operation), and because neither transaction can release the lock it currently holds (because both are suspended and waiting on locks), the transactions are stuck in a deadlock cycle." |
now this case in -904..why does this query go into deadlock and does not timeout as with the locktimeout parameter..
Can someone explain when -911 occurs and when -904 occurs..
hope i made it clear..
_________________
Thanks |
|
| Back to top |
|
 |
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Tue Dec 02, 2008 9:01 am Post subject: |
|
|
-911's and -913's are similar with the exception that the -911 also issues a ROLLBACK.
a -904 indicates that a resource is unavailable.
I have usually encountered -911's and -913's when I am in contention with another task.
-904's usually come when db2 knows that I will be permitted no locks whatsoever.
I will not receive a -904 because someone else also is beating on the table. I receive -904's when I can do nothing with the table - indexes messed up, table not accessable (-pending status, etc...)
you can go thru the reason codes for -904, -911/-913 and get a feel for the the different reasons for the -904 and the -911/-913.
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
jsharon1248
Intermediate
Joined: 08 Aug 2007
Posts: 291
Topics: 2
Location: Chicago
|
| Posted: Tue Dec 02, 2008 11:07 am Post subject: |
|
|
| Quote: | | why does this query go into deadlock and does not timeout as with the locktimeout parameter |
DB2 performs deadlock detection. When 2 processes are preventing each other from accessing locked resources held by the other, DB2 selects one to survive, and terminates the other. DB2 issues a rollback on one unit of work, and then allows the other to continue. If DB2 just let each unit of work go to timeout, it's possible that both would timeout since the second UOW could not proceed until the rollback completed. With a deadlock, DB2 picks a winner and a loser. Unfortunately, the winner is usually the one that created the problem in the first place. DB2 rollsback the UOW with the least amount of work. That's usually the guy that is issuing frequent commits, and the winner is the guy that's not issuing commits frequently.
One other point about -904's. You could cause a -904 on yourself. If you don't issue commits frequently enough, and there's other processes accessing your resources, you could exceed a max number of locks allowed. DB2 would want to escalate your lock to a table level lock, but since there are other concurrent processes, that wouldn't be allowed. The end result is that your process keeps acquiring locks until the number of locks exceeds the max and then you get the -904.
The bottom line, is that you must create your applications with commit frequency in mind. Commits based on time are the best. Commits based on record/row counts are ok in most instances. If you're designing the Roll Royce, you could code your programs to retry SQL following -904's and -911's. That's a little tricky as you need to reposition cursors and files. |
|
| Back to top |
|
|
edkir98
Beginner
Joined: 27 Aug 2007
Posts: 102
Topics: 42
Location: Chennai
|
| Posted: Thu Dec 04, 2008 5:16 am Post subject: |
|
|
Thanks jsharon and Dick..
_________________
Thanks |
|
| Back to top |
|
|
vjkumk
Beginner
Joined: 28 Sep 2005
Posts: 98
Topics: 33
|
| Posted: Thu Dec 04, 2008 12:33 pm Post subject: |
|
|
| sqlcode - 904 also occurs when you try to load the table and the database/tablespace status is in RW COPY.A force start of database will solve the problem. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Thu Dec 04, 2008 1:54 pm Post subject: |
|
|
| vjkumk wrote: | | sqlcode - 904 also occurs when you try to load the table and the database/tablespace status is in RW COPY.A force start of database will solve the problem. |
vjkumk,
When using ACCESS(FORCE), it is up to the user to ensure the consistency of data. Unless you are 100% sure that all the consistencies are in tact , I would never suggest using access FORCE |
|
| Back to top |
|
|
jsharon1248
Intermediate
Joined: 08 Aug 2007
Posts: 291
Topics: 2
Location: Chicago
|
| Posted: Thu Dec 04, 2008 2:34 pm Post subject: |
|
|
vjkumk
I'll add my 2 cents to kolusu's warning. ACCESS(FORCE) should not be used unless there are no other options and you understand and are willing to accept the risks. For the specific problem you mentioned, run the image copy or run the REPAIR utility. The next time you run the LOAD, specify LOG YES, or, LOG NO with the NOCOPYPEND option. ACCESS(FORCE) can easily put you in a position where a restore is your only option. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
