SQL Server is a multi user database server which allows multiple users to modify data at the same time. To protect data it provides locking mechanism. There are different locking mechanism such as pessimistic locking and optimistic locking.
To understand locking, we should understand how data is updated in database environment. When data is modified first of all it is written into transaction log file and then it is committed into data file. So, there are two step processes in data updating.
In case of pessimistic locking, lock is taken as soon as user begins modifying the data and lock is released after updating the data. It means that as soon as data is inserted into transaction log file, lock is taken and lock is released when entry is committed in data file. But in case of optimistic locking, lock is taken after data is inserted into transaction log file and lock is released when entry is committed in data file.
So, in case of pessimistic locking, for longer duration lock is taken compared to optimistic locking. It means that in case of pessimistic locking, no other user can access the data when another user is modifying the data. But in case of optimistic locking, other user can access the data when another user is modifying the data. The lock is taken when data is already modified but is not committed. In case of pessimistic locking lock is taken when data is neither modified nor committed.
In case of optimistic lock there is probability of write conflict when multiple users are modifying the data at the same time. The last user who has modified the data will override all modifications made by others. So in case of optimistic lock there is probability of dirty write.
continued...
No comments:
Post a Comment