0

I know pessimistic locking with lock a database record and release it when "transaction" ends which obtained the locks. But is this means pessimistic locking is within a physical transaction like

BEGIN TRANSACTION

//pessimistic locking

COMMIT TRANSACTION ?

For a web page, when user select a record to edit, when he press the edit button, I want pessimistic lock this record so others cannot change it, then in the edit button onpress() event, I start a physical transaction?

seem impossible as the edit process maybe very long...it hold the database transaction for the whole edit process (press edit button, edit in webpage, press save button) in pessimistic lock?

user1169587
  • 1,104
  • 2
  • 17
  • 34

2 Answers2

1

It's not very practical to build a locking system like that. Even if you were able to do this somehow, it would seriously limit the number of users that are allowed to access to the server.

You should use somekind of a versioning system if many users are allowed to edit a post that one user has submitted. Or, if you prefer a locking system, then make a system that keeps account who is editing and what and update this information in regular intervals from the client. Like once in a minute and if the server does not hear anything from the client in two minutes then release the lock.

Croco
  • 326
  • 1
  • 12
  • yes, so the locking mode like PESSIMISTIC_WRITE in JPA or LockMode.UPGRADE in hibernate is not usable in web application? To lock a record in web application when user select a record to edit, need implement pessimistic locking by self coding in web application? and any sample code reference for this kind of web application pessimistic locking? – user1169587 Jan 24 '19 at 15:44
  • I'm not an expert in this area. You probably should make a new post and give more details about the problem, like what kind of technologies you want to use. – Croco Jan 24 '19 at 16:31
1

Actually For Such situations Optimistic locking should be used . It can be used below way :

  1. User requests from UI to get record to edit .
  2. Server selects the record and pass them to UI . Suppose at this time record to be edited was having version 1 . Now database conncetion is released . No Database resources are held as we don't know if user will issue update request or not .
  3. Now user edits data on UI and requests for its update to server with the original version number that was sent in step 2.
  4. Now a new database conncetion is taken from db connection pool and record is updated in db if original record present in db has the same version as it was sent to UI in step 2. It version of the record present in step 4 has been changed before used sends the edit request you should ask user to edit again with fresh data .

In the above scenario no db resources are held upto the duration of whole conversation of user. Generally speaking in such scenarios optimistic locking is good .

But If it is very critical to hold the resources with write locks until the transaction commits then Pessimitic should be used

For example suppose a simple movie ticket booking system which doesn't hold seets for user who has not done payment yet . Means after payment they may or may not get the tickets . When after payment they reuqest booking engine to book the ticket .

  1. Suppose there is table in db which holds number of seets left in a show .
  2. Tx1 takes a pessimitic write lock of this table .
  3. Until the transaction TX1 to book a movie commits not any other transaction can take lock of this table.
  4. When Tx1 completes it decreases the seat count by number of seets booked in Tx1
  5. Now when Tx2 gets the write lock and if number of seets left are compatible it proceeds if not it present user with a message that seats got booked by other user and payment is refunded .

In this situation holding Pessimitic lock until the booking transaction completes make sense . Even in this situation no locks and transactions are held in user's complete convesation .

Pessimistic read or Pessimitic Write locks are held until a transaction is committed or rolled back . Only Pessimitic read locks can be shared all other combinations block each other until released .

Vaneet Kataria
  • 575
  • 5
  • 14