Description:
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 in the next section but essentially when the stored procedure is executed concurrently over two separate connections I get different values when I expect the same.
I assumed my understanding was incorrect however I then noticed that by removing an unrelated SELECT statement 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.
How to repeat:
Run in the script below to create a test database, two tables and a stored procedure:
-- 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 ;
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 ();
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.
Note that I am using READ_COMMITTED as my transaction isolation level.
I've tested this using MySQL server and MariaDB
Suggested fix:
Either:
A: My understanding of the expected transactional behaviour is incorrect and an explaination of this odd behaviour would be much appreciated
or
B: There is an issue whereby including a SELECT statement to an unrelated table causes the transactional locking to fail
Description: 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 in the next section but essentially when the stored procedure is executed concurrently over two separate connections I get different values when I expect the same. I assumed my understanding was incorrect however I then noticed that by removing an unrelated SELECT statement 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. How to repeat: Run in the script below to create a test database, two tables and a stored procedure: -- 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 ; 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 (); 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. Note that I am using READ_COMMITTED as my transaction isolation level. I've tested this using MySQL server and MariaDB Suggested fix: Either: A: My understanding of the expected transactional behaviour is incorrect and an explaination of this odd behaviour would be much appreciated or B: There is an issue whereby including a SELECT statement to an unrelated table causes the transactional locking to fail