• Post Reply Bookmark Topic Watch Topic
  • New Topic

Strange transcation question  RSS feed

 
Tom Chen
Ranch Hand
Posts: 46
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 46
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
Does anyone know something about it?
Thank you,
Tom
 
Kyle Brown
author
Ranch Hand
Posts: 3892
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 46
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author
Ranch Hand
Posts: 3892
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 46
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 46
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
Does anyone have some more idea about this problem?

Thanks in advance,
Tom
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!