DB2 Locking 2

[Member 310024]

Environment: IMB Z series Mainframe, MVS, COBOL/DB2

DB2 locking has always seemed a bit mysterious to me.
I understand the concept of locking, and if I knew
how to explicitly (manually) lock a record, I would
lock it when I needed it locked.

Is there a DB2 intrinsic that you can call, when you
want to lock a record?

I recently stuffed up when a delete failed with -911.
I selected the primary key (not a cursor) thinking
that if someone else has it, my select would fail.

After the sqlcode was not +100, I then went ahead with
my delete but got a -911 on the delete, which got sent
thru the non zero sqlcode terminate routine.

All the records in the buffer that were awaiting commit,
got committed, (because I didn't say rollback?) which I didn't want.

Is it the attempted delete that I should be checking
for -911, as the means of determining if the record
is already locked by another user?

Is there some way I can determine the lock status
with my non-cursor select of the primary key, and
then if it isn’t, lock it for myself, and when I'm
finished - unlock it?

The locking seems to be automatically done, outside
of my control (is it not?).

Thanks in advance for any comment.

 

[MarcLodge]

Terminate,

Locking is a hugely complicated subject and often gives unusual results. Craig Mullins has an excellent series of articles:

http://www.dbazine.com/db2/db2-mfarticles/mullins3/

http://www.dbazine.com/db2/db2-mfarticles/mullins4/

http://www.dbazine.com/db2/db2-mfarticles/mullins5/

that may help.

I'll try to explain from your description of your problem what I think is happening.
The delete tries to take an exclusive lock and if anybody else has got hold of it in a shared lock (fileaid or maybe in a cursor), then -911 is returned.
The select tries to take a shared lock and if anybody else has got hold of it in a shared lock, that's fine.

The above is a broad generalisation as the locking can be affected by the bind options of repeatable read or cursor stability amongst other things, but the scenario as I've described it is largely true of most installations, I believe.

Normal abend processing of an unexpected SQLCODE like -911 will back out any non committed updates and I'd have a look at this in detail as I would be quite worried that updates are being committed in this instance.

In order to get round your select/delete locking problem, have a look at FOR UPDATE OF clause in a cursor, as I think this might give you the answer you are looking for.

FInally, if you want to test this processing out, what I often do is go into fileaid and change the row, but not save it. This causes a lock that is detectable from within a program and will give a -911. An alternative mainframe solution is to use QMF to issue an update statement, but wait when QMF asks whether you want to update the row as it will have taken an exclusive lock.

Hope some of this helps, sorry for the length.


Marc

 

[ddiamond]

Marc,

I was not aware of the "FOR UPDATE OF" clause, so I looked it up. Looks fairly useful. Have a star for suggesting it.

- Dan

 

[Member 310024]

Thanks Marc - no apology for length of response, required.