MariaDB Tutorial / Compare Isolation Levels, Locks, and Deadlocks with Two Connections

Isolation controls which concurrent changes a transaction can read, while locks coordinate conflicting operations. Two separate connections with a controlled schedule can demonstrate REPEATABLE READ, READ COMMITTED, lock timeouts and deadlocks as distinct behaviors.

Prepare separate connections A and B

Open two command-line connections to the same learning server and shopcourse database. Check that CONNECTION_ID() differs, and run the setting-saving block below separately in each connection. Pasting both sides into one client does not create concurrency. Keep this lock experiment away from production data. Review transaction boundaries in Start InnoDB Transactions with COMMIT and ROLLBACK, and distinguish the SESSION values restored here from the configuration scopes in Inspect Server Settings, SQL Modes, Time Zones, and Logs.

SELECT CONNECTION_ID(),DATABASE(),@@SESSION.tx_isolation;
SET @saved_isolation=@@SESSION.tx_isolation;
SET @saved_wait=@@SESSION.innodb_lock_wait_timeout;

Create the table only in A, after checking the name and preserving any existing data:

USE shopcourse;
CREATE TABLE accounts_36_lab (
 account_id INT PRIMARY KEY,balance INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO accounts_36_lab VALUES(1,1000),(2,2000);

Check the MariaDB documentation for the syntax and conditions used here: for update, set transaction.

Compare REPEATABLE READ in A → B → A order

Start with account 1 at balance 1000 from the setup. Follow the connection labels; B runs only after A completes its first read.

A-1: establish the snapshot

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM accounts_36_lab WHERE account_id=1;

B-1: commit a change

START TRANSACTION;
UPDATE accounts_36_lab SET balance=1100 WHERE account_id=1;
COMMIT;

A-2: read in the same transaction

SELECT balance FROM accounts_36_lab WHERE account_id=1;

A-1 and A-2 both return 1000. This compares consistent reads by ordinary SELECT, not the behavior of a locking/current read such as FOR UPDATE.

A-3: finish and read again

ROLLBACK;
SELECT balance FROM accounts_36_lab WHERE account_id=1;

The final read returns 1100. See SET TRANSACTION for the isolation-level scope.

Repeat READ COMMITTED from an explicit reset

B-0: reset the previous exercise

ROLLBACK;
START TRANSACTION;
UPDATE accounts_36_lab SET balance=1000 WHERE account_id=1;
COMMIT;

A-1: first READ COMMITTED read

ROLLBACK;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts_36_lab WHERE account_id=1;

B-1: commit 1100

START TRANSACTION;
UPDATE accounts_36_lab SET balance=1100 WHERE account_id=1;
COMMIT;

A-2: second read and finish

SELECT balance FROM accounts_36_lab WHERE account_id=1;
ROLLBACK;

A reads 1000 first and 1100 second because each ordinary read uses committed data visible to that statement. Choose an application isolation policy using write-conflict and business-consistency requirements as well.

Distinguish a held lock from a lock timeout

A-1: hold the lock on row 1

START TRANSACTION;
SELECT account_id,balance FROM accounts_36_lab
WHERE account_id=1 FOR UPDATE;

B-1: attempt the same row update

SET SESSION innodb_lock_wait_timeout=3;
START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;

With A still open, B should reach a lock timeout after about three seconds. Do not assume that this error cancels B’s entire transaction; explicitly finish both connections.

B-2: clean up the failed attempt

ROLLBACK;
SET SESSION innodb_lock_wait_timeout=@saved_wait;

A-2: release the lock

ROLLBACK;

FOR UPDATE transaction requirements

Create a deadlock with four interleaved steps

Ensure both previous transactions have ended. These are four interleaved steps, not one sequential script: switch windows in the order A-1 → B-1 → A-2 → B-2.

A-1: lock only the first row

START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;

B-1: lock the other row

START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=2;

A-2: request B’s row

UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=2;

A waits here. Switch to B and run the next statement without waiting for A to finish.

B-2: request A’s row

UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;

With InnoDB deadlock detection enabled, one transaction can receive Deadlock found; the victim is not fixed. Issue ROLLBACK; separately in both windows. An application needs bounded retries of the transaction with duplicate-operation protection.

Restore settings and use a consistent locking order

Acquire multiple rows in a consistent key order and keep transactions short to reduce deadlocks. Broad predicates and missing useful indexes can increase lock scope. Investigate the queries, logs and transaction sequence rather than classify a deadlock as a generic connection failure.

ROLLBACK;
SET SESSION tx_isolation=@saved_isolation;
SET SESSION innodb_lock_wait_timeout=@saved_wait;
SELECT @@SESSION.tx_isolation,@@SESSION.innodb_lock_wait_timeout;

Restore the saved settings independently in both connections. User variables disappear when a connection closes, so inspect new connections afresh. End both transactions before removing only the disposable test table.

DROP TABLE shopcourse.accounts_36_lab;