I have two tables, let's call them A and B.

I have a view, call it V, which is defined as:
SELECT * FROM A, B WHERE A.ID = B.ID

Now I have an application #1 that has to update A and B. So I did an update to A, then I updated B.

It's a live system, though, so in middle, applicatoin #2 asked for "select * from V".

SQL0911 rc=2. From the event monitor, I can see that #1 has a lock on A, #2 has a lock on B.

I have re-written #1 so it updates B then A, but it occurs to me that I have no guarantee from DB2 that "select * from V" will always get lock B before A. In fact the way the view is defined suggests the opposite.

Help.

Bumping, hopefully someone has an idea.

I expect the only solution is just to keep the time between the update on A and the update on B as short as possible to reduce the possibility of someone grabbing the B lock before we get a chance.

jono

What isolation level the SELECT uses? Can you change it to a lower level?

You may want to do "select ... for update" on both A and B _before_ actually updating it - at least you'll get a lock timeout instead of a deadlock, which you can catch and re-try...

How about the DB2 registry variables DB2_SKIPINSERTED or DB2_SKIPDELETED and DB2_EVALUNCOMMITTED ??

ok, the last would not help since no where-condition is specified.
But DB2_SKIPINSERTED sounds good ...

Both run as CS, so there isn't a practical lower level.

I'll try the SELECT ... FOR UPDATE, that might work.

I've set DB2_SKIPINSERTED, DB2_SKIPDELETED, DB2_EVALUNCOMMITTED, to no effect. Since it's an UPDATE, only EVALUNCOMMITTED really applies, and because the row is a matching row it still needs to lock.