A transaction is a sequence of one or more operations (usually in the form of 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.
Which of the following is an example of a database transaction?
Commit
Committing a transaction means making its changes permanent in the database. After a successful commit, the changes are visible to other transactions.
In a database system, a marks the successful completion of a transaction and permanently saves the changes made.
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.
What happens if a database transaction fails?
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.
Savepoint
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.
When multiple transactions try to modify the same data simultaneously, mechanisms are used to prevent inconsistencies.
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.
The concept of isolation levels defines the degree to which one must be isolated from the others.
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.
Deadlock occurs when two or more transactions wait indefinitely for each other to resources.
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.
What is a transaction schedule?
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.
