Reputation: 489
I have read many articles on when to use Optimistic vs. Pessimistic locking and my basic understanding is :
I haven't been able to find any useful examples of when it would be smart to choose pessimistic locking (it seems that optimistic is usually preferred).
Would be very helpful if someone could answer for a specific example: say we have a Credit Card/account table and we want to authorize a transaction. Integrity is very important and I don't see why it would ever be useful to allow concurrent updates. Is this an example where we would use pessimistic locking?
Upvotes: 4
Views: 6918
Reputation: 317
When Read Committed Snapshot Isolation level is set to OFF it uses shared lock. Shared lock is placed on rows which are being read by current transaction and it prevent other transactions from updating the rows which are being read until current transaction reads the rows. Lock on the row is released immediately once the row is read. Similarly this shared lock is placed on the rows which are being updated by current transaction and it prevents other transactions from reading the rows until it updates the rows and commit/rollback the transaction.
Other than row level lock, shared lock uses page level lock and table level lock. Page level lock is released once the next page is read and table level lock is released once the statement is finished.
Shared lock is also known as pessimistic read since it doesn't allow other transactions to update the rows which are being read. Shared lock is also known as pessimistic write since it doesn't allow other transactions to read the rows which are being updated.
When Read Committed Snapshot Isolation level is set to ON it uses snapshot of last committed data to the underlying table before the statement in the transaction starts. It doesn't use any locking mechanism here. Its optimistic read since it allows transactions to update the records which are being read by current transaction. Its pessimistic write since it exclusively locks the records on the snapshot it took prior to start of update statement and other transactions read last committed data on the underlying table.
I've created salary table and inserted 1 million rows in it. Set Read Committed Snapshot OFF and one transaction is reading all the rows from it and almost at the same time another transaction will update all the rows of the table.It will take time for second transaction to update all the rows (Pessimistic Read) whereas if you execute both the transactions when read committed snapshot is ON then the second transaction will take only the update query execution time without any waiting time of first transaction(Optimistic Read).
T1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION
SELECT * FROM SALARY
COMMIT TRANSACTION
T2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION
UPDATE SALARY SET AMOUNT = 7500
COMMIT TRANSACTION
Set Read Committed Snapshot OFF and run below two transactions. first T1 and then T2 almost at the same time.
T1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION
SELECT * FROM SALARY WHERE ID IN (1,2,3,4,5)
WAITFOR DELAY '00:00:10'
SELECT * FROM SALARY WHERE ID IN (1,2,3,4,5)
COMMIT TRANSACTION
T2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION
UPDATE SALARY SET AMOUNT = 6500 WHERE ID IN (1,2,3,4,5)
WAITFOR DELAY '00:00:30'
COMMIT TRANSACTION
You'll see that second select statement of T1 transaction will wait until T2 commits the data. Its because T2 acquired shared lock prior to execution of update query.once it will commit the data T1 select statement will execute. Update statement here is an example of pessimistic write.
pessimistic write is there when you set SNAPSHOT ON on the above two transactions but since second select statement of T1 can take snapshot of last committed data it will not wait for T2 to complete and update statement of T2 also takes the snapshot of last committed data and apply exclusive lock to update the records.
pessimistic write is there when you set SNAPSHOT ON on the above two transactions but since second select statement of T1 can take snapshot of last committed data it will not wait for T2 to complete and update statement of T2 also takes the snapshot of last committed data and apply exclusive lock to update the records.
For optimistic write you can refer snapshot isolation level.
Upvotes: 0
Reputation: 1
Pessimistic locking prevents lost update or write skew. So, use pessimistic locking:
Optimistic locking doesn't prevent lost update or write skew. So, use optimistic locking:
In MySQL and PostgreSQL, you can use SELECT FOR UPDATE
as pessimistic locking.
You can check my answer of the lost update and write skew examples with optimistic locking(without SELECT FOR UPDATE
) and pessimistic locking(with SELECT FOR UPDATE)
in MySQL.
Upvotes: -2
Reputation: 562871
This is almost a duplicate of this past question: Optimistic vs. Pessimistic locking
Pessimistic locking is good for cases when you want to guarantee you lock multiple resources atomically. This helps to avoid deadlocks.
Optimistic locking relies on non-atomic locking, in the sense that you may need to lock multiple resources during your transaction, and if you acquire the locks optimistically, you do that one at a time, so there's a race condition with respect to other concurrent transactions.
Pessimistic locking has some risks too. You might lock some of the resources needlessly, if it turns out you didn't need to lock them after all. It could depend on the logic of your transaction, but you had to lock them as part of the atomic lock request just in case you did need them.
Upvotes: 3