Deadlock on SELECT WITH UR

Frank Fella

Deadlock on SELECT WITH UR

Hello,  I have a SELECT that is suffering a deadlock.  It has a WITH UR clause on it.  One of my co-workers said it is impossible to get a deadlock when you have WITH UR because it 'doesn't use locks".  I disagree because (1) I am getting a deadlock and (2) I remember something about under certain conditions a SELECT WITH UR may attempt to require a latch.  I don't know if that is really true or simply an "alternate fact" generated by my aging brain.

It seems to possible to get a deadlock when using WITH UR, but I don't have any information to explain how that could happen. I am hoping one of you confirm that it is possible to get a deadlock on a SELECT WITH UR and point to some information I can use to back that up.

Thanks for your help!

Frank J. Fella, Developer, Progressive Insurance

[login to unmask email]

440-395-8597

Tushar Jha

RE: Deadlock on SELECT WITH UR
(in response to Frank Fella)

Hi Frank,

WITH UR does take mass delete lock.

So, that could be causing deadlocks.

Thank you.

 

 

Tushar Jha

Robert Krall

RE: Deadlock on SELECT WITH UR
(in response to Tushar Jha)

Do you know if you can query a table without cause a deadlock? For example I want to query a log table, but I dont want to cause a deadlock  when querying that table when rows are inserted/updated. I know its possible in MSSQL but not sure on DB2.  

Walter Janißen

AW: Deadlock on SELECT WITH UR
(in response to Robert Krall)
Hi

If you query the table with the option WITH UR, deletes, updates and inserts are possible. You only block mass deletes that are deletes without any WHERE-predicates. You even can query a table with that option if concurrently someone else issued a LOCK TABLE … IN EXCLUSIVE MODE.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Robert Krall [mailto:[login to unmask email]
Gesendet: Freitag, 21. April 2017 14:38
An: [login to unmask email]
Betreff: [DB2-L] - RE: Deadlock on SELECT WITH UR


Do you know if you can query a table without cause a deadlock? For example I want to query a log table, but I dont want to cause a deadlock when querying that table when rows are inserted/updated. I know its possible in MSSQL but not sure on DB2.

-----End Original Message-----
Attachments

  • image001.png (2.6k)

Tushar Jha

RE: Deadlock on SELECT WITH UR
(in response to Robert Krall)

To be more clear on what I earlier mentioned, DB2 uses a special type of lock called " Mass Delete " lock in share mode, when you are using WITH UR. On the other hand, mass delete SQLs ( Deletes without where predicate ) use " mass delete lock " in exclusive mode.I reckon this is done to insure, that all the records simply do not vanish, when an application is reading using WITH UR.You can read more about this lock in " Resource Serialization Redbook ".

I guess, if Frank may run a Locking report, it should show the type of lock acquired and the reason of the deadlock may become clear ( and if it is mass delete lock , then it should show that as well ).

I am not aware of how MSSQL  works.It could be working differently.

I would like to hear from the esteemed experts on this list, that why DB2 needs this " mass delete " lock for WITH UR transaction,    

 

Thank you.

 

Tushar Jha.

Walter Jani&#223;en

AW: Deadlock on SELECT WITH UR
(in response to Tushar Jha)
You are right. DB2 uses mass delete lock to prevent that the rows are all deleted while an application is reading the rows.

BTW: In the master address space you can see deadlock-messages and which threads are affected. You also can see, which statements are responsible for it. The message contains a STMTID, which you can use to query SYSPACKSTMT (if it’s static).

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Spezialisten
Technische Anwendungsarchitektur
Victoriaplatz 2
40477 Düsseldorf
Tel 0211 477-2928
Fax 0211 477-7146
[login to unmask email]

Vorsitzender des Aufsichtsrats: Dr. Markus Rieß
Geschäftsführung: Tomasz Smaczny (Vorsitzender),
Gary Robert Dashwood, Lothar Engelke, Joachim Fensch,
Bernd Jung, Dr. Sebastian Rapsch, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996
Von: Tushar Jha [mailto:[login to unmask email]
Gesendet: Freitag, 21. April 2017 14:55
An: [login to unmask email]
Betreff: [DB2-L] - RE: Deadlock on SELECT WITH UR


To be more clear on what I earlier mentioned, DB2 uses a special type of lock called " Mass Delete " lock in share mode, when you are using WITH UR. On the other hand, mass delete SQLs ( Deletes without where predicate ) use " mass delete lock " in exclusive mode.I reckon this is done to insure, that all the records simply do not vanish, when an application is reading using WITH UR.You can read more about this lock in " Resource Serialization Redbook ".

I guess, if Frank may run a Locking report, it should show the type of lock acquired and the reason of the deadlock may become clear ( and if it is mass delete lock , then it should show that as well ).

I am not aware of how MSSQL works.It could be working differently.

I would like to hear from the esteemed experts on this list, that why DB2 needs this " mass delete " lock for WITH UR transaction,



Thank you.



Tushar Jha.

-----End Original Message-----

Myron Miller

Deadlock on SELECT WITH UR
(in response to Tushar Jha)
I'm sure there are people more knowledgeable about this than me. But a "mass Delete" marks free space elements as free or available not touching any individual rows. So someone could be transversing the rows and then try to get the next row, and its totally not there any more (or at least totally unaccessible). remember the mass deletes are at a very very high level and not at a row level. Kind of like an Oracle PRUNED statement.


How can you access the table if the entire table becomes "free space". What do you read? There are not any indications any longer where any rows are. its all empty space according to DB2, so with UR can't read anything even though it might try. Rows do not exist. So what can you read. really? Thus with UR will fail if a mass delete is processing. To prevent the failing SQL, if a lock of mass delete is on the UR, at least then, the SQL won't fail trying to read non-existant rows. There will be a contention issue then and one of the two will fail according to locking rules.


At least that's my interpretation of this. If i run a mass-delete, there's no way you can run with UR, since no individual rows exist any longer at all in the table. No row markers, nothing. table is totally freespace. And with UR can't read freespace and find any rows. Individual deletes still have rows and markers where the rows exist or existed. mass Delete makes everything freespace basically at the entire tablespace level.




Thanks Myron W. Miller


________________________________
From: Tushar Jha <[login to unmask email]>
Sent: Friday, April 21, 2017 8:55 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Deadlock on SELECT WITH UR


To be more clear on what I earlier mentioned, DB2 uses a special type of lock called " Mass Delete " lock in share mode, when you are using WITH UR. On the other hand, mass delete SQLs ( Deletes without where predicate ) use " mass delete lock " in exclusive mode.I reckon this is done to insure, that all the records simply do not vanish, when an application is reading using WITH UR.You can read more about this lock in " Resource Serialization Redbook ".

I guess, if Frank may run a Locking report, it should show the type of lock acquired and the reason of the deadlock may become clear ( and if it is mass delete lock , then it should show that as well ).

I am not aware of how MSSQL works.It could be working differently.

I would like to hear from the esteemed experts on this list, that why DB2 needs this " mass delete " lock for WITH UR transaction,



Thank you.



Tushar Jha.

-----End Original Message-----

Patrick Bossman

RE: Deadlock on SELECT WITH UR
(in response to Myron Miller)

SQL with ur and DDL can also have contention.