soft lock problem

[xixi]

hi, we are using db2 udb v8.1 ESE with type 4 jcc driver. since DB2
can't support pessimistic locking , and our application required that,
which means when both users try to access the same record with intent
to update, the first one grab the record will create a row lock on the
current row reading, before it update, other user can't access the
same record with intent for update read, but can access with read only
type. so when the first user update the row, it will release the row
lock and let the other user with intent for update read to grab the
record. In order to do this, i think i can't use transaction and
isolation level control by the database, db2 support only optimistic
locking ,isn't it? so i think might need to use column locking (soft
lock), which means with extra column like timestamp, but the
disadvantage is when power off, some one who create the lock will
leave the flag there and when server start again, i have to clean up
all thousands tables timestamp column to start over, does anyone has
any idea how to do it efficiently? thanks

[Serge Rielau]

Xixi,

My understanding is that optimistic lockis what you want and pessimistic
locking is what DB2 does.
Try this (not validated)

CREATE TABLE T(pk not null primary key, c1 INTEGER, version INTEGER)
;
CREATE TRIGGER trg1 NO CASCADE BEFORE UPDATE ON T OF (version)
REFERENCING NEW AS n OLD AS o FOR EACH ROW MODE DB2SQL
SET n.version = CASE WHEN n.version = o.version
THEN -n.version
ELSE raise_error('38 000', 'version mismatch')
END
;
-- trg2 must be defined after trg1!
CREATE TRIGGER trg2 NO CASCADE BEFORE UPDATE ON T
REFERENCING NEW AS n OLD AS o FOR EACH ROW MODE DB2SQL
SET n.version = CASE WHEN n.version = -o.version
THEN n.version + 1
ELSE raise_error('38 001',
'version not specified')
END
;
CREATE TRIGGER trg3 NO CASCADE BEFORE INSERT ON T
REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL
SET n.version = 1
;

SELECT c1, version INTO v_c1, v_version FROM T WHERE pk = 5;
.....
UPDATE T SET c1 = v_c1, version = v_version WHERE pk = 5;
....
From CLI you can also use optimistic locking through scrollable cursors.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab