Managing Concurrent Write Operations

Transaction Isolation

MogDB manages transactions based on MVCC and two-phase locks, avoiding conflicts between read and write operations. SELECT is a read-only operation, whereas UPDATE and DELETE are read/write operations.

• There is no conflict between read/write and read-only operations, or between read/write operations. Each concurrent transaction creates a snapshot when it starts. Concurrent transactions cannot detect updates made by each other.
  • At the READ COMMITTED level, if transaction T1 is committed, transaction T2 can see changes made by T1.
  • At the REPEATABLE READ level, if T2 starts before T1 is committed, T2 will not see changes made by T1 even after T1 is committed. The query results in a transaction are consistent and unaffected by other transactions.
• Read/Write operations use row-level locks. Different transactions can concurrently update the same table but not the same row. A row update transaction will start only after the previous one is committed.
  • READ COMMITTED: At this level, a transaction can access only committed data. This is the default level.
  • REPEATABLE READ: Only data committed before transaction start is read. Uncommitted data or data committed in other concurrent transactions cannot be read.

Write and Read/Write Operations

Statements for write-only and read/write operations are as follows:

• INSERT, used to insert one or more rows of data into a table
• UPDATE, used to modify existing data in a table
• DELETE, used to delete existing data from a table
• COPY, used to import data

INSERT and COPY are write-only operations. Only one of them can be performed at a time. If INSERT or COPY of transaction T1 locks a table, INSERT or COPY of transaction T2 needs to wait until T1 unlocks the table.

UPDATE and DELETE operations are read/write operations. They need to query for the target rows before modifying data. Concurrent transactions cannot see changes made by each other, and UPDATE and DELETE operations read snapshots of data committed before their transactions start. Write operations use row-level locks. If T2 starts after T1 and is to update the same row as T1 does, T2 waits for T1 to finish update. If T1 is not complete within the specified timeout duration, T2 will time out. If T1 and T2 update different rows in a table, they can be concurrently executed.

Potential Deadlocks During Concurrent Write

Whenever transactions involve updates of more than one table, there is always the possibility that concurrently running transactions become deadlocked when they both try to write to the same set of tables. A transaction releases all of its locks at once when it either commits or rolls back; it does not relinquish locks one at a time. For example, transactions T1 and T2 start at roughly the same time.

• If T1 starts writing to table A and T2 starts writing to table B, both transactions can proceed without conflict. However, if T1 finishes writing to table A and needs to start writing to the same rows as T2 does in table B, it will not be able to proceed because T2 still holds the lock on B. Conversely, if T2 finishes writing to table B and needs to start writing to the same rows as T1 does in table A, it will not be able to proceed either because T1 still holds the lock on A. In this case, a deadlock occurs. If T1 is committed and releases the lock within the lock timeout duration, subsequent update can proceed. If a lock times out, an error is reported and the corresponding transaction exits.
• If T1 updates rows 1 to 5 and T2 updates rows 6 to 10 in the same table, the two transactions do not conflict. However, if T1 finishes the update and proceeds to update rows 6 to 10, and T2 proceeds to update rows 1 to 5, neither of them can continue. If either of the transactions is committed and releases the lock within the lock timeout duration, subsequent update can proceed. If a lock times out, an error is reported and the corresponding transaction exits.

Concurrent Write Examples

This section uses the test table 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.

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

Concurrent INSERT and DELETE in the Same Table

Transaction T1:

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

Transaction T2:

START TRANSACTION;
DELETE test WHERE NAME='test1';
COMMIT;

Scenario 1:

T1 is started but not committed. At this time, T2 is started. After INSERT of T1 is complete, 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 this time, T2 is started. After INSERT of T1 is complete, T1 is committed and DELETE of T2 is executed. 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 this time, T2 is started. After INSERT of T1 is complete, T1 is committed and DELETE of T2 is executed. 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:

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

Transaction T2:

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

Scenario 1:

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

Scenario 2:

• READ COMMITTED level

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

• REPEATABLE READ level

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

Concurrent UPDATE in the Same Table

Transaction T1:

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

Transaction T2:

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

Transaction T3:

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

Scenario 1:

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

Scenario 2:

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

Concurrent Data Import and Queries

Transaction T1:

START TRANSACTION;
COPY test FROM '...';
COMMIT;

Transaction T2:

START TRANSACTION;
SELECT * FROM test;
COMMIT;

Scenario 1:

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

Scenario 2:

• READ COMMITTED level

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

• REPEATABLE READ level

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