Comparison
Optimistic lockingvsPessimistic locking
Optimistic locking
you read the row with its version, write it back only if the version has not changed, and retry the whole thing if it has.
Detecting conflicts at write time by checking that the record has not changed since you read it, usually via a version or timestamp column. There is no lock held, so readers never block and there is no deadlock risk. It works well when conflicts are rare, and degrades badly on hot rows where retries pile up.
Full entry →Pessimistic locking
you take a lock on the row up front with SELECT FOR UPDATE, so everyone else waits until your transaction ends.
Preventing conflicts by acquiring an exclusive lock before reading data you intend to modify. It guarantees you will not have to redo the work, at the cost of blocking others and creating deadlock potential. The right choice on genuinely contended rows, on long operations you cannot cheaply retry, and anywhere the retry has side effects.
Full entry →