1

Assume a "Bank_Account" table exists and contains a single column "balance", where everyone has balance=100.

If a transaction performs "update Bank_Account set balance = 200 where 1=1; commit;" Is it possible for any 2 transactions performing a select on the Bank_Account table to have one read 200 and another one read 100 for a different bank account?

Or once 200 is returned for a given bank account, all others necessarily return 200 too because the commit was atomic?

My database intuition would be that once one reads 200, all others must read 200 because a transaction is atomic, but i also read that some selects can execute without locking the table or rows and return a snapshot of data at the start of a transaction, even assuming read committed isolation level.

If that is the case, how do databases implement this behavior without locks? What magic does the commit and the selects perform to assure the selects operate on an atomic, all at same time, behavior of a commit?

Thanks

1 Answer 1

2

COMMIT is atomic, either the whole transaction is committed or none of it.

some selects can execute without locking the table or rows and return a snapshot of data at the start of a transaction

If you use a database engine that supports concurrency (ie, not MyISAM) then each query sees the database as it was when the snapshot was taken. In the "read committed" isolation level, the the snapshot is taken at the beginning of the query, once per query. In "repeatable read" the snapshot is taken at the beginning of the transaction.

Here are two clients connected to a database server performing queries concurrently. Assuming READ COMMITTED:

client 1      client 2
BEGIN
UPDATE stuff
              SELECT stuff
COMMIT

In this case, SELECT from client 2 will see the older version of stuff, because client 1 hasn't committed yet when the SELECT takes its snapshot. This is true even if the SELECT takes a while to run and client1 commits before it is finished, as the snapshot for the SELECT was taken before client1's commit.

To implement this, the database must keep a copy of all versions of a row that could still be needed. This includes previous versions of rows updated by a transaction that hasn't committed yet, all versions still visible by an active snapshot, etc. It's rather complicated.

One way of doing this is MVCC which can be implemented in different manners, each with its own tradeoffs: new versions can be stored in the table along with the old version, but that bloats the table, makes holes when they're deleted, and requires some free space to be kept in each page to be efficient. Or old versions can be moved to a "version store" or "undo log" and the new version stored in place of the old, but in case of rollback that requires moving all the old versions back where they were.

Also there are locks everywhere.

There are internal locks that are invisible to the client: for example when it modifies one page, it's probably going to lock it while writing the row in it to ensure the pointers, counts, etc end up in a coherent state. This type of lock is not held for the duration of a transaction, it is only held to do what's needed, typically write some stuff in memory, so it's very short.

Then there are locks that are visible to the client: when you do an UPDATE, the database will put a lock on that row until you COMMIT. So if another client attempts to update that specific row, it will have to wait until you either commit or rollback. Otherwise it wouldn't know which version of the row the UPDATE gets. The whole point is this type of lock does not block reads, so the row can still be selected.

The important thing is that there are no locks on the whole table, because that absolutely destroys performance.

If your queries take locks, it's important to try to release them as soon as possible.

4
  • But is the COMMIT also "instantaneous" from a transactional point of view? Or, assuming the commit was composed, for the sake of example, by 10 small updates, would it be possible for multiple transactions performing SELECT on the data being update to have some read updated values and some read non-updated (yet) values? Dec 22, 2023 at 20:47
  • 1
    It's atomic, all or nothing. I mean, not "nuclear big boom" but the original sense of "atom" which is "something that cannot be divided".
    – bobflux
    Dec 22, 2023 at 21:45
  • This all makes the assumption that every DBMS that can do ACID transactions uses optimistic concurrency (snapshots), which is absolutely not the case. Dec 24, 2023 at 19:18
  • Could you provide examples?
    – bobflux
    Dec 24, 2023 at 21:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.