Row Locking issue

[black lee via DBMonster.com]

Hi

here is what we want:
when process A selects a row for update, process B will only be allowed to
read only.

We are using DB2V8.2 Express Edition V8.2, and ODBC Driver is IBM DB2 ODBC
Driver. our application is C application.

the below is the SQL statements we have tried to lock the row, but none of
them works for me.

SELECT t0.EMP_FNAME, t0.EMP_LNAME, t0.EMP_TITLE from DBO_EMP t0 where
t0.KEY = 60 WITH RS USE AND KEEP UPDATE LOCKS

SELECT t0.EMP_FNAME, t0.EMP_LNAME, t0.EMP_TITLE from DBO_EMP t0 where
t0.KEY = 60 FOR UPDATE

SELECT t0.EMP_FNAME, t0.EMP_LNAME, t0.EMP_TITLE from DBO_EMP t0 where
t0.KEY = 60 FOR READ ONLY WITH RS USE AND KEEP EXCLUSIVE LOCKS

we also alter the DBO_EMP table with the command:
ALTER TABLE DBO_EMP LOCKSIZE ROW

I would much appreciate it if you guy could help me on this.

- BLACK

[Mark A]

"black lee via DBMonster.com" <fo***@nospam.DBMonster.com> wrote in message
news:52******************************@DBMonster.co m...

Hi

here is what we want:
when process A selects a row for update, process B will only be allowed
to
read only.

We are using DB2V8.2 Express Edition V8.2, and ODBC Driver is IBM DB2 ODBC
Driver. our application is C application.

the below is the SQL statements we have tried to lock the row, but none of
them works for me.

SELECT t0.EMP_FNAME, t0.EMP_LNAME, t0.EMP_TITLE from DBO_EMP t0 where
t0.KEY = 60 WITH RS USE AND KEEP UPDATE LOCKS

SELECT t0.EMP_FNAME, t0.EMP_LNAME, t0.EMP_TITLE from DBO_EMP t0 where
t0.KEY = 60 FOR UPDATE

SELECT t0.EMP_FNAME, t0.EMP_LNAME, t0.EMP_TITLE from DBO_EMP t0 where
t0.KEY = 60 FOR READ ONLY WITH RS USE AND KEEP EXCLUSIVE LOCKS

we also alter the DBO_EMP table with the command:
ALTER TABLE DBO_EMP LOCKSIZE ROW

I would much appreciate it if you guy could help me on this.

- BLACK

SELECT t0.EMP_FNAME, t0.EMP_LNAME, t0.EMP_TITLE from DBO_EMP t0 where
t0.KEY = 60 FOR UPDATE

should keep a lock on the row until a commit is issued (auto-commit or
explicit commit statement). The lock will prevent other select for updates
on the same row, and other updates/deletes, but allow reads. This will
create a lock wait situation for other select for updates and
updates/deletes.

If it is not working, then you probably have auto-commit on.

[black lee via DBMonster.com]

Hi Mark,

thank you a lot for answering my question. how can I set auto-commit off?

- black

--
Message posted via http://www.dbmonster.com

[Jurgen Haan]

black lee via DBMonster.com wrote:

Hi Mark,

thank you a lot for answering my question. how can I set auto-commit off?

- black

Autocommit is a client setting.
If you're using the DB2 CLP, check out the cli cfg (db2 get cli cfg).
If needed, you can alter the autocommit setting.

-R-

[black lee via DBMonster.com]

yes, the auto-commit was on, I have turned it off. but I am still unable to
lock the record if using the SQL:

SELECT t0.EMP_FNAME, t0.EMP_LNAME, t0.EMP_TITLE from DBO_EMP t0 where
t0.KEY = 60 FOR UPDATE

I can see the record is locked from the monitor, but another process still
can access the record for update. I don't understand why.

it will lock the record if I use the SQL:

SELECT t0.EMP_FNAME, t0.EMP_LNAME, t0.EMP_TITLE from DBO_EMP t0 where
t0.KEY = 60 WITH RS USE AND KEEP UPDATE LOCKS

--
Message posted via http://www.dbmonster.com

[Philip Sherman]

"Use and keep update locks" tells the database to use update type locks
instead of what it would normally decide to use. Since this is working;
I suspect that you are not obtaining the "U" lock on the row that you're
expecting the "for update" version to get. You've shown only the
SELECT statement; not anything else about the processing.

Your SELECT statement uses a cursor to provide positioning on the rows.
Assuming you are using CS isolation; the "U" lock on the row to be
updated, which prevents others from reading the row, should not be
obtained until the row had been read with a FETCH statement. Nothing in
your posts says anything about where you stopped the process to examine
the lock.

You stated that you can see the locks which implies that you have looked
at them using some tool. How about posting the actual lock states on
both the table and the row that BOTH applications have. Looking at the
lock differences for the two statements (that worked and didn't work)
will also give you clues as to what is happening.

Phil Sherman

black lee via DBMonster.com wrote:

yes, the auto-commit was on, I have turned it off. but I am still unable to
lock the record if using the SQL:

SELECT t0.EMP_FNAME, t0.EMP_LNAME, t0.EMP_TITLE from DBO_EMP t0 where
t0.KEY = 60 FOR UPDATE

I can see the record is locked from the monitor, but another process still
can access the record for update. I don't understand why.

it will lock the record if I use the SQL:

SELECT t0.EMP_FNAME, t0.EMP_LNAME, t0.EMP_TITLE from DBO_EMP t0 where
t0.KEY = 60 WITH RS USE AND KEEP UPDATE LOCKS