Transactional Faults

Dirty Reads

          The most egregious fault is a transaction’s work being visible to other transactions before the transaction even commits its changes. When a transaction can read another transaction’s uncommitted updates, this is called a dirty read. The problem with dirty reads is that the data being read is not yet committed, so the transaction writing the data might be rolled back.

Non-Repeatable Reads

          A non-repeatable read is similar to a dirty read, but a non-repeatable read occurs when a transaction can see the committed updates from another transaction. Reading a row inside a transaction should produce the same results every time. If reading a row twice results in different values, that’s a non-repeatable read type of transaction fault.

Phantom Rows

          The least severe transactional-integrity fault is a phantom row. Like a non-repeatable read, a phantom row occurs when updates from another transaction affect not only the result set’s data values, but cause the select to return a different set of rows. Of these transactional faults, dirty reads are the most dangerous, while non-repeatable reads are less so, and phantom rows are the least dangerous of all.

Lost Updates

          A lost update occurs when two users edit the same row, complete their edits, and save the data, and the second user’s update overwrites the first user’s update. Because lost updates occur only when two users edit the same row at the same time, the problem might not occur for months. Nonetheless, it’s a flaw in the transactional integrity of the database that needs to be prevented.

Deadlocks

          A deadlock is a special situation that occurs only when transactions with multiple tasks compete for the same data resource. For example, consider the following scenario:

Transaction one has a lock on data A and needs to lock data B to complete its Transaction, and Transaction two has a lock on data B and needs to lock data A to complete its transaction. Each transaction is stuck waiting for the other to release its lock, and neither can complete until the other does. Unless an outside force intercedes, or one of the transactions gives up and quits, this situation could persist until the end of time.