Concurrent MYSQL procedure calls returning different results with an unrelated SELECT statement

Question

I'm experiencing some very strange transactional behaviour in my MYSQL application.

I've managed to reduce the problem down to a small isolated test case, the code for which I’ve included below:

-- Setup a new environment
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
DROP DATABASE IF EXISTS `testDB`;
CREATE DATABASE `testDB`;
USE `testDB`;

-- Create a table I want two procedure calls to interact with
CREATE TABLE `tbl_test` (
    `id` INT(10) UNSIGNED NOT NULL
    , PRIMARY KEY (`id`)
);

-- A second table purely to demonstrate the issue
CREATE TABLE `tbl_test2` (
    `id` INT(10) UNSIGNED NOT NULL
);


DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_test` $$
CREATE PROCEDURE `sp_test` ()
BEGIN

    START TRANSACTION;

        -- CRAZY LINE
        SELECT * FROM `tbl_test2`;

        -- Insert ignore so both calls don’t try to insert the same row
        INSERT IGNORE INTO `tbl_test` (`id`) VALUES (1);

        -- Sleep added to make it possible to run concurrently manually
        SELECT SLEEP(1) INTO @rubbish; 

        -- The result I am interested in
        SELECT COUNT(*) FROM `tbl_test`;

    COMMIT;

END $$

DELIMITER ;

Steps to Reproduce:

Run in the above script to create a test database, two tables and a stored procedure.
In two separate connections, as near to simultaneously as possible, run the stored procedures (you can increase the SLEEP time if you need longer):
```
USE `testDB`;
CALL sp_test ();
```

The Problem

When executed concurrently over two separate connections the SELECT COUNT(*) FROM `tbl_test`; statement returns different values for the two calls.

When I follow the steps above, I get back 1 from the first of the two procedure calls and 0 from the second.

My understanding of transactional behaviour and table locking is that when the first call reaches the INSERT statement it will create a lock. The second procedure call will reach the same line but must then wait until the transaction from the first call has been committed. Increasing the sleep time reinforces this idea as the second call will take twice as long to complete. If this is the case however, then the second procedure call should pick up the insert from the first call and both results should be equal to 1.

TL;DR I'm expecting both to equal 1

Note that I am using READ_COMMITTED as my transaction isolation level.

I've tested this using MYSQL server and MariaDB

Further Weirdness

So at this point I assumed my understanding was incorrect. However, I then noticed that by removing the line SELECT * FROM `tbl_test2`; the results suddenly produced the expected values!

I've been experimenting with the script but essentially, including a SELECT statement to any table within the database before the INSERT line causes unanticipated results. I have absolutely no idea why this is the case.

Questions

Is my understanding of the expected transactional behaviour correct?
Why on earth does the SELECT statement to an unrelated table cause the transactional locking to fail?

If anyone can shed some light on this I would be very grateful!

Concurrent MYSQL procedure calls returning different results with an unrelated SELECT statement

0 Answers0