Handling Concurrency Data Insertion Operations in MySQL

Problem Statement

How to fetch the latest inserted data from a database in MySQL?

This article discusses how to create database and tables in MySQL. More emphasis is placed on how to get the details of the latest inserted ID and also the usage of appropriate functions in the case of concurrent insertions by multiple users.

For this article, I am using the SQLyog client tool for MySQL. Start the tool to work on the article. You can download a trial version of the tool from the following URL:

http://www.webyog.com/en/downloads.php

Create a new database so that we should not be disturbing the existing ones as below:



Figure 1: Create Database

Name the database "Employee".



Figure 2: Database Name

Now we need to create a table and insert test data into it.



Figure 3: Create Table

Name the table "EmployeeDetails". The best practices in Database design says that the table name should not be abbreviated and try to provide the full name. Also create columns for the table. In our example, column ID is of type integer and it is the primary key and so not null. I have set the Auto Increment property of the column so that the user does not provide the value for this column. The database engine provides the value. In most of the projects, we set the primary key as an auto incrementing column since we do not expect the client to enter the value for the primary key. If we are not using auto increment for the column then we might end up writing logic for auto incrementing the value every time a new value is inserted. Anyways the discussion of Auto Increment is out of scope for our article.



Figure 4: Creating columns

Now save the table by clicking on the "Save" button on the bottom of the window.



Figure 5: Save, Reset buttons

Click "NO" and this saves the table. Now our next task is to enter data into the table. Let us do that as below:

Right-click on the table "EmployeeDetails" and select "Open Table in New Tab".

Figure 7: Open table in new tab

A new tab will be shown in the query window as below:

Figure 8: Query window to show the table data

Now enter the data:

Figure 9: Data entry

Now we have created a database and a new table with data. Let us try to get the details of the table by a query. For that we need to open a new query window. Let us do that.



Figure 10: new Query Editor Location

Click on the button to create "New Query window". Now I am writing a simple select statement for the table as below:

Figure 11: Simple Select query

A couple of things to consider while querying in the client tools:

• Make sure your query window is pointing to the correct database.
• Make sure your query has the right table in usage.
• If you are using transactions, make sure that you execute roll back or commit. Otherwise kill the transactions by querying the transactions in the database and using the appropriate command to kill the transaction.
• If your tool supports auto complete feature, use it at the maximum as it helps you in writing your queries faster.

A simple query to get all the records in the table EmployeeDetails:

SELECT * FROM EmployeeDetails

Figure 12: Query output

Now let us fetch the data for the latest ID from the table.

The simplest query to get the details of the latest ID is as below:



Figure 13: Query and Output for latest ID data

In real time, we may need to do more than just inserting the details. For example, there is a requirement that once you insert the details in the EmployeeDetails table you need to get the new ID for the inserted data so that we use that ID for updating some other table or insert new data in another table.

There is a function available in MySQL which gets you the last inserted ID from the table. LAST_INSERT_ID() gets you the most recent inserted identity column value in the current session. This is something like SCOPE_IDENTITY in SQL Server.

SELECT * FROM EmployeeDetails WHERE ID = LAST_INSERT_ID()

Output:



Figure 14: Output

Let us test this in concurrent mode. For this we need to create a stored procedure that inserts a row in the table EmployeeDetails.

Create a new stored procedure:

Figure 15: Create new stored procedure

Name the procedure "InsertEmployeeDetails".

Figure 16: Naming the stored procedure

Click on the "Create" button. Now define the stored procedure:

DELIMITER $$
CREATE PROCEDURE InsertEmployeeDetails (
IN empname VARCHAR(10),
IN address VARCHAR(10),
IN qualification VARCHAR(10)
)
BEGIN
INSERT INTO EmployeeDetails(EmpName,Address,Qualification)
VALUES (empname, address, qualification);
SELECT MAX(ID) FROM EmployeeDetails;
END$$
DELIMITER ;

Most developers use the MAX function to get the maximum value of the identity column to use for subsequent insertions or deletions. So, let us check how it fares in case of concurrent insertions.

For example, there are 3 users who are trying to insert data into the table using the stored procedure and the stored procedure after inserting the values into the table then returns an ID that is inserted. The application then reads the ID value and uses the ID for inserting the data into some other table.

Before doing this I am going to clean up the table by deleting all the rows.

Delete from EmployeeDetails

Figure 17: Screen for no data

Now we need to reset the identity. There is no command to reset the identity in MySQL like we have in Microsoft SQL Server. But we can the alter table to reset the identity column on where to start again.

ALTER TABLE EmployeeDetails AUTO_INCREMENT = 1

Now it resets the identity column to start again from value 1.

Now try to simulate concurrent users inserting data using the application in the same table by executing multiple insert stored procedures. The client tool displays results in multiple tabs for each query.
/* User 1 */
CALL InsertEmployeeDetails('Salma','Philly','BS');

/* User 2 */
CALL InsertEmployeeDetails('Penelope','Philly','BS');

/* User 3 */
CALL InsertEmployeeDetails('Kate','Philly','BS');



Figure 18: Executing multiple insert stored procedures

Now click the button to execute all the queries by clicking the "Execute All Queries" button on the top tool bar.

Here are the results:



Figure 19: First tab output

Figure 20: Second tab output

Figure 21: Third tab output

There is a possibility that the user 1 may get any of the values above based on the time difference on when the stored procedures are executed. If user 1 gets the value as 1 then it is correct and any other 2 values makes the data corrupted. Similarly in case of user 2.

Note: In Microsoft SQL Server many developers use the system function "@@identity" which gets the inserted ID value. But this also retrieves the most recently inserted identity value in the table, not in the user session. This leads to the same problem that we have discussed above. So, in Microsoft SQL we use SCOPE_IDENTITY which always returns back the inserted identity value in the current session. This helps in maintaining data integrity one of the main goals of Database Management Systems.

Another way to test this is as below:

Open two instances of the SQLyog tool and open query windows for the database "Employee" and run the insert statements as below:

Each instance represents a new connection established by the application for each user.

Figure 22: Two windows with Insert statements

Now execute queries in each window.



Figure 23: Two windows output after executing the insert statement

Now check the Max(ID) from each window:



Figure 24: MAX(ID) value

Both windows have the value 2 which is wrong for one of the users. Now we will check how LAST_INSERT_ID() works in this situation.



Figure 25: LAST_INSERT_ID() value

Our goal was to get the correct inserted value for each user in the concurrent insertions and if we observe in the preceding figure we find that it has given the correct ID for each insertion although the insertions took place at the same time and we queried the inserted ID after a longtime. So, the ID will be stored for each connection instance thus providing the correct value.

Note: The same example can be tested for Microsoft SQL Server also where we can identify the difference between usages of MAX(ID), @@identity and SCOPE_IDENTITY. If you test you will find that SCOPE_IDENTITY is the best solution in the concurrency mode.

I have never worked with PHP and it seems there is a function mysql_insert_id() that executes and gets the latest ID. If you want to experiment with it, then do so at your own risk.

Hope you liked this article. Happy coding!!!