Concurrency Handling using PHP-MySQL

In this blog we will take an online shopping as an example to analyse the concurrency issues and its possible solutions.  You guys can refer the following database tables for the given examples:

Database Design:

Table 1: product

`product` (`productId` int(11) NOT NULL AUTO_INCREMENT,`productName` varchar(50) NOT NULL,`productDescription` text NOT NULL,`productSalePrice` double NOT NULL,`productCategoryId` int(11) NOT NULL,`productPicture` text NOT NULL,`productDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,`productQuantity` int(11) NOT NULL,PRIMARY KEY (`productId`)) ENGINE=InnoDB

Table 2: productaategories

`productcategories` (`categoryId` int(11) NOT NULL AUTO_INCREMENT,`categoryName` varchar(50) NOT NULL,`categoryDescription` text NOT NULL,PRIMARY KEY (`categoryId`)) ENGINE=InnoDB

Table 3: customer

`customer` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(50) NOT NULL,`password` text NOT NULL,`phoneNumber` varchar(20) NOT NULL,`email` varchar(50) NOT NULL,`shippingAddress` text NOT NULL,`shippingPostcode` varchar(20) NOT NULL,`billingAddress` text NOT NULL,`billingPostcode` varchar(20) NOT NULL,`isLogged` enum(‘y’,’n’) NOT NULL DEFAULT ‘n’,PRIMARY KEY (`id`)) ENGINE=InnoDB

Table 4: orderdetails

`orderdetails` (`orderId` int(11) NOT NULL AUTO_INCREMENT,`productId` int(11) NOT NULL,`productName` varchar(20) NOT NULL,`productPrice` float NOT NULL,`productQuantity` int(11) NOT NULL,`customerName` varchar(25) NOT NULL,PRIMARY KEY (`orderId`)) ENGINE=InnoDB

Let’s go through various examples and its possible solutions:

Example 1:

Consider that customer has added the product to his/her shopping cart and has clicked the ‘Buy’ button and at the same time owner changes the product details like ID, name, price etc.  from the database.

Solutions:

To handle this problem there can be three possible solutions:

  1. When a user is logged-in, the shopping cart should get stored in the database in shopping cart table in order to link shopping basket products with the database product details table. Using referential integrity, whenever any changes are done in actual product details table, it should automatically change the product details in the shopping cart table and should prompt the customer that “product details are changed do you want to continue” using AJAX. But this process can irritate customers.
  1. Another way of doing it is that the shopping cart should not refer the actual product table. Once the products are added to the shopping basket, the shopping cart table can be used as the final table to purchase the products even if the product details get changed in actual product table.
  1. Another neat and clean way of doing it is that whenever owner edits any product details, instead of changing the same product id he/she should add the new product id of the changed product so that old product details will remain same and changes will be considered as new product itself. This way, it will not affect the customers purchase and they will be able to buy the same product which was added to the shopping basket.

One of the problems of storing the shopping cart in the database is related to the real time data required for multiple customers logged in at the same time. If a customer adds a product to the shopping cart but doesn’t buy the product, then the product is still stored in the basket and it may happen that due to limited quantity available, other users have to wait for the same product to buy. Session variable automatically get expired as soon as customers logged out or browser is closed. To handle this, similar method can be used to clear the shopping basket table in the database. A database trigger can be written which will check that at what time the product was added and if the time elapsed is more than a pre-defined time, it should automatically expire the customer ‘s shopping basket. At the same time, it should inform the user that time has elapsed due to which the basket is expired. Trigger should run automatically after few intervals.

Example 2:

Consider example when two owners are trying to change the details of the products at the same time.

Solutions:

It can be implemented by locking the table or row of the table. Myisam table supports only table level locking while InnoDb supports row level locking. I personally prefer using InnoDB over Myisam as it has better features related to ACID properties, that we can discuss in some other post.

In this case optimistic lock can be used when first owner makes and commit the changes and when second owner tries to commit his/her changes, he/she will get the conflicts and will come to know that somebody else has made the changes in that version and second owner can update his changes using updated version.

Another way of handling the above case is to add two fields to the product table:

LAST_LEASE_TIME: Time of the last lease

LAST_LEASE_USER: User that leased the record for the last time.

When a user tries to edit the record, application will first check that the record is not leased, or that the lease has expired (i.e. the lease is not older that the specified lease time) or the owner is the one that has granted the lease.

Web application will periodically renew the lease, for example with an AJAX call. When the user ends editing the record, it explicitly expire the lease. This way it will not allow another owner to edit the same product. This is similar to pessimistic locking.

Example 3:

If only one quantity is left of a particular product and at the same time multiple users are trying to buy the same product, there is a probability that the time difference may be less than even few milliseconds for two different users.

Improvements:

This can be improved by using the pessimistic lock in the above scenario. In this case first customer will lock the product so that nobody else will be able to access it and after buying the product the lock will get removed. The reason of using the pessimistic lock instead of optimistic lock is that the chance of conflict between concurrent sessions is high where one of the customers will get dissatisfied after going through the long process of adding the product to the shopping cart. To avoid this, pessimistic lock is better option in order to avoid the conflicts. The disadvantage of this may be in cases where a customer has locked it and is not buying the product. In this case after few minutes locks should get released. Still, always using the pessimistic lock is not the good option as it is a long transaction and long transactions are never a good option but in this case it can be considered by checking the availability of product quantity. If product quantity left is one then pessimistic lock could be used and if product quantity is greater than 1 then no locking is required.

Example 4:

Suppose a product is left having one quantity only and this product is added by multiple users to their basket, but till now nobody has bought the product. Now one of the customers buys it and when other customers try to buy it, they will not be able to buy it. This will irritate them as they have gone through the long process of buying it. It is the next stage of example 3.

Solutions:

If the above situation occurs, the website should simply displays the error message but it can be improved by removing the item from ‘Available Stock’ of product table and put it in a ‘Pending Sale’ state along with a timestamp and session ID. Implement the session state so there is a timeout. Periodically check ‘Pending Sales” for items that have significantly exceeded the timeout and return them to ‘Available Stock’. When completing a transaction, application can check ‘Pending Sales’ for an item with a matching product and session ID. This should find the product. If for some reason it cannot be found, application will need to report it internally to a ‘Support’ person for resolution. To make it more user friendly it should be informed to the customer that “Few minutes left to buy this product” using AJAX.

Example 5:

Consider if monthly report has to be prepared by owners of the company. To do that Mr. ABC tries to retrieve the number of products that are present under ‘Samsung’ (contains 4 products) and ‘iPhone’ (contains 6 products) categories. First he retrieved the information from ‘Samsung’ category and got 4 products. In the mean time he went busy in doing other stuffs and at the same time Mr XYZ added the new products in both the categories. Due to the changes made by MR. XYZ, now ‘Samsung’ category has 6 products and ‘iPhone’ category has 8 products. Mr. ABC came back and retrieved the number of products from iPhone category and got 8 products so now according to Mr. ABC total count of both the categories is 4+8 = 12 products which is actually not correct. Either he should get 10 products or 14 products, but 12 products count is not correct.

Solutions:

This scenario can be handled by using the optimistic lock. In this case, both the users will work on different versions so that the proper information and database integrity is maintained.

 

Through this blog we have tried to show my own version of understanding the concurrency issues and its solutions using above examples that how differently we can manage it either by table/row level locking or by session ids or by different time stamps. There could or there are different other solutions are available using which system can be improved. Concurrency is one of the most hot topic of web based applications which always comes with its different concerns and solutions.

Concurrency Handling using PHP-MySQL

5 thoughts on “Concurrency Handling using PHP-MySQL

Leave a Reply

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