Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

In which case, database will be locked?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Fri Aug 16, 2013 2:47 pm    Post subject: In which case, database will be locked?
Reply with quote

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
View user's profile Send private message

Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Fri Aug 16, 2013 7:57 pm    Post subject:
Reply with quote

A "deadlock" is not the same as a "lock" icon_evil.gif
Back to top
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 685
Location: Earth

PostPosted: Fri Aug 16, 2013 8:13 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Aug 16, 2013 8:14 pm    Post subject:
Reply with quote

Hello,

Quote:
How can a whole database would be locked
Why do you believe it is . . . icon_confused.gif

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
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Fri Aug 16, 2013 8:34 pm    Post subject: Reply to: In which case, database will be locked?
Reply with quote

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
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Sat Aug 17, 2013 6:48 am    Post subject:
Reply with quote

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
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7913
Location: Bellevue, IA

PostPosted: Sat Aug 17, 2013 6:55 am    Post subject:
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Aug 19, 2013 10:44 am    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts IMS Database backup info ashek15 IMS DB/DC 14 Wed Nov 16, 2016 5:29 am
No new posts Getting list of tables defined under ... kishpra DB2 2 Wed Aug 24, 2016 10:36 am
No new posts Case insenstive in INCLUDE Condtion smileseenu DFSORT/ICETOOL 2 Mon Feb 15, 2016 8:42 pm
No new posts Database access from COBOL amitmahalkar DB2 1 Wed Jan 27, 2016 11:33 am
No new posts How steps execute in Job in case of a... richiewalia JCL & VSAM 4 Tue Jan 12, 2016 12:34 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us