Duplicate question from:
Copy/Pasting answer from the above link:
Optimistic Locking is a strategy where you read a record, take note of a version number and check that the version hasn't changed before you write the record back. When you write the record back you filter the update on the version to make sure it's atomic. (i.e. hasn't been updated between when you check the version and write the record to the disk) and update the version in one hit.
If the record is dirty (i.e. different version to yours) you abort the transaction and the user can re-start it.
This strategy is most applicable to high-volume systems and three-tier architectures where you do not necessarily maintain a connection to the database for your session. In this situation the client cannot actually maintain database locks as the connections are taken from a pool and you may not be using the same connection from one access to the next.
Pessimistic Locking is when you lock the record for your exclusive use until you have finished with it. It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks. To use pessimistic locking you need either a direct connection to the database (as would typically be the case in a two tier client server application) or an externally available transaction ID that can be used independently of the connection.
In the latter case you open the transaction with the TxID and then reconnect using that ID. The DBMS maintains the locks and allows you to pick the session back up through the TxID. This is how distributed transactions using two-phase commit protocols (such as XA or COM+ Transactions) work.
Edit (Adding more info to address the performance question):
Performance wise it depends on your environment. Take in the following factors to decide:
you're going to find optimistic will be better due to concurrency in most situations. Depending on the RDBMS and environment this might be less or more performant however. Typically with Optimistic locking you will find that the value needs to be row versioned somewhere.
With MS SQL Server for example, it gets moved to TempDB and something between 12-14 bytes are appended at the end of the column. Turning on optimistic locking with an isolation level such as Snapshot Isolation can cause fragmentation and your fill factor will need to be adjusted as the rows now have additional data at the end which could cause a page near full to cause a page split, which will lower your performance. If your TempDB is under optimized then this will not be as fast.
So I guess a checklist is:
- -Do you have sufficient IO/resources to handle the form of row versioning? If not, you are adding overhead. If so, then if you are
reading the data often while you are often locking it for writes, you
will notice a good improvement on concurrency across reads and writes
(although writes will still block writes, reads will no longer block
writes and vice versa)
- -Is your code susceptible to deadlocks or do you experience locking? If you are not experiencing long locks or a lot of deadlocks, then
the additional overhead of Optimistic locking wouldn't make things
faster, of course, in most cases we're talking milliseconds here.
- -If your DB is big (or on very limited hardware) and your data pages are near full, depending on the RDBMS, you could cause major page
splits and data fragmentation so make sure to consider reindexing
after turning it on.
Those are my thoughts on the matter, open to hearing more from the community.