0

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?

ffff
  • 2,853
  • 1
  • 25
  • 44
  • If you are worried that `SELECT` and `UPDATE` will operate on *different* data (because another user did something between `SELECT` and `UPDATE`) then the answer is - no, you shouldn't worry **IF** you are in a transaction. Why? Because transaction isolates the environment and you are working on the data *snapshot*. You don't have to lock anything, MySQL does this for you if you're in a transaction. You can check [available isolation levels for MySQL](https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html) at the manual page. – N.B. Aug 12 '16 at 08:07

1 Answers1

1

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.

jersoft
  • 478
  • 2
  • 9
  • 20