Database : Optimistic and Pessimistic Locks

Recently, I had a conversation with a senior software engineer about this topic, and I found it very fruitful, so I organized the topic we discussed a bit as a record.

Optimistic and Pessimistic Locks

In a system with spike in traffic, these two locks are dealing with different issues of how to ensure data accuracy and how to allow the DB to cope with the high traffic.

Pessimistic lock

Pessimistic locks are used to ensure that data will be modified at a high probability, and the focus is to ensure that the data in that row will not be modified by others at the same time. Therefore, the resources will be locked so that the operation in that row will be completed before allowing the next row to be executed.

The difference with the Transactional concept is that Transactional is to ensure that an operation either succeeds or fails at the same time rollback, while pessimistic locking focuses on "row-level locking".

The data accuracy is there, but this will result in a long waiting time for the user when there is a large amount of instantaneous traffic, e.g., if the first user makes a change, the second user will be 0.001 seconds slower, and the nth user will be n(0.001) seconds slower, and the waiting time will add up as the number of users increases.

Optimistic lock

Optimistic lock thinks that the data will not be modified in high probability, so it won't lock the resources and allow more commands to operate the table, but this will cause the data to be incorrect, so one of the solutions is to add the version, for example, version 0 and version 1, and compare the operation of these two versions to see if it is the same, for example.

1. read record + version
2. when the transaction is to be committed, it will make sure the version number is the same as when it was read, then record + version number will be committed. 3. if the version number is different, it means that the record + version number is the same as when it was read.
3. if the version number is different, it means there is a conflict.

最近剛好跟一位軟體工程師前輩聊到的主題覺得滿有收穫的，就稍微整理然後把它紀錄下來😄

Database 中的”樂觀鎖” 與 “悲觀鎖”

一個瞬時流量很大的系統，怎樣確保資料的正確性和可以讓 DB 能夠負擔高流量，這兩把鎖就是在處理不同的議題。

悲觀鎖

悲觀認為資料會高機率被修改，重點在保證該行資料不會同時被其他人修改，所以會鎖住資源，讓該行的操作完成後才會讓下一行接續執行。

跟 Transactional 概念不同在於，Transactional 是確保一個操作不是同時成功就是同時失敗 rollback，悲觀鎖的重點放在 “行級別的鎖定” (row-level locking)。

資料正確性有了，但這樣會造成瞬間流量大時使用者等待時間很長，例如 : 第一個使用者修改，第二個使用者會慢 0.001 秒，第 n 個使用者會慢 n(0.001) 秒，等待時間隨著使用者的數量累加。

樂觀鎖

樂觀認為資料不會高機率被修改，所以不會鎖住資源，允許多的 command 來操作 table，但這樣會造成資料不正確，所以解法之一是加上 version，例如 version 0 和 version 1，對比這兩個 version 的操作是否相同，舉例 :

1. 讀出 record + version
2. 當 transaction 要被 commit 時，會確認 version number 與讀時相同，record + version number 才會被 commit
3. 如果 version number 不同，代表衝突，那這時候就要再另外處理

大概是這樣瞜。