How does a database read data change during the transaction?
database, sql, transactions
Solution
Let's think on database table as B-Tree. Let me talk couple words on it datastructure - all we should know for your topic that B-tree is page organized. Assume you have 9 rows (marked from `A..I`) and B-tree with page size=3. Some way we have 3 pages on disk
Page1: A,B,C,
Page2: D,E,F
Page3: G,H,I
Assume you have changed something in row `E`. Your database connection will allocate memory for the page2 and totally load it (`D..F`). You made changes to `E` but transaction is not committed. Now you try to select (in the same connection). Since memory is already contains page loaded, your SELECT will see data that is modified. But if another connection will try load `E` it have to load to memory in-mutable `D..F` page2. After commit page2 is persisted, so all another connections could see changes.
Of course in real world the process much more complicated.
Problem
Let's assume the following scenario: ``` [Start TX] SELECT userName FROM users WHERE userId = 1; -- returns x UPDATE users SET userName = 'y' where userId = 1; SELECT userName FROM users WHERE userId = 1; -- returns y [End TX] ``` How does the database knows to return y the second time? How is the transaction state integrated into the query processing? Another scenario: ``` [Start TX] SELECT userName FROM users, accounts WHERE useres.userId = accounts.userId AND accounts.balance < 0; -- returns x UPDATE accounts SET balance = 100 where userId = 1; SELECT userName FROM users, accounts WHERE useres.userId = accounts.userId AND accounts.balance < 0; -- returns nothing [End TX] ``` Same question - how does the database runs the join over the transaction information?