|
View previous topic :: View next topic
|
| Author |
Message |
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
when I issue 'CREATE INDEX' against a table, sql execution failed with below error message:
| Code: |
CREATE INDEX DEVTEST.CMITXSTA1 03660099
ON DEVTEST.CMKTXSTA(TXN_STUS) 03670099
USING STOGROUP DEVTEST 03680099
ERASE NO 03690099
BUFFERPOOL BP2 03700099
CLOSE NO; 03710099
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -911, ERROR: THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK
DUE TO DEADLOCK OR TIMEOUT. REASON 00C9008E, TYPE OF RESOURCE
00000100, AND RESOURCE NAME DEVTEST
DSNT418I SQLSTATE = 40001 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXICIX SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -190 -100 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF42' X'FFFFFF9C' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE618I ROLLBACK PERFORMED, SQLCODE IS 0 |
From the source type code, we can see it's due to deadlock of database.
But this is what I don't understand: How can a whole database would be locked, and thus even an index cannot be created? |
|
| Back to top |
|
 |
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
A "deadlock" is not the same as a "lock"  |
|
| Back to top |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007
Posts: 691
Location: Earth
|
|
|
|
Reason code 00C9008E indicates timeout and not a deadlock.
Did you try executing this request again? Getting a -911 doesn't mean that it was your query that was trying exclusive control of resource. Maybe your query just needed a S lock on database but it was exclusively held by some other process.
Try looking for claimers using DIS DATABASE command.
Don't know if this is relevant but, SQLERRD(3) should have contained the reason code but it is showing zero. Strange.. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| How can a whole database would be locked |
Why do you believe it is . . . 
You need to learn what a "deadlock" really is. It is also known as a "fatal embrace".
If it is a deadlock (rather than a timeout) it is because of a problem with the code or with scheduling. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| If the phrase is not familiar (also "deadly embrace") it is when A is calmly waiting for B and B is calmly waiting for A. One or other, or both, has to die before things move on. In your case, DB2 prevented this occuring by doing the killing up front. |
|
| Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
In fact , I know exactly what 'deadlock' is.
But in my case, I'm defining an index against a table, how can there be a chance to get deadlock?? |
|
| Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA
|
|
|
|
| Code: |
| DUE TO DEADLOCK OR TIMEOUT. |
The error message EXPLICITLY tells you that your problem could be a deadlock OR timeout. Since you obviously do not know the meaning of the word "or", you have leaped to the wrong conclusion and believe that a deadlock is involved even though your problem is not a deadlock. As long as you continue to believe wrongly, there is no way for us to help you. |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
dejunzhu,
Did you check the MSTR log, it would have information on what was holding it.
Was there any parallel activity happening at that time on the database like selects or any other utilities
Regards,
Sushanth |
|
| Back to top |
|
|
|
|
