CODEWITHSUNDEEP
mysqlconcurrencyoptimistic-concurrencypessimistic-locking
ffff
ffff

Reputation: 3070

Pessimistic vs optimistic concurrency control implementation

I've understood how both these concurrency controls work in plain English. However I was more interested how pessimistic control must be done in code. Here is what I feel, let's assume two users are trying to update a wiki document

Pessimistic control

Here, we are told to make use of transactions.

BEGIN
     SELECT DOC FROM WIKI WHERE DOC_ID = 1;
     /* business logic */
     UPDATE WIKI SET DOC = INPUT WHERE DOC_ID = 1;
END

However this is still prone to overwriting the previous updates. I feel there has to be a second check within the transaction to see if any writes have happened after the select statement, if yes rollback or else commit. Am I correct?

Upvotes: 0

Views: 797

Answers (1)

jersoft
jersoft

Reputation: 478

you have two choice of Concurrency (pessimistic):

Table level Locking and Row Level Locking:

But bear in mind that locking might cause DEADLOCK if you have multiple users. There's a lot of things you need to considered when using concurrency. Good luck!

UPDATE:

Since locking may cause deadlocks and other concurrency problem, you can implement a checked-in and checked-out features where in when a user check out a certain records, it will prevent the other user to check out the same records.

Upvotes: 1

Related Questions