The secret of Locking Behavior in Mysql and Postgres

The default locking behavior of MySQL and PostgreSQL can affect how transactions interact with one another. Below, I’ll explain the default behavior for both databases and provide a revised example using the previous scenario.

Default Locking Behavior

MySQL

• Default Isolation Level: The default isolation level for MySQL is Repeatable Read.
• Locking Behavior:
  • In the Repeatable Read isolation level, a row is locked when it is read for updates, preventing other transactions from modifying it until the first transaction is committed or rolled back.
  • Other transactions can still read the data but may not see the changes made by the transaction holding the lock until it is committed.

PostgreSQL

• Default Isolation Level: The default isolation level for PostgreSQL is Read Committed.
• Locking Behavior:
  • In Read Committed, a row is locked when it is modified (i.e., during an UPDATE statement). Other transactions attempting to modify the same row will be blocked until the first transaction is completed.
  • However, other transactions can read the data even if it is locked, and they will see the last committed version of the data.

Example of Default Locking Behavior

Below is a SQL-based example illustrating how transactions operate in MySQL and PostgreSQL, including the locking behavior.

Setup SQL for Both Databases

-- Create the accounts table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    balance NUMERIC NOT NULL
);

-- Insert an initial record
INSERT INTO users (balance) VALUES (500);

Transaction Example for MySQL

User1 Transaction:

-- User1 starts a transaction
START TRANSACTION;

-- User1 updates the balance (this will lock the row)
UPDATE users SET balance = balance - 100 WHERE id = 1;

-- User1 simulates waiting (sleeping for 30 seconds, just for demonstration)
SELECT SLEEP(30); -- MySQL-specific function to pause execution

-- User1 commits the transaction
COMMIT;

User2 Transaction:

-- User2 starts a transaction
START TRANSACTION;

-- User2 attempts to read the balance (this will see 500)
SELECT balance FROM users WHERE id = 1;

-- User2 attempts to update the same record
UPDATE accounts SET balance = balance + 50 WHERE id = 1;

-- User2 commits the transaction
COMMIT;  -- This will fail if User1's transaction is still active

Transaction Example for PostgreSQL

User1 Transaction:

-- User1 starts a transaction
BEGIN;

-- User1 updates the balance (this will lock the row)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- User1 simulates waiting (sleeping for 10 seconds)
SELECT pg_sleep(10); -- PostgreSQL-specific function to pause execution

-- User1 commits the transaction
COMMIT;

User2 Transaction:

-- User2 starts a transaction
BEGIN;

-- User2 attempts to read the balance (this will see 500)
SELECT balance FROM accounts WHERE id = 1;

-- User2 attempts to update the same record
UPDATE accounts SET balance = balance + 50 WHERE id = 1;

-- User2 commits the transaction
COMMIT;  -- This will fail if User1's transaction is still active

Key Points

1. Locking:

• When User1 executes the UPDATE statement, the row with id = 1 is locked until User1 commits.
• User2 will be blocked from executing the UPDATE if they try to do it while User1’s transaction is still active.

1. Reading the Balance:

• Both users can read the balance during their transactions. However, User2 will see the old balance (before the User1 update) if the transaction isolation level allows it.

1. Error Handling:

• If User2 tries to commit their changes while User1 is still holding the lock, they will experience a timeout or an error, depending on the specific database configuration.

Conclusion

This example demonstrates the default locking behavior of MySQL and PostgreSQL within transactions. Understanding this behavior is essential for managing concurrency and ensuring data integrity in your applications.