Dev Learning Lab

The labs on this site need JavaScript to run. The same content is written out below.

Fundamentals › Database

Row locks

What happens when two transactions try to change the same row?

Many connections can work with a database at the same time. When one connection changes a row with UPDATE, the database puts a lock on that row. A lock is a mark that says "I am changing this row, so other connections must not change it." The SQL we write has no statement for this; the database takes the lock by itself when it receives the UPDATE. The lock is not released until the transaction of that connection ends with COMMIT or ROLLBACK. If another connection tries to change the same row, the database makes that UPDATE wait until the lock is released, and if it waits too long, the database ends it with an error. In this lab you change the row that connection B updates and remove the COMMIT of connection A, run the same SQL, and see when the UPDATE of B runs and what the final balances are.

Checked against real execution · Ran all 4 combinations on H2 2.4.240 with two connections and checked against the results; the rules were checked against the MySQL documentation

Concepts in this lab

Row locks
A row lock is a lock that the database puts on a row when a transaction changes that row. In MySQL InnoDB, UPDATE ... WHERE puts an exclusive lock on the index records it meets in the search, and if it finds one row with a unique index such as a primary key, that one record lock is enough. When one transaction holds an exclusive lock on a row, a request by another transaction for a lock on the same row cannot be granted at once, and it must wait until the earlier transaction releases the lock. InnoDB locks are released when the transaction commits or aborts, so if you leave a transaction open without ending it, that row stays locked. If the condition finds one row directly, such as by a primary key, the lock is put only on that row (if there is no suitable index for the search, it covers more rows), and it is not written in the code; the database that received the UPDATE takes it by itself.
Transaction
A way to group several data changes into a single unit so that either all of them are applied (commit) or all of them are canceled (rollback). In a bank transfer, it prevents an intermediate state where only one of the withdrawal and the deposit is applied. It is the most basic means a database has for keeping data consistent.
Shared locks and exclusive locks
A shared lock (S) is a lock for reading, and several transactions can hold it at the same time. An exclusive lock (X) is a lock for writing, and only one transaction can hold it. While data has an exclusive lock, no other transaction can take a shared lock or an exclusive lock on it.
Lock wait timeout
A failure of a statement that waited for a lock held by another transaction to be released, up to a set time, and still could not get it. In MySQL InnoDB, when innodb_lock_wait_timeout (default 50 seconds) passes, error 1205 (Lock wait timeout exceeded) is returned. Unlike a deadlock, it can occur even when there is no cycle of mutual waiting, and a common cause is another transaction being kept open for a long time. Under the default settings, only the statement that was waiting is canceled and the transaction stays alive, so the earlier changes and locks remain. That is why, when you get this error, you must roll back yourself and then decide whether to try again.

Try these

Running with the default settings

  1. CREATE TABLE and INSERT: The database creates the account table and puts a balance of 1000 in each of account 1 and account 2. The path a program uses to reach the database is called a connection, and many connections can be attached to a database at the same time. In the code on the screen, the "Connection A" and "Connection B" parts are the SQL that one connection each sends, and the two connections write to the same table at the same time. We follow it one statement at a time in time order, not in the order of the parts, starting from the next step.
  2. BEGIN (connection A): Connection A starts a transaction. This transaction continues until it meets COMMIT or ROLLBACK.
  3. UPDATE account SET balance = balance - 100 WHERE id = 1 (connection A): The database changes the balance of the id 1 row from 1000 to 900. At this moment the database puts a lock on this row. There is no statement in the SQL we wrote that takes the lock; the database does it by itself when it receives the UPDATE. The lock is not released until this transaction ends with COMMIT or ROLLBACK. 900 is not final yet, so if another connection reads this row, it sees 1000.
  4. BEGIN (connection B): Connection B also starts its own transaction. A and B are different connections, so their transactions are separate too.
  5. UPDATE account SET balance = balance + 50 WHERE id = 1 (connection B): B tries to change the same id 1 row as A. The database first checks whether another connection holds a lock on this row. The lock of A is on it, so the UPDATE of B cannot run and waits. B stops at this statement and cannot go on to the next one. (Only a statement that changes the row waits. When another connection read this row with SELECT at the same time, it did not wait and read 1000 right away. Checked on H2.)
  6. COMMIT (connection A): A makes its transaction final. The 900 in the id 1 row is now final and can be read from other connections, and the lock that A held is released. B was waiting, so the database right away continues the UPDATE of B.
  7. The UPDATE of B continues. The database reads the current balance of the id 1 row and adds 50. It adds to the 900 that A made final, so the balance becomes 950, and the change of A is not erased. Now the lock of B is on this row.
  8. COMMIT (connection B): B makes its transaction final. The 950 in the id 1 row is now final and can be read from other connections. Now the id 1 row has both the -100 of A and the +50 of B applied and is 950, and the lock of B is released too.
  9. After all the statements have ended, we read the accounts on a new connection. The result is (1, 950), (2, 1000). The -100 of A and the +50 of B were applied in order.

Result of this run

Code

CREATE TABLE account (id INT PRIMARY KEY, balance INT);
INSERT INTO account VALUES (1, 1000), (2, 1000);

-- Connection A (runs first)
BEGIN;
-- The row being changed gets a lock, and it is kept until this transaction ends
UPDATE account SET balance = balance - 100 WHERE id = 1;
-- End: make the transaction final and release the lock
COMMIT;

-- Connection B (runs after the UPDATE of A)
BEGIN;
-- The database first checks whether another connection holds a lock on the row to be changed
UPDATE account SET balance = balance + 50 WHERE id = 1;
COMMIT;

Assumptions of this model

It was run and checked against H2 2.4.240 (in-memory database, JDBC), and it was not checked against execution on MySQL. For all 4 combinations, it built the whole SQL on the screen (including the comment lines), split it into the "Connection A" and "Connection B" parts, and ran each part exactly as written, with connection A (the main thread) and connection B (a thread). The order of execution is the BEGIN and UPDATE of A, the BEGIN of B, the UPDATE attempt of B, the COMMIT of A (if there is one), and the COMMIT of B. It created a new in-memory database with a name (DB_CLOSE_DELAY=-1) for each combination so that the two connections look at the same database, and it left the isolation level at the H2 default, READ COMMITTED. What it checked is the three items of the result table. They are whether the UPDATE of B was still not finished after 0.4 seconds and whether it finished later than the time of the COMMIT of A (the times were recorded and compared), the kind of error if there was one (the SQLSTATE of H2 is HYT00, the error code is 50200, and the first line of the message is Timeout trying to lock table "ACCOUNT"; H2 writes the table name in the message even for a row lock), and the balances read on a new connection after all the statements ended. For the combinations that did not end, the read was also made while A held the lock, and the value read after closing the connection of A was the same. The time to wait for a lock was shortened to 1 second on connection B with SET LOCK_TIMEOUT 1000 (the H2 default is 2 seconds, and this setting is not in the code on the screen). The screen shows no time numbers, and the default of MySQL InnoDB is innodb_lock_wait_timeout of 50 seconds. As the facts the step sentences rely on, it also checked by execution the value that another connection read right after A's UPDATE (1000, because it is not final yet), that while B was waiting, a SELECT from another connection did not wait and read 1000 right away (that reads do not wait in H2 was measured separately, and the screen mentions it in only one sentence), that after B waited and ended with an error, auto-commit of B is still off so the transaction is still open, and that the UPDATE of B that ran afterward added 50 to the 900 that A made final and got 950. From the MySQL 8.4 documentation, we opened and checked section 17.7.1 InnoDB Locking (an exclusive lock lets a transaction change or delete the row, and when one transaction holds an exclusive lock on a row, a request by another transaction for a lock on the same row cannot be granted at once and must wait), section 17.7.3 Locks Set by Different SQL Statements in InnoDB (UPDATE ... WHERE sets an exclusive lock on the records it meets in the search, and if it finds one row with a unique index, an index record lock is enough; InnoDB locks are released when the transaction commits or aborts), and innodb_lock_wait_timeout on the page for InnoDB startup options and system variables (innodb-parameters) (default 50 seconds, error 1205 after waiting, and only the statement is rolled back, not the whole transaction). From the H2 documentation, we checked the Advanced page, Multi-Version Concurrency Control (a connection sees only the final data and its own changes, and if connections try to lock or change the same row at the same time, it waits until the lock timeout) and Lock Timeout (an error if the lock cannot be got, and it can be set for each connection). Section 17.7.3 does not directly state in a sentence when the lock of an UPDATE is released; we read it from the general sentence in the same section (InnoDB locks are released when the transaction commits or aborts). We did not check by execution whether MySQL gives the same result for the same SQL. This lab covers only the exclusive lock and the waiting on one row, and does not cover read locks (SELECT ... FOR UPDATE), the differences by isolation level (iso), its use in stock decrement (stock), deadlocks where connections wait for each other (dbdeadlock), or locks on the empty gaps between rows (gaplock).

Previous lab: Transactions · Next lab: Storage and durability

Concepts in this lab

What this lab shows

Settings

Code


      
    


    

      
Diagram

      

      

    


    

      

      

      

    


  


  

    

      
Run comparison

      

      

    

    

      
Try these