• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • paul wheaton
  • Ron McLeod
  • Devaka Cooray
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Piet Souris
Bartenders:

Strange transcation question

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

Thanks in advance,
Tom
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic