How can pessimistic locking prevent a lost update?-CodePudding

I was reading Ask TOM and he defines lost update as

A lost update happens when:

session 1: read out Tom's Employee record

session 2: read out Tom's Employee record

session 1: update Tom's employee record

session 2: update Tom's employee record

Session 2 will OVER WRITE session 1's changes without ever seeing them -- resulting in a lost update.

Consider the web application that allows people to update their addresses and their phone numbers. the update statement used is always:

update emp set address = :a, phone = :b where empno = :x;

In the optimistic concurency scenario you can use @version and avoid it. However in a pessimistic locking scenarion like using SELECT FOR UPDATE or in another DBMS with pessimistic locking how can this particular example be prevented ?

If session 1 does

update emp set address = 'London', phone = 123 where empno = 10;

Session 2 blocks while doing

update emp set address = 'Brighton', phone = 456 where empno = 10;

Session 1 updates,commits. Session 2 gets the fresh data but so what? It overwrites it anyway!

is that a lost update or not , and if yes how can it be revented under a pessimistic locking scenario?

CodePudding user response：

If you're doing pessimistic locking, Session 2 would block doing the initial SELECT since both sessions were doing a SELECT ... FOR UPDATE. Session 2 wouldn't be able to read the row until Session 1 committed its change. At that point, the data that Session 2 would read would have Session 1's changes so there would be no lost update.

In real-world systems, it is exceedingly rare that you would want to have this sort of concurrency issues. Or that, architecturally, you'd be able to. Locking a row while a user edits it is pretty easy in a client-server application where each user gets their own dedicated connection to the database. It is very difficult to do that in a web-based world where the end user communicates with a middle tier using a stateless protocol like HTTP and the middle tier maintains a pool of database connections that get used by a number of different users.

It appears that you may misunderstand what "lost update" means. In this context, the fact that session 2 overwrites a change that session 1 made does not mean that there was a lost update. A lost update would mean that session 2 overwrote the change session 1 made without ever seeing the change that session 1 made. If you're doing a pessimistic lock, session 2 reads the change that session 1 made so session 1's change isn't a "lost update".

CodePudding user response：

Thank you,the comment made it clear