{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"concurrent-writes","title":"Two people, one row: lost updates and how the database prevents them","url":"https://addaly.com/learn/data-and-sql/concurrent-writes","position":60,"of":66,"minutes":9,"answer":"A read followed by a write is a gap another transaction can enter; close it with an atomic UPDATE that reads and writes in one statement, a FOR UPDATE lock.","keyIdea":"A read followed by a write is a gap another transaction can enter; close it with an atomic UPDATE that reads and writes in one statement, a FOR UPDATE lock, or a version check that detects the collision and retries."},"body":{"format":"markdown","text":"## The counter that goes wrong under load\n\nA course has 1 seat left. Two people click \"enrol\" at the same moment. Each request runs the same code: read `seats_left`, see 1, subtract one, write 0. Both read 1. Both write 0. Two students are enrolled in one seat, and the table says nothing is wrong.\n\nThis is a lost update, and it is the fundamental problem of concurrent writes: a read and a write that are two statements can have another transaction slip between them. Everything in this lesson is a way of closing that gap.\n\n## Make the read and the write one statement\n\n```sql\nUPDATE courses\nSET    seats_left = seats_left - 1\nWHERE  id = 42 AND seats_left > 0\nRETURNING seats_left;\n```\n\nThe database reads and writes the row under a lock it takes for the duration of the statement, so the second request cannot read between the first's read and write. It sees the row after the first request's write, `seats_left` is 0, the WHERE clause fails, and it updates nothing. `RETURNING` tells the application whether it won: a row means enrolled, no row means full. This is the first thing to reach for, and it handles most counters, balances and stock levels with no further machinery.\n\n## Lock the row for a read-then-write\n\nWhen the logic between the read and the write is too complicated for one statement, lock the row explicitly:\n\n```sql\nBEGIN;\nSELECT seats_left FROM courses WHERE id = 42 FOR UPDATE;\n-- application decides what to do\nUPDATE courses SET seats_left = seats_left - 1 WHERE id = 42;\nCOMMIT;\n```\n\n`FOR UPDATE` makes the second transaction's `SELECT ... FOR UPDATE` wait until the first commits, and then it sees the updated value. The gap is closed by making the second reader queue. The cost is that the row is locked for the length of the transaction, which is why the previous lesson said to keep transactions short: a `FOR UPDATE` held while a human thinks is a course nobody can enrol in.\n\n## Or detect the collision instead of preventing it\n\nOptimistic concurrency skips the lock and checks at write time whether anyone else got there first, using a version number:\n\n> Figure: One seat left, and the gap between a read and a write. A two by two grid, rows Read, then write and One UPDATE, columns One request and Two at once: Read, then write and One request: Seat sold; Read, then write and Two at once: Sold twice; One UPDATE and One request: Seat sold; One UPDATE and Two at once: Sold once. A read and a write in two statements leave a gap another transaction can enter. One UPDATE that reads and writes in the same statement closes it: the second request sees the row after the first has written, the WHERE fails, no row is returned, and the application learns it lost.\n\n```sql\nUPDATE courses\nSET    seats_left = 0, version = version + 1\nWHERE  id = 42 AND version = 7;\n```\n\nThe application read version 7. If another transaction has already updated the row, the version is 8, the WHERE fails, zero rows are updated, and the application re-reads and tries again. No lock is held between the read and the write, so it scales to many readers, at the cost of retries when collisions are common. Web applications use this pattern constantly, and the `version` column, or an `updated_at` timestamp used the same way, is the tell.\n\n## What a transaction can see\n\nPostgres's default isolation level is Read Committed: each statement sees the data as committed at the moment the statement started. Two SELECTs in one transaction can therefore see different data if someone committed in between. `REPEATABLE READ` freezes the view for the whole transaction, and `SERIALIZABLE` goes further and refuses to commit any transaction whose result could not have come from running the transactions one after another. The refusal appears as an error, `could not serialize access`, and the application must retry. Stricter levels trade retries for correctness; the atomic-update pattern above is correct at every level, which is another reason to prefer it.\n\n## Deadlock\n\nTransaction A locks row 1 and then wants row 2. Transaction B locks row 2 and then wants row 1. Each waits for the other forever. Postgres notices within a second, kills one of them with `deadlock detected`, and the other proceeds. The killed one must retry. The prevention is to lock rows in a consistent order, for instance by id ascending, so that two transactions wanting the same rows always queue rather than cross. A transfer between two accounts that always locks the lower id first cannot deadlock with another transfer.\n\n## The engines differ, again\n\nSQLite allows one writer at a time for the whole database file. A second writer gets `database is locked` and must wait or retry, which is fine for a phone app and is the reason SQLite is not the choice for a busy web service. DuckDB is similar, and built for a single analyst rather than a crowd. Postgres and MySQL lock rows, not files, so thousands of writers touching different rows proceed together, and the patterns in this lesson only matter when they touch the same one."},"check":{"question":"Two requests each run `SELECT balance FROM accounts WHERE id = 7` (both see 500), then `UPDATE accounts SET balance = 400 WHERE id = 7`, intending to withdraw 100 each. The final balance is 400. Which change makes the result correct?","options":[{"text":"Wrap each request's two statements in `BEGIN ... COMMIT`, because a transaction guarantees the other request cannot run until it finishes.","correct":false,"tempting":"A transaction alone does not block the other request's SELECT under Read Committed; both still read 500 and one update still overwrites the other."},{"text":"Write `UPDATE accounts SET balance = balance - 100 WHERE id = 7`, so the read and the write happen in one statement under the row lock.","correct":true},{"text":"Add an index on `accounts.id`, since the second request only overtook the first because the row lookup was slow.","correct":false,"tempting":"Speed does not remove the gap between a read and a write; two fast requests can still interleave in the same way."},{"text":"Change the isolation level to REPEATABLE READ, which makes the second UPDATE see the first one's result and subtract from 400.","correct":false,"tempting":"Under REPEATABLE READ the second transaction would fail with a serialization error rather than see the new value; it still needs the atomic form or a retry."}]},"prev":{"slug":"transactions-and-rollback","title":"Transactions: making a mistake reversible","url":"https://addaly.com/learn/data-and-sql/transactions-and-rollback"},"next":{"slug":"altering-a-live-table","title":"Changing a table that is in use","url":"https://addaly.com/learn/data-and-sql/altering-a-live-table"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Two people, one row: lost updates and how the database prevents them', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/concurrent-writes (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Two people, one row: lost updates and how the database prevents them","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":6,"slug":"changing-data-safely","title":"Changing data safely"},"answer":null,"keyIdea":"A read followed by a write is a gap another transaction can enter; close it with an atomic UPDATE that reads and writes in one statement, a FOR UPDATE lock, or a version check that detects the collision and retries.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"cols":["One request","Two at once"],"rows":["Read, then write","One UPDATE"],"type":"matrix","cells":[["Seat sold","Sold twice"],["Seat sold","Sold once"]],"title":"One seat left, and the gap between a read and a write","caption":"A read and a write in two statements leave a gap another transaction can enter. One UPDATE that reads and writes in the same statement closes it: the second request sees the row after the first has written, the WHERE fails, no row is returned, and the application learns it lost.","cellLabels":[["seats_left is 0","two in one seat"],["seats_left is 0","the second hits none"]]},"alt":"Figure: One seat left, and the gap between a read and a write. A two by two grid, rows Read, then write and One UPDATE, columns One request and Two at once: Read, then write and One request: Seat sold; Read, then write and Two at once: Sold twice; One UPDATE and One request: Seat sold; One UPDATE and Two at once: Sold once. A read and a write in two statements leave a gap another transaction can enter. One UPDATE that reads and writes in the same statement closes it: the second request sees the row after the first has written, the WHERE fails, no row is returned, and the application learns it lost."}},"blocks":[{"t":"h2","id":"the-counter-that-goes-wrong-under-load","runs":[{"text":"The counter that goes wrong under load"}]},{"t":"p","runs":[{"text":"A course has 1 seat left. Two people click “enrol” at the same moment. Each request runs the same code: read "},{"text":"seats_left","code":true},{"text":", see 1, subtract one, write 0. Both read 1. Both write 0. Two students are enrolled in one seat, and the table says nothing is wrong."}]},{"t":"p","runs":[{"text":"This is a lost update, and it is the fundamental problem of concurrent writes: a read and a write that are two statements can have another transaction slip between them. Everything in this lesson is a way of closing that gap."}]},{"t":"h2","id":"make-the-read-and-the-write-one-statement","runs":[{"text":"Make the read and the write one statement"}]},{"t":"code","lang":"sql","label":"SQL","text":"UPDATE courses\nSET    seats_left = seats_left - 1\nWHERE  id = 42 AND seats_left > 0\nRETURNING seats_left;"},{"t":"p","runs":[{"text":"The database reads and writes the row under a lock it takes for the duration of the statement, so the second request cannot read between the first’s read and write. It sees the row after the first request’s write, "},{"text":"seats_left","code":true},{"text":" is 0, the WHERE clause fails, and it updates nothing. "},{"text":"RETURNING","code":true},{"text":" tells the application whether it won: a row means enrolled, no row means full. This is the first thing to reach for, and it handles most counters, balances and stock levels with no further machinery."}]},{"t":"h2","id":"lock-the-row-for-a-read-then-write","runs":[{"text":"Lock the row for a read-then-write"}]},{"t":"p","runs":[{"text":"When the logic between the read and the write is too complicated for one statement, lock the row explicitly:"}]},{"t":"code","lang":"sql","label":"SQL","text":"BEGIN;\nSELECT seats_left FROM courses WHERE id = 42 FOR UPDATE;\n-- application decides what to do\nUPDATE courses SET seats_left = seats_left - 1 WHERE id = 42;\nCOMMIT;"},{"t":"p","runs":[{"text":"FOR UPDATE","code":true},{"text":" makes the second transaction’s "},{"text":"SELECT ... FOR UPDATE","code":true},{"text":" wait until the first commits, and then it sees the updated value. The gap is closed by making the second reader queue. The cost is that the row is locked for the length of the transaction, which is why the previous lesson said to keep transactions short: a "},{"text":"FOR UPDATE","code":true},{"text":" held while a human thinks is a course nobody can enrol in."}]},{"t":"h2","id":"or-detect-the-collision-instead-of-preventing-it","runs":[{"text":"Or detect the collision instead of preventing it"}]},{"t":"p","runs":[{"text":"Optimistic concurrency skips the lock and checks at write time whether anyone else got there first, using a version number:"}]},{"t":"code","lang":"sql","label":"SQL","text":"UPDATE courses\nSET    seats_left = 0, version = version + 1\nWHERE  id = 42 AND version = 7;"},{"t":"p","runs":[{"text":"The application read version 7. If another transaction has already updated the row, the version is 8, the WHERE fails, zero rows are updated, and the application re-reads and tries again. No lock is held between the read and the write, so it scales to many readers, at the cost of retries when collisions are common. Web applications use this pattern constantly, and the "},{"text":"version","code":true},{"text":" column, or an "},{"text":"updated_at","code":true},{"text":" timestamp used the same way, is the tell."}]},{"t":"h2","id":"what-a-transaction-can-see","runs":[{"text":"What a transaction can see"}]},{"t":"p","runs":[{"text":"Postgres’s default isolation level is Read Committed: each statement sees the data as committed at the moment the statement started. Two SELECTs in one transaction can therefore see different data if someone committed in between. "},{"text":"REPEATABLE READ","code":true},{"text":" freezes the view for the whole transaction, and "},{"text":"SERIALIZABLE","code":true},{"text":" goes further and refuses to commit any transaction whose result could not have come from running the transactions one after another. The refusal appears as an error, "},{"text":"could not serialize access","code":true},{"text":", and the application must retry. Stricter levels trade retries for correctness; the atomic-update pattern above is correct at every level, which is another reason to prefer it."}]},{"t":"h2","id":"deadlock","runs":[{"text":"Deadlock"}]},{"t":"p","runs":[{"text":"Transaction A locks row 1 and then wants row 2. Transaction B locks row 2 and then wants row 1. Each waits for the other forever. Postgres notices within a second, kills one of them with "},{"text":"deadlock detected","code":true},{"text":", and the other proceeds. The killed one must retry. The prevention is to lock rows in a consistent order, for instance by id ascending, so that two transactions wanting the same rows always queue rather than cross. A transfer between two accounts that always locks the lower id first cannot deadlock with another transfer."}]},{"t":"h2","id":"the-engines-differ-again","runs":[{"text":"The engines differ, again"}]},{"t":"p","runs":[{"text":"SQLite allows one writer at a time for the whole database file. A second writer gets "},{"text":"database is locked","code":true},{"text":" and must wait or retry, which is fine for a phone app and is the reason SQLite is not the choice for a busy web service. DuckDB is similar, and built for a single analyst rather than a crowd. Postgres and MySQL lock rows, not files, so thousands of writers touching different rows proceed together, and the patterns in this lesson only matter when they touch the same one."}]}],"check":{"question":[{"text":"Two requests each run "},{"text":"SELECT balance FROM accounts WHERE id = 7","code":true},{"text":" (both see 500), then "},{"text":"UPDATE accounts SET balance = 400 WHERE id = 7","code":true},{"text":", intending to withdraw 100 each. The final balance is 400. Which change makes the result correct?"}],"options":[{"text":[{"text":"Wrap each request’s two statements in "},{"text":"BEGIN ... COMMIT","code":true},{"text":", because a transaction guarantees the other request cannot run until it finishes."}],"correct":false,"tempting":[{"text":"A transaction alone does not block the other request’s SELECT under Read Committed; both still read 500 and one update still overwrites the other."}]},{"text":[{"text":"Write "},{"text":"UPDATE accounts SET balance = balance - 100 WHERE id = 7","code":true},{"text":", so the read and the write happen in one statement under the row lock."}],"correct":true,"tempting":null},{"text":[{"text":"Add an index on "},{"text":"accounts.id","code":true},{"text":", since the second request only overtook the first because the row lookup was slow."}],"correct":false,"tempting":[{"text":"Speed does not remove the gap between a read and a write; two fast requests can still interleave in the same way."}]},{"text":[{"text":"Change the isolation level to REPEATABLE READ, which makes the second UPDATE see the first one’s result and subtract from 400."}],"correct":false,"tempting":[{"text":"Under REPEATABLE READ the second transaction would fail with a serialization error rather than see the new value; it still needs the atomic form or a retry."}]}],"order":[0,3,1,2],"why":null},"prev":{"slug":"transactions-and-rollback","title":"Transactions: making a mistake reversible","minutes":9},"next":{"slug":"altering-a-live-table","title":"Changing a table that is in use","minutes":9}}}