Strange transcation question

[Tom Chen]

Hi,
Two transcations are runned concurrently. They update the same table, but different records. Isolation level is set to Read Commited. The transaction is implemented as a stateless CMT session bean.
(1)
transcation begin
update record1
(let the thread wait for 60 seconds on purpose....)
update rec2
transcation end
(2)
transcation begin
update rec3
update rec4
transcation end
Result:
the 2nd transcation need to wait for the 1st one.
Can anyone explain why would this happened? How to fix this problem.
Thank you
Tom
[ January 12, 2003: Message edited by: Tom Chen ]
[ January 12, 2003: Message edited by: Tom Chen ]

[Tom Chen]

Hi,
Does anyone know something about it?
Thank you,
Tom

[Kyle Brown]

This may be no mystery at all. It may be that your database does not support (or is not set up for) row-level locking and instead locks at a higher level (like the table, or a "group" of rows). So, T2 actually starts first, obtains the lock through updating, T1 needs the lock to update its second row, can't get it, so it waits, T2 completes and releases the lock, and T1 completes.
Kyle

[Tom Chen]

Hi Kyle,
Yes, I agree with you that the database is not config support row-level locking. I'm using weblogic 6.1 + IBM DB2 v7.
However, after I config the table:
Alter table account locksize row
and run the two transaction again, the result remains the same: When a transaction updating a record, it seems to lock the whole table.

Thank you,
Tom
[ January 12, 2003: Message edited by: Tom Chen ]
[ January 12, 2003: Message edited by: Tom Chen ]
[ January 12, 2003: Message edited by: Tom Chen ]

[Kyle Brown]

I seem to remember (but be sure -- ask a DBA) that DB2 actually has a fairly small set of row locks that it uses -- you may want to look into increasing the number of available locks...
Kyle

[Tom Chen]

Originally posted by Kyle Brown:
I seem to remember (but be sure -- ask a DBA) that DB2 actually has a fairly small set of row locks that it uses -- you may want to look into increasing the number of available locks...
Kyle

Hi,
I believe that the lock level of the table is set to row-level. The table is so samll that it never reach the limitation of available locks.(Only 4 records, it's just for testing).
The problem remains the same. Why? Why?
Thank you again,
Tom

[Tom Chen]

Hi,
Does anyone have some more idea about this problem?

Thanks in advance,
Tom