Updated on 2025-09-18 GMT+08:00

Concurrent Write Examples

This section uses table test as an example to describe how to perform concurrent INSERT and DELETE in the same table, concurrent INSERT in the same table, concurrent UPDATE in the same table, and concurrent import and queries.

1
CREATE TABLE test(id int, name char(50), address varchar(255));

Concurrent INSERT and DELETE in the Same Table

Transaction T1:

1
2
3
START TRANSACTION;
INSERT INTO test VALUES(1,'test1','test123');
COMMIT;

Transaction T2:

1
2
3
START TRANSACTION;
DELETE test WHERE NAME='test1';
COMMIT;

Scenario 1:

T1 is started but not committed. At the same time, T2 is started. After the INSERT of T1 is complete, the DELETE of T2 is performed. In this case, DELETE 0 is displayed, because T1 is not committed and T2 cannot see the data inserted by T1.

Scenario 2:

  • READ COMMITTED level

    T1 is started but not committed. At the same time, T2 is started. After the INSERT of T1 is complete, T1 is committed and the DELETE of T2 is performed. In this case, DELETE 1 is displayed, because T2 can see the data inserted by T1.

  • REPEATABLE READ level

    T1 is started but not committed. At the same time, T2 is started. After the INSERT of T1 is complete, T1 is committed and the DELETE of T2 is performed. In this case, DELETE 0 is displayed, because the data obtained in queries is consistent in a transaction.

Concurrent INSERT in the Same table

Transaction T1:

1
2
3
START TRANSACTION;
INSERT INTO test VALUES(2,'test2','test123');
COMMIT;

Transaction T2:

1
2
3
START TRANSACTION;
INSERT INTO test VALUES(3,'test3','test123');
COMMIT;

Scenario 1:

T1 is started but not committed. At the same time, T2 is started. After the INSERT of T1 is complete, the INSERT of T2 is performed and succeeds. At the READ COMMITTED and REPEATABLE READ levels, the SELECT of T1 cannot see data inserted by T2, and a query in T2 cannot see the data inserted by T1.

Scenario 2:

  • READ COMMITTED level

    T1 is started but not committed. At the same time, T2 is started. After the INSERT of T1 is complete, T1 is committed. In T2, a query performed after INSERT can see the data inserted by T1.

  • REPEATABLE READ level

    T1 is started but not committed. At the same time, T2 is started. After the INSERT of T1 is complete, T1 is committed. In T2, a query performed after INSERT cannot see the data inserted by T1.

Concurrent UPDATE in the Same Table

Transaction T1:

1
2
3
START TRANSACTION;
UPDATE test SET address='test1234' WHERE name='test1';
COMMIT;

Transaction T2:

1
2
3
START TRANSACTION;
UPDATE test SET address='test1234' WHERE name='test2';
COMMIT;

Transaction T3:

1
2
3
START TRANSACTION;
UPDATE test SET address='test1234' WHERE name='test1';
COMMIT;

Scenario 1:

T1 is started but not committed. At the same time, T2 is started. The UPDATE of T1 and then T2 starts, and both of them succeed. This is because UPDATE operations use row-level locks and do not conflict when they update different rows.

Scenario 2:

T1 is started but not committed. At the same time, T3 is started. The UPDATE of T1 and then T3 starts, and the UPDATE of T1 succeeds. The UPDATE of T3 times out. This is because T1 and T3 update the same row, the lock on which is held by the uncommitted T1.

Concurrent Data Import and Queries

Transaction T1:

1
2
3
START TRANSACTION;
COPY test FROM '...';
COMMIT;

Transaction T2:

1
2
3
START TRANSACTION;
SELECT * FROM test;
COMMIT;

Scenario 1:

T1 is started but not committed. At the same time, T2 is started. The COPY of T1 and then the SELECT of T2 starts, and both of them succeed. In this case, T2 cannot see the data added by the COPY of T1.

Scenario 2:

  • READ COMMITTED level

    T1 is started but not committed. At the same time, T2 is started. The COPY of T1 is complete and T1 is committed. In this case, T2 can see the data added by the COPY of T1.

  • REPEATABLE READ level

    T1 is started but not committed. At the same time, T2 is started. The COPY of T1 is complete and T1 is committed. In this case, T2 cannot see the data added by the COPY of T1.