• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

do most databases writelock a row when it's changed?

 
Yuriy Zilbergleyt
Ranch Hand
Posts: 429
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

I know that Oracle databases writelock a row when a value on it is changed, even if the "changed" value is the same as the old value, and the writelock is only released when the transaction is committed. Do all or most databases function the same way? I'm investigating the possibility of using JDBC w/ setAutoCommit(false) to synchronize distributed components.

Thank you,
Yuriy
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34870
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Yuriy Zilbergleyt:
Do all or most databases function the same way?

Yuriy,
Every database I used functioned that way. The database doesn't know that you are changing the value to the same value, just that it has been edited. In order to know there is no change, the database would have to go back through the transaction logs.
 
Yuriy Zilbergleyt
Ranch Hand
Posts: 429
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks, Jeanne!

In that case there should be no problem with using JDBC as a synchronization mechanism among distributed components (like EJBs), right?

Thank you,
Yuriy
 
Maximilian Xavier Stocker
Ranch Hand
Posts: 381
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Almost all databases will exclusively lock at LEAST the row you are updating/inserting but there is other behaviour that you may want to consider.

Many databases lock much more than the individual row. Some (like MS-SQL) lock the page (of allocated disk space containing the row) when they update rows. This means that several rows will be locked at the same time.

Further some other databases have to get exclusive locks on the whole table in order to make updates. (MySQL for one). This means that all the other rows in the table are locked (for writing) until finished.

I am not sure how you intend for your synchonization to work but I think you should be aware that yes the row you are updating will be locked but so possibly will many others get locked as well.
 
Yuriy Zilbergleyt
Ranch Hand
Posts: 429
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
All right, thanks for the info Maximilian!

Yuriy
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic