Question:  I have a large application that will be doing large updates and I want to know the ways that you can lock rows and ensure integrity.  There are other approaches to setting exclusive locks on rows, and I want to understand the best practices for Oracle table locking.

Answer:  There are three main approaches used, each with its own advantages and disadvantages:

• select for update - This holds an exclusive lock on the target row and is 100% reliable.  The downside is with disconnected session which may require DBA intervention to release the locks.  In general, the "select for update" is not used in web-based systems, or on applications with unreliable network connectivity.
   

• Re-read - One common method is to write the application to re-read the rows before updating, and notify the end-user that the row has changed since it was first displayed on their screen.  With this approach, it's important to track changes to everything that is displayed to the end-user, not just the updated item.  This is because a non-updated screen item may have influenced the update value.
   

• Date-time stamp - Another approach is to tag all rows with a last_updated_date column (of the date datatype).  At screen map-out time, the sysdate is stored within the application (i.e. a variable named map_out_time).  At update time, the application re-reads the row and compares the value of the last_updated_date column to the map_out_time value.

Oracle locking notes

See my related notes on Oracle locking and lock strategies: