Laravel 5.2 with Concurrency Update MySQL InnoDB problem and solutions

Table of Content

Lets take a look at very basic example :

  • Website have users which have currencies related with.
  • User can purchase a product using their currencies many times by submit a form at our website.

Its easy to create a basic website like that with Laravel 5.2 and MySQL : we check for current user’s currencies, if the amount is enough, decreasing this and add product for user.

But when the website traffic is too big, and user can login to our systems from multi devices and submit the purchasing form multi times at same time, problem will come.

We need the very safe way to handle those problems.

In this tutorials, i will introduce you how we can handle those problem, using Redis locking-flag and Laravel Database built-in functions to handle the famous thing in developer wolrd, which usually call “Concurrency Update”.

Basic code.

Lets take a look at code before we going to improve this :

First we create a table user_currencies with migration :

Our products table :

Our orders table :

Our Form Submit Process function :

It is very simple and clear function about processing purcharsing form.

But this will be very dangerous for our system in case :

We need the complete solution to solve those problems.

First lets take a look at our MySQL secret about Concurrency Update Handle.

MySQL Secret.

We need to choose MySQL Engine InnoDB instead of MYISAM.

We need to care about MYSQL Isolation Level.

The four isolation levels

READ UNCOMMITTED

With the READ-UNCOMMITTED isolation level there isn’t much isolation present between the transactions at all. The transactions can see changes to data made by other transactions that are not committed yet. That means transactions could be reading data that may not even exist eventually because the other transaction that was updating the data rolled-back the changes and didn’t commit. This is known as dirty read. An application rarely needs to rely on dirty needs, in fact this really can’t be called an isolation level. Simply put, there isn’t really any isolation at all and hence such a system can’t really be called a transactional system.

READ COMMITTED

With the READ-COMMITTED isolation level, the phenomenon of dirty read is avoided, because any uncommitted changes is not visible to any other transaction, until the change is committed. Within this isolation level each SELECT uses its own snapshot of the committed data that was committed before the execution of the SELECT. Now because each SELECT has its own snapshot, so the same SELECT when run multiple times during the same transaction could return different result sets. This phenomenon is called non-repeatable read.

REPEATABLE READ

With the REPEATABLE-READ isolation level, the phenomenon of non-repeatable read is avoided. This isolation level returns the same result set through out the transaction execution for the same SELECT run any number of times. A snapshot of the SELECT is taken the first time that SELECT is run during the transaction and the same snapshot is used through out the transaction when the same SELECT is executed. A transaction running in this isolation level does not take into account any changes to data made by other transactions, regardless of whether the changes have been committed or not. This ensures that reads are always consistent(repeatable). This isolation level is the default for InnoDB. Although this isolation level solves the problem of non-repeatable read, but there is another possible problem phantom reads.

SERIALIZABLE

With the SERIALIZABLE isolation level, the phenomenon of phantom reads is avoided. Transactions when run in this isolation level place locks on all records that are accessed, as well as locks the resource so that records cannot be appended to the table being operated on by the transaction. Transactions when run in this fashion, run in a serialized manner. This isolation level is the strongest possible isolation level.

You can read more about those things at this very useful article http://www.ovaistariq.net/597/understanding-innodb-transaction-isolation-levels/#.V2NrhO0vDCJ

Those explainsion are very easy to understand. But wait, mean that we must to set MySQL to SERIALIZABLE everytime we want to avoid “Concurrent Update” (phantom reads) ?

Luckily, no. Please read carefully below :

As the text above describes, phantom reads is the only problem that SERIALIZABLE is helping you avoid, otherwise SERIALIZABLE is pretty much same as REPEATABLE-READ. InnoDB has a special locking feature called gap-locking which helps you avoid the phantom reads problem. In the case of gap locking, a lock is set on the gap between index records, or on the gap before the first or after the last index record. Now all you have to do avoid phantom reads is to convert such reads to locking reads using SELECT with FOR UPDATE or LOCK IN SHARE MODE.

So all we need to do is leave the MySQL Isolation LEVEL same as default and “using SELECT with FOR UPDATE or LOCK IN SHARE MODE”.

How we can do that with Laravel?

Laravel 5.2 fully support that with both DB and Eloquent using https://laravel.com/docs/5.2/queries#pessimistic-locking.

So we change our processForm function above to :

The function look perfect with InnoDB Transaction and Pessimistic Locking. But the story is not end.

When we testing with one user and login to multi session and submit form, we got the negative point like -200.

What the problem here? We will take a look at this in next post. Currently now we can easy solve this problem with change the field point to UNSIGNED INTEGER mean that it can not be negative.

And because we using try catch block with InnoDB transaction so the error Exception by SQL when code try to update point to negative value are handle perfectly.

(To be continue…)

Leave a Reply

Your email address will not be published. Required fields are marked *