How to solve Concurrency issue?

Question

All,

I am using MySql 5.7 in my application. I trying to make my save function Concurrency Safe. I will explain with an example.

Example : I have two admin users Admin 1 and Admin 2. We have a product table and we have a product table entry with product code "P1". Suppose Admin 1 and Admin 2 are logged into the system and try to update product entry with code "P1" at the same time.

I need to inform one of the users that the record(s) you are trying modify is updating by another user and try again after some time.

I am using transaction and didn't change MySql's default transaction level(repeatable read). I am trying to solve it by using "SELECT FOR UPDATE"(included a where condition to check with modified time). This "where" condition will solve concurrency issue to those transactions which are already committed. But if two transaction starts at the same time and the first transaction gets committed before lock timeout, then when the second transaction executes, it overwrites the first one.

Kindly share your ideas

Thanks in advance

You could just do a `LOCK TABLES product WRITE` before the critical section comes up. Don't forget to unlock afterwards. — apokryfos, Feb 10 '17 at 13:04
@apokryfos locking an entire table to a finish an edit of a single record is not really an efficient solution — Shadow, Feb 10 '17 at 13:10
I guess it can be one of the solution: use InnoDB engine and `select ... for update` — Wizard, Feb 10 '17 at 13:11
There is a old technique which is to create a version field in the table. Every time one gets a registry to update it will have the version, so admin 1 will have version one and admin 2 will have version 1 also when one of then updates the registry, through a trigger you increase the version (checking it if it is same version) when admin 2 try to update the trigger will check that the version is outdated and throw an exception. — Jorge Campos, Feb 10 '17 at 13:11
A very similar question and answer: http://stackoverflow.com/questions/41395533/mysql-logic-optimization — Shadow, Feb 10 '17 at 13:15

RiggsFolly · Answer 1 · 2017-02-10T13:18:28.770

Well there are actually 2 issues here.

First, one of the admins will get a lock on the row before the other, so assuming admin1 gets the lock first, admin2 will queue until admin1's transaction completes, then admin2's transaction will take place.

So that is all looked after for you by the DBMS.

But the second issue is of course if both admin1 and admin2 are attempting to update the same column(s). In this case admin1's update will be overwritten by admin2's update. The only way to stop this happening if that is what you want to stop is to make the UPDATE very specific about what it is updating. In other words the UPDATE must be something like this

UPDATE table SET col1 = 'NewValue' 
WHERE usual criteria
  AND col1 = 'Its Original Value'

So this means that when you present the original data from this row to the user in a form, you must somehow remember what its original state was as well as capture its new state that the admin changed it to.

Of course the PHP code will also have to be written to capture the fact the UPDATE did not take place and return something to whichever admin's update has now failed. Showing the new value in the column in question and giving them a notice that the update failed because someone else already changed that field, and letting them either forget there change, or apply their change over the top of the other admins update.

score 0 · Answer 2 · edited May 23 '17 at 12:01

There is this technique you can use to control it without locking the table or controlling the update you should do.

Create a field on your table that will be a version for that registry:

alter table someTable add column version not null integer default 0;

There will be no need to change any insert code with this.

Every time a user fetches a registry to update you make sure that it will have the version also in the object (or form,m or whatever way you handle your entity in the system).

Then you will need to Create a before update trigger for your table that will check if the version of the current registry is still the same is so you update if not you raise an error. Something like:

delimiter $$
create trigger trg_check_version BEFORE UPDATE
         ON yourTable 
         for each row
    begin
        if NEW.version != OLD.version then
            signal sqlstate '45000' set message_text = 'Field outdated';
        else
           NEW.version = NEW.version + 1; 
        end if;
    end$$
delimiter;

Then you handle the error in your php code. This signal command will only work on MySql 5.5 or later. Check out this thread

How to solve Concurrency issue?

2 Answers2