Transaction and Concurrency Control (MIDTERMS)

transaction

Is a collection of operations that form a single logical unit of work.

 database request

Is the equivalent of a single SQL statement in an application program or transaction.

If a transaction has three (3) update statements and one (1) insert statement, the transaction uses four (4) ____________.

Database consistent state

 satisfies the constraints specified in the schema.

Assume that a specific table contains a gender column where it only accepts an entry having values of “Male” and “Female”. If a user attempts to enter a value of ‘Person’, then the database will disallow the entry of such a value.

Transaction log

A DBMS uses this to keep track of all the transactions that update the database.

In the case of system failure, this log helps bring the database back to a consistent state.

Atomicity

This requires that all operations (SQL requests) of a transaction should be completed.

Example: Transaction T1 has four (4) SQL requests that must be successfully completed. Otherwise, the entire transaction is aborted.

Consistency

This ensures that only valid data following all rules and constraints will be written in the database. When a transaction results in invalid data, the database reverts to its previous sta

Isolation

The data used during the execution of a current transaction cannot be used by another transaction until the first one is completed.

Durability

ensures that once transaction changes are done and committed, they cannot be undone or lost.

Atomicity requirement

If the transaction fails after step 3, and before step 6, the system should ensure that its updates are not reflected in the database, an inconsistency will result.

Consistency requirement

The sum of Aldous' account and Brendon's account is unchanged by the execution of the transaction. 

If between steps 3 and 6, another transaction is allowed to access the partially updated database, it will see an inconsistent database.

Isolation requirement

Transactions should be run sequentially. Therefore, no other transaction will happen on both accounts until the current transaction is completed.

Durability requirement

Once the user has been notified that the transaction has completed (i.e., the transfer of the P1000 has taken place), the updates to the database by the transaction must persist despite failures.

BEGIN TRANSACTION

 This marks the beginning of transaction execution.

COMMIT 

This signals a successful end of the transaction so that any changes (updates) executed by the transaction can be safely committed to the database and will not be undone.

ROLLBACK TRANSACTION

This signals that the transaction has ended unsuccessfully so that any changes or effects that the transaction may have applied to the database must be undone.

SAVE TRANSACTION

 is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.

@@TRANCOUNT

- returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.

Active state 

in this state, a transaction stays in this state to perform READ and WRITE operations.

Partially committed state

where the final statement in queries has been executed

§ Committed state -

 after all the operation has been completed

Failed State

 if one of the operations cannot be done or proceed

Terminated

corresponds to the transaction leaving the system and if it is either committed or aborted