Detailed Database transactions

Transactions: A transaction is the basic unit of recovery and concurrency control and is a user-defined sequence of operations. These operations are either done or not, and are an inseparable unit of work. A logically related set of operations is bound together through a transaction so that the server maintains the integrity of the data.

Transactions Four features: 1. atomicity: Each operation in a transaction is either done or not done

2. Consistency: A transaction must transform a database from one consistent state to another

3. Isolation: Execution of one transaction cannot be disturbed by other transactions

4. Permanent: Once a transaction is committed, its changes to the data in the database should be permanent

Dirty reads: Data from another uncommitted transaction is read in one transaction process

Non-REPEATABLE READ: Multiple queries within a transaction return different data values (emphasis on update)

Phantom reads: One transaction range multiple queries return different data values (emphasizing insert,delete)

Five isolation levels in sring:

Transaction_none: Do not use transactions

Transaction_read_uncommitted: Allow dirty reads

Transaction_read_committed: Prevent dirty reads, most commonly used isolation levels, most databases default isolation levels

Transaction_repeatable_read: Prevent dirty reads and non-repeatable reads

Transaction_serializable: Prevent dirty reading, non-repeatable reading, phantom reading, low efficiency

Detailed Database transactions