Database Transaction Terminology

Transaction

A transaction is a sequence of one or more SQL statements that are executed as a single unit of work. A transaction can either be committed (applied to the database) or rolled back (undone), ensuring data consistency and integrity.

Commit

Committing a transaction means making its changes permanent in the database. After a successful commit, the changes are visible to other transactions.

Rollback

Rolling back a transaction means undoing its changes, reverting the database to its state before the transaction started. It is typically used in case of errors or exceptions.

Savepoint

A savepoint is a point within a transaction to which you can later roll back. It allows you to create intermediate points in a transaction and roll back to them without affecting the entire transaction.

Concurrency Control

Concurrency control mechanisms ensure that transactions execute in a way that preserves the consistency of the database.

Two common concurrency control protocols are Two-Phase Locking (2PL) and Optimistic Concurrency Control (OCC).

• In 2PL, transactions acquire locks before accessing data and release them after completion.
• In OCC, transactions are allowed to proceed without locks, but conflicts are detected and resolved at the end of the transaction.

Isolation Levels

Isolation levels define the degree to which one transaction must be isolated from the effects of other concurrent transactions. Common isolation levels include Read Uncommitted, Read Committed, Repeatable Read, and Serializable.

Deadlock

A deadlock occurs when two or more transactions are waiting for each other to release locks, resulting in a circular waiting condition. Database systems employ deadlock detection mechanisms to identify such situations and resolve them by rolling back one or more transactions involved in the deadlock.

Transaction Scheduling

Database systems use a scheduler to determine the order in which transactions are executed. The scheduler ensures that transactions are executed in a way that maintains the consistency of the database. It may use various algorithms to prioritize and schedule transactions based on factors like timestamp ordering or conflict resolution.

Timestamp Ordering

Each transaction is assigned a unique timestamp, and transactions are ordered based on these timestamps. This helps in determining the serialization order of transactions. Older transactions are typically given precedence over newer ones to avoid conflicts.