{"title":"Locking Mechanisms","description":null,"content":"The _Isolation Levels_ chapter covered what guarantees the database provides when multiple transactions run concurrently. Locks are the primary mechanism the database uses to enforce those guarantees. Understanding how locks work helps you write transactions that are both correct and performant, and it gives you the tools to control concurrency explicitly when isolation levels alone are not enough.\n\n---\n\n# Sample Data\n\nThis chapter uses the `users`, `subscriptions`, `payments`, `tracks`, and `streams` tables. Insert these rows to follow along:\n\n\n```sql\nINSERT INTO users (user_id, username, email, country, timezone, signup_date, subscription_tier)\nVALUES\n (1, 'alice_m', 'alice@example.com', 'US', 'America/New_York', '2023-01-15', 'free'),\n (2, 'bob_jones', 'bob@example.com', 'US', 'America/Chicago', '2023-03-22', 'premium'),\n (3, 'carol_uk', 'carol@example.com', 'GB', 'Europe/London', '2023-06-10', 'free'),\n (4, 'dave_kr', 'dave@example.com', 'KR', 'Asia/Seoul', '2024-01-05', 'family'),\n (5, 'emma_dev', 'emma@example.com', 'US', NULL, '2024-02-14', 'free'),\n (6, 'frank_j', 'frank@example.com', 'GB', NULL, '2024-04-01', 'premium');\n\nINSERT INTO subscriptions (subscription_id, user_id, plan, start_date, end_date, amount, currency)\nVALUES\n (1, 1, 'free', '2023-01-15', NULL, 0.00, 'USD'),\n (2, 2, 'premium', '2023-03-22', NULL, 9.99, 'USD'),\n (3, 3, 'free', '2023-06-10', NULL, 0.00, 'USD'),\n (4, 4, 'family', '2024-01-05', NULL, 14.99, 'USD'),\n (5, 5, 'free', '2024-02-14', NULL, 0.00, 'USD'),\n (6, 6, 'premium', '2024-04-01', NULL, 9.99, 'USD');\n\nINSERT INTO payments (payment_id, user_id, amount, currency, payment_date, status, refund_amount)\nVALUES\n (1, 2, 9.99, 'USD', '2024-01-15', 'completed', 0),\n (2, 4, 14.99, 'USD', '2024-01-15', 'completed', 0),\n (3, 6, 9.99, 'USD', '2024-02-15', 'completed', 0),\n (4, 2, 9.99, 'USD', '2024-02-15', 'completed', 0),\n (5, 4, 14.99, 'USD', '2024-03-15', 'completed', 0),\n (6, 6, 9.99, 'USD', '2024-03-15', 'completed', 0),\n (7, 2, 9.99, 'USD', '2024-04-15', 'completed', 0);\n\nINSERT INTO tracks (track_id, album_id, title, duration_seconds, track_number)\nVALUES\n (1, 1, 'Morning Light', 230, 1),\n (2, 1, 'Sunset Drive', 195, 2),\n (3, 1, 'Midnight Run', 210, 3);\n\nINSERT INTO streams (stream_id, user_id, track_id, started_at, duration_listened, country)\nVALUES\n (1, 1, 1, '2024-03-01 10:00:00', 230, 'US'),\n (2, 1, 2, '2024-03-01 10:05:00', 195, 'US'),\n (3, 2, 1, '2024-03-01 11:00:00', 200, 'US'),\n (4, 3, 3, '2024-03-01 12:00:00', 210, 'GB'),\n (5, 2, 3, '2024-03-01 14:00:00', 180, 'US'),\n (6, 4, 1, '2024-03-02 09:00:00', 230, 'KR'),\n (7, 5, 2, '2024-03-02 10:30:00', 195, 'US'),\n (8, 6, 1, '2024-03-02 11:00:00', 220, 'GB');\n```\n\n\n---\n\n# Lock Types: Shared and Exclusive\n\nDatabases use two fundamental lock types to control concurrent access to data.\n\nA **shared lock** (also called a read lock) allows a transaction to read a row while preventing other transactions from modifying it. Multiple transactions can hold shared locks on the same row simultaneously, because reading does not conflict with reading.\n\nAn **exclusive lock** (also called a write lock) allows a transaction to modify a row and prevents all other transactions from reading or writing that row. Only one transaction can hold an exclusive lock on a given row at a time.\n\nThe compatibility between these two lock types follows a simple rule:\n\n\n| Lock Requested | Shared Held | Exclusive Held |\n|---|---|---|\n| Shared | Granted | Blocked |\n| Exclusive | Blocked | Blocked |\n\n\nTwo shared locks can coexist. But any combination involving an exclusive lock results in blocking: the requesting transaction waits until the held lock is released.\n\nHere is how this plays out in practice. Session A updates Bob's subscription amount, which acquires an exclusive lock on that row:\n\n\n```sql\n-- Session A\nBEGIN;\nUPDATE subscriptions SET amount = 12.99 WHERE user_id = 2;\n-- Exclusive lock acquired on Bob's subscription row\n-- (no COMMIT yet)\n```\n\n\nSession B tries to update the same row:\n\n\n```sql\n-- Session B\nBEGIN;\nUPDATE subscriptions SET amount = 14.99 WHERE user_id = 2;\n-- BLOCKED: waiting for Session A's exclusive lock to be released\n```\n\n\nSession B cannot proceed until Session A either commits or rolls back. Once Session A finishes, Session B's UPDATE unblocks and executes.\n\n\n```mermaid\nflowchart LR\n subgraph SA[\"Session A\"]\n A1[\"BEGIN\"]:::primary\n A2[\"UPDATE Bob's row
(exclusive lock)\"]:::orange\n A3[\"COMMIT\"]:::green\n end\n\n subgraph SB[\"Session B\"]\n B1[\"BEGIN\"]:::primary\n B2[\"UPDATE Bob's row
(blocked)\"]:::red\n B3[\"Lock acquired
UPDATE runs\"]:::green\n end\n\n A1 --> A2\n A2 --> A3\n B1 --> B2\n A3 -.-> |\"lock released\"| B3\n B2 -.-> B3\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef red fill:#ff8787,stroke:#000,color:#000\n```\n\n\nThe important detail: Session B is not rejected. It waits. The database queues the lock request and resumes Session B once the lock becomes available. This waiting behavior is what makes locks different from the serialization errors you saw with higher isolation levels, where the database aborts the conflicting transaction outright.\n\nMeanwhile, other sessions can still modify different rows in the same table. If Session C updates Carol's subscription (user_id = 3) during all of this, it proceeds without blocking because it touches a different row.\n\n\n```sql\n-- Session C (runs concurrently, no conflict)\nBEGIN;\nUPDATE subscriptions SET amount = 7.99 WHERE user_id = 3;\nCOMMIT;\n-- Succeeds immediately — different row, no lock conflict\n```\n\n\n---\n\n# Lock Granularity: Row-Level vs Table-Level\n\nLock granularity determines how much data a lock covers. The finer the granularity, the more concurrency you get, but the more overhead the database incurs to track all those locks.\n\n#### Row-Level Locks\n\nRow-level locking is the default for DML statements (INSERT, UPDATE, DELETE) in PostgreSQL, MySQL InnoDB, and SQL Server. When you update a row, the database locks only that specific row. Other transactions can freely read and write other rows in the same table.\n\nThis is what makes the Session C example above work: updating Carol's row does not conflict with the lock on Bob's row, because each row is locked independently.\n\nRow-level locking maximizes concurrency. In a table with millions of rows, locking a single row blocks almost nothing. The trade-off is overhead: the database must track each individual lock, which consumes memory.\n\n#### Table-Level Locks\n\nA table-level lock covers the entire table. No other transaction can modify any row in the table while a table-level lock is held. Databases use table-level locks for DDL operations (ALTER TABLE, DROP TABLE) and for certain bulk operations.\n\nIn PostgreSQL, you can also acquire a table-level lock explicitly using the LOCK TABLE command:\n\n\n```sql\nBEGIN;\nLOCK TABLE payments IN ACCESS EXCLUSIVE MODE;\n-- No other transaction can read or write this table\n-- until this transaction ends\n\nUPDATE payments SET status = 'refunded' WHERE payment_date < '2024-01-01';\nCOMMIT;\n```\n\n\nPostgreSQL defines several table lock modes with varying levels of restrictiveness. The most common ones are:\n\n\n| Lock Mode | Allows Concurrent Reads? | Allows Concurrent Writes? | Typical Use |\n|---|---|---|---|\n| ACCESS SHARE | Yes | Yes | Regular SELECT (acquired automatically) |\n| ROW SHARE | Yes | Yes | SELECT FOR UPDATE |\n| ROW EXCLUSIVE | Yes | No (conflicts with SHARE and higher) | UPDATE, DELETE, INSERT |\n| SHARE | Yes | No | CREATE INDEX (non-concurrent) |\n| ACCESS EXCLUSIVE | No | No | ALTER TABLE, DROP TABLE, LOCK TABLE |\n\n\nYou rarely need to use LOCK TABLE explicitly. The database acquires appropriate table-level locks automatically when you run DDL statements or certain operations. Explicit table locks are occasionally useful for bulk data loads or maintenance tasks where you want to prevent all concurrent access.\n\n#### Database Comparison\n\nDifferent databases handle lock granularity differently:\n\n\n| Behavior | PostgreSQL | MySQL InnoDB | SQL Server |\n|---|---|---|---|\n| Default DML lock | Row-level | Row-level | Row-level |\n| DDL lock | Table-level (ACCESS EXCLUSIVE) | Table-level (metadata lock) | Schema modification lock |\n| Explicit table lock | `LOCK TABLE ... IN mode MODE;` | `LOCK TABLES tbl WRITE;` | Table hints: `WITH (TABLOCKX)` |\n| Lock tracking | Per-row in shared memory | Per-row in lock system tables | Per-row, per-page, or per-table |\n\n\n---\n\n# Explicit Row Locking: SELECT FOR UPDATE\n\nRegular SELECT statements read data without acquiring locks that block writers (in PostgreSQL and MySQL InnoDB, reads use MVCC snapshots instead of locks). This is efficient for most queries, but it creates a problem for read-then-write operations.\n\nConsider this scenario: two concurrent requests both try to upgrade a free user to premium. The application logic reads the current tier, checks if the user is eligible, and then performs the upgrade.\n\n\n```sql\n-- Session A\nBEGIN;\nSELECT subscription_tier FROM users WHERE user_id = 5;\n-- Returns 'free' — eligible for upgrade\n```\n\n\n\n```sql\n-- Session B (concurrent)\nBEGIN;\nSELECT subscription_tier FROM users WHERE user_id = 5;\n-- Also returns 'free' — also thinks user is eligible\n```\n\n\nBoth sessions read the same value and both decide to proceed with the upgrade:\n\n\n```sql\n-- Session A\nUPDATE users SET subscription_tier = 'premium' WHERE user_id = 5;\nINSERT INTO payments (payment_id, user_id, amount, currency, payment_date, status, refund_amount)\nVALUES (8, 5, 9.99, 'USD', '2024-04-15', 'completed', 0);\nCOMMIT;\n```\n\n\n\n```sql\n-- Session B\nUPDATE users SET subscription_tier = 'premium' WHERE user_id = 5;\nINSERT INTO payments (payment_id, user_id, amount, currency, payment_date, status, refund_amount)\nVALUES (9, 5, 9.99, 'USD', '2024-04-15', 'completed', 0);\nCOMMIT;\n```\n\n\nThe user got charged twice. Both sessions read the old value, both decided to upgrade, and both inserted a payment. The SELECT did not block Session B from reading the same row that Session A was about to modify.\n\nSELECT FOR UPDATE solves this by acquiring an exclusive lock on the selected rows at read time:\n\n\n```sql\n-- Session A\nBEGIN;\nSELECT subscription_tier FROM users WHERE user_id = 5 FOR UPDATE;\n-- Returns 'free', AND locks user_id = 5\n```\n\n\n\n```sql\n-- Session B (concurrent)\nBEGIN;\nSELECT subscription_tier FROM users WHERE user_id = 5 FOR UPDATE;\n-- BLOCKED: waiting for Session A's lock\n```\n\n\nNow Session B cannot read the row until Session A finishes. Once Session A commits the upgrade, Session B unblocks and reads the updated value ('premium'), correctly determining that the user has already been upgraded.\n\n\n```mermaid\nflowchart TD\n A1[\"Session A:
SELECT ... FOR UPDATE
(reads 'free', locks row)\"]:::primary\n A2[\"Session A:
UPDATE tier to 'premium'
INSERT payment\"]:::orange\n A3[\"Session A:
COMMIT (releases lock)\"]:::green\n B1[\"Session B:
SELECT ... FOR UPDATE
(blocked, waiting)\"]:::red\n B2[\"Session B:
Lock acquired
reads 'premium'\"]:::teal\n B3[\"Session B:
Skips upgrade
(already premium)\"]:::green\n\n A1 --> A2\n A2 --> A3\n A3 -.-> |\"lock released\"| B2\n B1 -.-> B2\n B2 --> B3\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef red fill:#ff8787,stroke:#000,color:#000\n```\n\n\nSELECT FOR UPDATE works with JOINs too. When you join multiple tables, you can specify which table's rows should be locked:\n\n\n```sql\nBEGIN;\nSELECT u.username, s.plan, s.amount\nFROM users u\nJOIN subscriptions s ON s.user_id = u.user_id\nWHERE u.user_id = 4\nFOR UPDATE OF s;\n-- Locks only the subscriptions row, not the users row\nCOMMIT;\n```\n\n\nThe `OF s` clause locks only the `subscriptions` row. Without it, both the `users` and `subscriptions` rows would be locked.\n\n#### FOR SHARE\n\nThere is also a FOR SHARE variant (called FOR SHARE in PostgreSQL, LOCK IN SHARE MODE in MySQL). It acquires a shared lock instead of an exclusive lock. This prevents other transactions from modifying the selected rows but still allows them to read with FOR SHARE:\n\n\n```sql\nBEGIN;\nSELECT amount FROM payments WHERE payment_id = 1 FOR SHARE;\n-- Other transactions can also SELECT FOR SHARE on this row\n-- But no transaction can UPDATE or DELETE this row until we commit\nCOMMIT;\n```\n\n\nFOR SHARE is useful when you need to ensure a row does not change during your transaction but you don't intend to modify it yourself.\n\n---\n\n# NOWAIT and SKIP LOCKED\n\nBy default, SELECT FOR UPDATE blocks until the lock becomes available. This can be problematic when a transaction holds a lock for a long time. Two modifiers change this behavior.\n\n#### NOWAIT\n\nNOWAIT tells the database to fail immediately if the lock cannot be acquired, rather than waiting:\n\n\n```sql\n-- Session A\nBEGIN;\nSELECT subscription_tier FROM users WHERE user_id = 2 FOR UPDATE;\n-- Lock acquired on Bob's row\n```\n\n\n\n```sql\n-- Session B\nBEGIN;\nSELECT subscription_tier FROM users WHERE user_id = 2 FOR UPDATE NOWAIT;\n-- ERROR: could not obtain lock on row in relation \"users\"\n```\n\n\nInstead of waiting, Session B gets an immediate error. The application can catch this error and respond, either by retrying later or by returning an error to the user.\n\nNOWAIT is useful in web applications where you prefer a fast failure over an unpredictable wait. If a request cannot acquire the lock it needs within zero time, it can fail gracefully and let the user try again, rather than holding open a database connection and a web request while waiting.\n\n#### SKIP LOCKED\n\nSKIP LOCKED silently skips over any rows that are currently locked by other transactions. Instead of blocking or failing, the query returns only the rows it can lock:\n\n\n```sql\n-- Session A\nBEGIN;\nSELECT payment_id, user_id, amount\nFROM payments\nWHERE status = 'completed'\nORDER BY payment_date\nLIMIT 1\nFOR UPDATE SKIP LOCKED;\n-- Returns payment_id = 1, locks it\n```\n\n\n\n```sql\n-- Session B (concurrent)\nBEGIN;\nSELECT payment_id, user_id, amount\nFROM payments\nWHERE status = 'completed'\nORDER BY payment_date\nLIMIT 1\nFOR UPDATE SKIP LOCKED;\n-- Skips payment_id = 1 (locked by Session A)\n-- Returns payment_id = 2\n```\n\n\nEach session gets a different row to process. This is the foundation of a database-backed job queue. Multiple workers can pull from the same table concurrently, each processing a different row, without blocking each other.\n\n\n```mermaid\nflowchart LR\n Q[\"payments table
(job queue)\"]:::primary\n W1[\"Worker 1
processes payment 1\"]:::orange\n W2[\"Worker 2
processes payment 2\"]:::teal\n W3[\"Worker 3
processes payment 3\"]:::green\n\n Q --> W1\n Q --> W2\n Q --> W3\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\nA more complete job queue pattern updates the row's status after processing:\n\n\n```sql\n-- Worker picks up one pending payment\nBEGIN;\nSELECT payment_id, user_id, amount\nFROM payments\nWHERE status = 'completed' AND refund_amount = 0\nORDER BY payment_date\nLIMIT 1\nFOR UPDATE SKIP LOCKED;\n\n-- Process the payment (e.g., generate invoice, send receipt)\n\n-- Mark it as processed\nUPDATE payments SET status = 'processed' WHERE payment_id = 1;\nCOMMIT;\n```\n\n\nThe combination of FOR UPDATE SKIP LOCKED with LIMIT gives each worker exactly one uncontested row to process.\n\nBoth NOWAIT and SKIP LOCKED are supported in PostgreSQL (9.5+), MySQL (8.0+), and SQL Server (using `WITH (READPAST)` for similar SKIP LOCKED behavior and `WITH (NOWAIT)` for immediate failure).\n\n\n| Feature | PostgreSQL | MySQL | SQL Server |\n|---|---|---|---|\n| NOWAIT | `FOR UPDATE NOWAIT` | `FOR UPDATE NOWAIT` | `WITH (NOWAIT)` |\n| SKIP LOCKED | `FOR UPDATE SKIP LOCKED` | `FOR UPDATE SKIP LOCKED` | `WITH (READPAST)` |\n\n\n---\n\n# Pessimistic vs Optimistic Locking\n\nThe locking mechanisms covered so far (shared locks, exclusive locks, SELECT FOR UPDATE) all follow the **pessimistic locking** approach: acquire locks upfront, before doing any work, on the assumption that conflicts will happen.\n\n**Optimistic locking** takes the opposite approach. It assumes conflicts are rare. Instead of locking rows upfront, it lets all transactions proceed without locks and detects conflicts at write time. If two transactions tried to modify the same row, one of them fails and must retry.\n\n#### How Optimistic Locking Works\n\nOptimistic locking typically uses a version column. The application reads a row along with its version number, does its work, and then issues an UPDATE that includes the version in the WHERE clause:\n\n\n```sql\n-- Step 1: Read the row with its current version\nBEGIN;\nSELECT subscription_id, plan, amount, version\nFROM subscriptions\nWHERE user_id = 2;\n-- Returns: subscription_id=2, plan='premium', amount=9.99, version=1\n```\n\n\n\n```sql\n-- Step 2: Update, but only if the version hasn't changed\nUPDATE subscriptions\nSET amount = 12.99, version = version + 1\nWHERE user_id = 2 AND version = 1;\n-- If another transaction already changed this row, version won't be 1 anymore\n-- The UPDATE affects 0 rows, and the application knows it lost the race\nCOMMIT;\n```\n\n\nIf the UPDATE affects 0 rows, the version has changed since the read, meaning another transaction modified the row. The application must re-read and retry.\n\nThe `version` column is not part of the StreamFlow schema as defined, but it illustrates the pattern. In practice, you can also use a `last_modified` timestamp column for the same purpose.\n\n#### Comparison\n\n\n| Aspect | Pessimistic (SELECT FOR UPDATE) | Optimistic (version column) |\n|---|---|---|\n| When locks are acquired | At read time | No database locks used |\n| Conflict handling | Blocks competing transactions | Detects conflicts at write time |\n| Best for | High contention (many concurrent writes to same rows) | Low contention (rare conflicts) |\n| Drawback | Reduced concurrency, potential for long waits | Retries on conflict, wasted work |\n| Implementation | Database-level (SQL) | Application-level (version check in WHERE) |\n\n\n\n```mermaid\nflowchart TD\n subgraph PESS[\"Pessimistic Locking\"]\n P1[\"SELECT ... FOR UPDATE
(lock acquired)\"]:::primary\n P2[\"Do work\"]:::teal\n P3[\"UPDATE + COMMIT
(lock released)\"]:::green\n end\n\n subgraph OPT[\"Optimistic Locking\"]\n O1[\"SELECT ... (no lock)
read version=1\"]:::primary\n O2[\"Do work\"]:::teal\n O3[\"UPDATE WHERE version=1\"]:::orange\n O4{\"Rows affected?\"}:::orange\n O5[\"COMMIT\"]:::green\n O6[\"Retry from start\"]:::red\n end\n\n P1 --> P2 --> P3\n O1 --> O2 --> O3 --> O4\n O4 -- \"1 row\" --> O5\n O4 -- \"0 rows\" --> O6\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef red fill:#ff8787,stroke:#000,color:#000\n```\n\n\nThe choice between pessimistic and optimistic locking depends on the workload. If many transactions frequently compete for the same rows (high contention), pessimistic locking avoids the wasted work of retries. If conflicts are rare and you want maximum throughput, optimistic locking avoids the overhead and reduced concurrency of holding locks.\n\nMost ORMs (Hibernate, Django ORM, ActiveRecord) support optimistic locking through built-in version tracking. The application typically does not write raw SQL for this. The database itself is not aware of the optimistic locking strategy. It just sees a normal UPDATE with a WHERE clause that happens to include a version check.\n\n---\n\n# Lock Escalation\n\nWhen a transaction locks a large number of individual rows, tracking each lock consumes memory. Some databases address this by **escalating** row-level locks to coarser-grained locks automatically.\n\n#### SQL Server\n\nSQL Server is the main database that actively performs lock escalation. When a single transaction holds more than approximately 5,000 row-level locks on a single table, SQL Server can promote them to a table-level lock. The escalation path goes from row locks to table locks (in newer versions, SQL Server can also escalate to partition-level locks on partitioned tables).\n\nThis happens transparently. The application does not request it. If you run a large UPDATE that touches thousands of rows:\n\n\n```sql\n-- SQL Server\nBEGIN TRANSACTION;\nUPDATE streams SET duration_listened = duration_listened + 1\nWHERE country = 'US';\n-- If this touches enough rows, SQL Server may escalate to a table lock\nCOMMIT;\n```\n\n\nThe trade-off is clear: fewer locks to track (less memory overhead), but the table lock blocks all other access to the entire table, reducing concurrency.\n\nYou can control lock escalation behavior on a per-table basis in SQL Server:\n\n\n```sql\n-- SQL Server: disable lock escalation for a specific table\nALTER TABLE streams SET (LOCK_ESCALATION = DISABLE);\n```\n\n\n#### PostgreSQL\n\nPostgreSQL does not perform lock escalation. Row-level locks remain row-level regardless of how many rows are locked. If a transaction locks one million rows, PostgreSQL tracks one million individual locks. This keeps concurrency high but uses more memory for lock tracking.\n\nPostgreSQL manages this overhead through its lock table, which is stored in shared memory. If the lock table runs out of space, transactions that need additional locks will fail. You can increase the `max_locks_per_transaction` configuration parameter if you run into this limit.\n\n#### MySQL InnoDB\n\nMySQL InnoDB also does not escalate row locks to table locks. However, InnoDB uses a concept called **gap locks** and **next-key locks** that can lock ranges of index records rather than individual rows. These are not the same as lock escalation. Gap locks exist to prevent phantom reads at the REPEATABLE READ isolation level by locking the gaps between index entries where new rows could be inserted.\n\n\n| Behavior | PostgreSQL | MySQL InnoDB | SQL Server |\n|---|---|---|---|\n| Lock escalation | No | No | Yes (row to table at ~5,000 locks) |\n| Row lock limit | Limited by shared memory (`max_locks_per_transaction`) | No hard limit | Escalates before reaching memory pressure |\n| Gap locks | No (uses SSI for phantom prevention) | Yes (prevents phantom reads) | Key-range locks at SERIALIZABLE |\n| Control | N/A | `innodb_lock_wait_timeout` | `ALTER TABLE SET (LOCK_ESCALATION = ...)` |\n","pageType":"sql"}

Locking Mechanisms

Ashish Pratap Singh

Get Premium