Originally posted by Rene Larsen:
If you are using a Oracle database, you can use 'select xx from yy where xx=tt FOR UPDATE' for locking the selected rows. You release it again by calling commit() (or rollback() if error) on the connection.
This is known as pessimistic locking. When you are updating a row, the database will perform locking behind the scenes that prevents other transactions from updating that row. As Rene posted, Oracle allows you to lock the row when you read it using the 'FOR UPDATE' syntax in the SELECT statement. In pessimistic locking, you lock the row when you perform the read.
Another type of locking you could use is called optimistic locking. In this case, you handle the conflict in the UPDATE statement, that is, when you perform the write. Suppose you have performed a query, saved a read-only local copy of the data, presented the data to a user, and allowed the user to make changes. Now it is time to update the row:
When you attempt this update, if some other transaction has changed some of the data you are trying to change, the UPDATE will fail and you know that you have to deal with the situation where another transaction has changed the data between your read and your write. If the update succeeds, then there has been no change between your read and your write. In optimistic locking, you lock the data when it is time for the write, and you check the data to ensure it has not changed.
KMukhar
http://home.earthlink.net/~kmukhar/ [ November 18, 2002: Message edited by: Kevin Mukhar ]