Page tree
Skip to end of metadata
Go to start of metadata

In a multiuser environment, there are two models for updating data in a database: optimistic concurrency and pessimistic concurrency. Our architecture is designed to encourage the use of optimistic concurrency, whereas, Oracle Forms uses pessimistic concurrency.

 

 “Pessimistic concurrency involves locking rows at the data source to prevent other users from modifying data in a way that affects the current user. In a pessimistic model, when a user performs an action that causes a lock to be applied, other users cannot perform actions that would conflict with the lock until the lock owner releases it. […] Therefore, in a pessimistic concurrency model, a user who updates a row establishes a lock (In Oracle forms, the record locking occurs as soon as you start changing the value of an item). Until the user has finished the update and released the lock, no one else can change that row. For this reason, pessimistic concurrency is best implemented when lock times will be short, as in programmatic processing of records. Pessimistic concurrency is not a scalable option when users are interacting with data and causing records to be locked for relatively large periods of time.”

Citation from Microsoft’s documentation (found at http://msdn.microsoft.com/en-us/library/aa0416cz(v=vs.110).aspx)

 

In Oracle Forms applications, record locks can be in place for a long time, since it locks as soon as you start changing a field and doesn’t release the lock until data is committed (either programmatically or through the save button). If you picture a situation where a user, opens a form, places focus on an item, changes its value and goes out to lunch without saving data, then you have a record locked for a seriously long time. Which is clearly the opposite of the recommendation of usage of pessimistic concurrency.

 

“By contrast, users who use optimistic concurrency do not lock a row when reading it. When a user wants to update a row, the application must determine whether another user has changed the row since it was read. Optimistic concurrency is generally used in environments with a low contention for data. Optimistic concurrency improves performance because no locking of records is required, and locking of records requires additional server resources. Also, in order to maintain record locks, a persistent connection to the database server is required. Because this is not the case in an optimistic concurrency model, connections to the server are free to serve a larger number of clients in less time.

In an optimistic concurrency model, a violation is considered to have occurred if, after a user receives a value from the database, another user modifies the value before the first user has attempted to modify it. “

Citation from Microsoft’s documentation (found at http://msdn.microsoft.com/en-us/library/aa0416cz(v=vs.110).aspx)

 

To conclude, in our migration process, the usage of optimistic concurrency instead of pessimistic concurrency (as in oracle forms) introduces expected differences. While previously a user would be prevented from trying to edit a record, now a user is notified on the save moment, that someone else has already modified the same data. To ensure data consistency, the user is not allowed to proceed with the operation. It's that way by design. This is common in most modern applications, as pretty much all the technologies for building modern applications favor the optimistic approach.

Both approaches, preserve the data integrity, the main difference is in terms of the locking timing, which in some situations also impacts in the user experience. The optimistic approach also scales better and uses less database resources as the number of locks reduce considerably and many deadlocks are completely avoided.