Locking
Locking in database is a feature where we can request data set to be locked by the database for concurrent transactions.
The default lock type is - Reads allowed for concurrent transactions but write allowed to only one transaction at a time.
In the default locking mechanism, the writes are only done serially. There are no checks if the queued updates are making any changes that could be writing unwanted changes.
This default locking mechanism of a database is called Readers—writer lock. Here the read requests can happen in parallel but write happens sequentially.
Optimistic Locking
It's the locking mechanism that happens at application level. This means, the application must verify if something has changed and if something has changed, then the query must be updated and retried or handled based on the situation.
It's called so because this locking mechanism is very optimistic and assumes that the race condition would occur very rarely.
Version or Timestamp Column
This is implemented by using an extra version or timestamp column.
The application then uses this column to ensure the row being updated isn't already updated by another concurrent transaction.
Pessimistic Locking
It's the locking mechanism implemented by the database itself. But the application must control the database on how it must be applied. This is specified in the query it sends to the database and database then handles it.
SQL syntax
By using special SQL syntax by the application, specific datasets can be locked.
These aren't common syntax. Each SQL server has a different implementation of the pessimistic locking concept.