Visibility of Committed Transactions

 Although we have focused on the all-or-nothing nature of transactions, they have other important benefits. Only committed transactions are visible to users. Although the current user sees his changes, other users do not see them until the transaction is committed.

For example, Table  shows two users issuing queries using the default mode in which every statement is in its own transaction.

 

Table: Visibility of single-query transactions

User 1	User 2	Description
	SELECT (*) FROM trans_test	returns 0
INSERT INTO trans_test VALUES (1)		add row to trans_test
SELECT (*) FROM trans_test		returns 1
	SELECT (*) FROM trans_test	returns 1

Table  shows the same query with user 1 using a multiquery transaction.

 

Table: Visibility of multiquery transactions

User 1	User 2	Description
BEGIN WORK		User 1 starts a transaction
	SELECT (*) FROM trans_test	returns 0
INSERT INTO trans_test VALUES (1)		add row to trans_test
SELECT (*) FROM trans_test		returns 1
	SELECT (*) FROM trans_test	returns 0
COMMIT WORK
	SELECT (*) FROM trans_test	returns 1

User 1 sees the changes made by his transaction. User 2, however, does not see the changes until user 1 commits the transaction.

This shielding is another advantage of transactions. They insulate users from seeing uncommitted transactions, so that users never see a partially committed view of the database.

As another example, consider the bank account query where we transferred $100 from one bank account to another. Suppose we were calculating the total amount of money in all bank accounts at the same time the $100 was being transferred. If we did not see a consistent view of the database, we might see the $100 removed from the account, but not the $100 added. Our bank account total would then be wrong. A consistent database view means that either we see the $100 in its original account or we see it in its new account. Without this feature, we would have to ensure that no one was making bank account transfers while we were calculating the amount of money in all accounts.

Although this case is a contrived example, real-world database users INSERT, UPDATE, and DELETE data all at the same time, even as others SELECT data. This activity is orchestrated by the database so that each user can operate in a secure manner, knowing that other users will not affect their results in an unpredictable way.