Nothing got wrong with DB2 COMMIT, db2 cfg, ...
Even you did an "insert some_id+1 ..." immediately after you did "select
max(some_id) from .... " in session1. This will not block the operations
which submitted by the other processes/threads. Before the INSERT, DB2
will not prevent the other SELECT MAX(some_id) from this table.
So the following case could happen,
Suppose in session A:
Begin a transactionA
Select max(id) from mytable;
.... at this moment Session B also Begin transaction B, and submit the
SELECT MAX ?BR>Session A will do the INSERT id+1 ...
.... if Session B wants to do INSERT too, it has to wait ...
The simple solution:
Lock the table before you do SELECT MAX ... release the lock after
INSERT ...
This will cause potential lock contention, and hurt concurrency.
I will suggest you use SEQUENCE to replace the column SOME_ID. Please
check with the db2 manual.
You can also use Identity Column, but I will suggest Sequence ...
BTW, AFAIK, db2 introduce SEQUENCE since V7.2 or V7.1 + fixpak3.
Regards,
FRX
===================
From: sergiu (sg******@qct.ro)
Subject: DB2 commit synchronization
Newsgroups: comp.databases.ibm-db2
Date: 2003-09-29 01:33:37 PST
Hello,
I am developing a java application (client-server), that is supposed
to support lots of users (over 100). I am encoutering a problem with
DB2 commit statement.
There is an portion of code on the server side, that needs to be
thread safe. Therefore I've made that method synchronized. Inside that
method, i have to do an "select max(some_id) from .... " and
imediately after i do an "insert some_id+1 ...". I close the
connection (it is obtained through JNDI datasource), and exit the
method.
However, if multiple clients try to access that method at the same
time, they are put on hold until one of them terminates (witch is the
behaviour i expect). But, if a very short time passes between 2 runs
in that method (less than 20ms), the second client, when does the
"select max(some_id) ..." it gets the old id, not the one updated by
the previous client. I have tried in many ways to get the damn thing
working (setted autocommit false, and just before closing doing an
manual commit), but it seems that the JDBC driver (at least) is
returning from the commit method, and not actually doing the commit
(if i wait 30ms everything is fine, but this is not what i want). I'm
sure i can find a workaround for exactly this situation (static
variables,etc.) but what will I do if i encounter a situation in
witch this workaround doesn't apply?
Is there any way to force DB2 to make a commit when i tell it so, and
not to return from the statement until everything is written on the
disk?
I'm using DB2 v7.1 on an RedHat 7.3 machine with an ext3
filesystem(dual proc ... etc.).
Thanks,
Sergiu.
