Concurrency control is essential in multi-user database environments to maintain data integrity and consistency. KingbaseES (KES), like other relational databases, implements transaction isolation levels to manage how transactions interact with each other during concurrent execution.

The SQL standard defines four isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. However, KES deviates slightly from this model:

• READ UNCOMMITTED behaves identically to READ COMMITTED—dirty reads are not permitted.
• REPEATABLE READ prevents phantom reads, unlike some other databases where phantoms may still occur.

Below are practical demonstrations of key phenomena under different isolatino levels.

Dirty Read (Not Possible in KES)

A dirty read occurs when a transaction reads uncommitted changes from another transaction. In KES, even if you explicitly set the isolation level to READ UNCOMMITTED, the system enforces at least READ COMMITTED semantics.

-- Session A
BEGIN ISOLATION LEVEL READ UNCOMMITTED;
-- No rows visible yet
SELECT * FROM accounts WHERE id = 100;

-- Session B
BEGIN;
INSERT INTO accounts (id, balance) VALUES (100, 500);
-- Do NOT commit

-- Session A
SELECT * FROM accounts WHERE id = 100; -- Still returns nothing

After rolling back Session B, Session A never observes the uncommitted row—confirming that KES blocks dirty reads regardless of the requested isolation level.

Non-Repeatable Read (Possible under READ COMMITTED)

This occurs when a transaction reads the same row twice but gets different values because another transaction committed an udpate in between.

-- Session A
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1; -- Returns 1000

-- Session B
BEGIN;
UPDATE accounts SET balance = 1100 WHERE id = 1;
COMMIT;

-- Session A
SELECT balance FROM accounts WHERE id = 1; -- Now returns 1100
COMMIT;

Under READ COMMITTED, each SELECT sees the latest committed data, so the second read reflects the update—demonstrating a non-repeatable read.

Phantom Read (Prevented under REPEATABLE READ in KES)

A phantom read happens when a range query returns different results on repeated execution due to inserts or deletes by concurrent transactions.

-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts WHERE id BETWEEN 1 AND 5; -- Returns 3 rows

-- Session B
BEGIN ISOLATION LEVEL REPEATABLE READ;
INSERT INTO accounts (id, balance) VALUES (4, 2000);
COMMIT;

-- Session A
SELECT * FROM accounts WHERE id BETWEEN 1 AND 5; -- Still returns 3 rows
COMMIT;

Similarly, if Session B deletes a row in the range and commits, Session A’s subsequent query still returns the original result set. This confirms that KES’s REPEATABLE READ eliminates phantom reads through its concurrency control mechanism—likely based on Multi-Version Concurrency Control (MVCC) with snapshot isolation.

KES maps its REPEATABLE READ level to what other systems (likee PostgreSQL) call SERIALIZABLE in terms of preventing phantoms, though true serializability (including detection of serialization anomalies) requires the SERIALIZABLE level.

In summary, KES provides strong consistency guarantees even at lower isolation levels, aligning closely with snapshot isolation semantics to balance performance and correctness in high-concurrency scenarios.