mysql - 乐观锁与悲观锁各自适用场景是什么？

Question

悲观锁貌似没法解决更新丢失的问题。见下面的例子，两个用户张三，李四，他们两人可以更新同一条数据库记录。假设记录为(sex,age) = (‘male’, 25)。在张三的查询和修改的时间间隔内，李四更新了记录，而张三对这种情况不知情，最后导致李四的更新丢失了。无论加不加悲观锁，都解决不了这种问题。我的问题是

1）对于这种并发写冲突，是不是只能用乐观锁（给表加一个版本号字段）来防止更新丢失？
2）那select ... for update这种悲观锁在什么场景下使用，悲观锁的使用应该是为了解决并发写冲突，但貌似它又不能解决更新丢失问题，感觉有点鸡肋啊，亦或是我理解有误.

有一篇相关文章，参见http://www.douban.com/note/204830640/

Answer 1

There is actually no lock in the examples of Zhang San and Li Si. This is a matter of business.
Li Si modified the record before Zhang San performed the modification, causing his modification to be overwritten by Zhang San, so the update was lost. The root cause of this problem is that the transaction is not isolated (you can search for the ACID of the transaction). Zhang San's modification operation and Li Si's modification operation are divided into two different transactions, and there should be isolation between transactions (I in ACID).
The method to achieve isolation is locking, which is divided into pessimistic locking and optimistic locking.
When Zhang San wants to modify the record, he first locks the record. In this way, Li Si cannot modify this data while Zhang San holds the lock. After Zhang San completes the modification, he releases the lock, and Li Si obtains this record again. lock, then modify the record and release the lock. Why is it called pessimistic locking? The main reason is that everyone who wants to modify the record "pessimistically" believes that others will modify the data concurrently, so they must lock it first.

Your problem is a transaction problem, not a lock problem.