Ravi Kiran V wrote:
But can you please explain me what does the above mean ??
Hi,
To prevent data corruption in cases when two sessions (users) try to modify the same table row, you must lock records.
Consider a simple case:
1. User A retrieves a record (for example containing customers data ) and displays it on the screen.
2. User B retrieves the same record and displays it on the screen.
3. User A modifies the address (street number), updates and commits changes ....
... but here data displayed on the screen of user B is not atomagically refreshed !!!
4. User B modifies the address (telephone number), updates and commits changes.
After a while user A retrieves the same row again and sees that his changes are lost
- street number is the same as it was before the update.
This situation is called 'lost update'.
Standard database automatic locking mechanism (database locks records on delete/update, unlock it on commit/rollback)
will not protect you from such kind of errors, you must apply some strategy to prevent this.
And here optimistic or pessimistic locking methods are used.
In pessimistic method the record is locked before the user A modifies values on the screen (for example user A clicks "update" button
which locks the row), and the lock is held until user A saves (commits) his changes to the database.
When user B in the same time will try to change data, it will get error 'record is locked'.
This method has some drawbacks - if user A locks a record, and then leaves his computer and goes to the lunch,
the record remains locked and no one can make changes to this row.
In the second method - optimistic locking - the row is not locked, but an application retrieves data (the row content),
holds old and new data in the memory ('new' - means data entered by the user), and in the end when user makes his changes
(he clicks "save changes" button), the application locks the row, retrieves current row content and then compare it
to the 'old' data stored in the memory
- if the current record content in the database is the same as record data in the memory, it means that nobody
has changed the row since the time the application retrieved data, and it is safe to store changed (new) data in the database.
And, in this case, new record content is updated and commit to the database, and in the end the row is unlocked.
But if this comparison is negative (row content and 'old' data in the memory differ) - it means that someone other
has changed data in the meantime, and we cannot save our changes to the database
because we can overwrite changes made by the other user.
So the application display the message 'Sorry, other user has changed the row - you must retrieve modified data
and start entering your changes from the beginning'.
In this method the row is locked only in very short time ('locking granularity' is low), but sometimes users must do their work twice.
Hope this helps and is clear enough ... my english is probably horrible