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.
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.
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.