Database Security - Lecture 1 (Transaction Processing)

Database Security - Lecture 1 (Transaction Processing)

• Transaction concept: a unit of program execution accessing and potentially updating data items, ensuring a consistent database state.
• Transaction must complete either fully or not at all (Atomicity).
• Database must be consistent both before and after a transaction.
• Main issues: Hardware and system crashes; Concurrent execution of multiple transactions.

Lecture Objectives

• Transaction Concept
• Transaction State
• Implementation of Atomicity and Durability
• Concurrent Executions
• Serializability
• Recoverability
• Implementation of Isolation
• Transaction Definition in SQL
• Testing for Serializability

ACID Properties

• Atomicity: Operations either entirely complete or are not reflected at all in the database.
• Consistency: Execution of a transaction maintains database consistency.
• Isolation: Concurrent transactions operate independently, hidden from each other.
• Durability: Changes made by a successful transaction persist in the database despite failure.

Example of Fund Transfer

• The sum of account A and B must remain constant.
• Atomicity: If a transaction fails during execution, any changes made are not reflected in the database.

Transaction State

• Active: Initial state, the transaction is being executed
• Partially committed: Final statement has been executed
• Failed: discovery of normal execution failure
• Aborted: Transaction has been rolled back, restoring database to a previous state. Two options: restart or kill the transaction.
• Committed: Successful completion of transaction

Implementation of Atomicity and Durability

• Shadow-database scheme - assumes only one transaction is active, with a dedicated pointer (db_pointer) pointing to the current consistent copy of the database. Updated parts of the database are made on a shadow copy; it is only made the current copy after the transaction reaches a partial commit, and the updated pages have been flushed to disk
• The recovery mechanism guarantees changes to shadow copy. If the transaction fails, the old consistent copy is used and the shadow copy is deleted.

Concurrent Executions

• Multiple transactions run concurrently, increasing processor utilization and reducing average response time
• Concurrency control schemes manage interaction among transactions, preventing damage to database consistency.

Schedules

• Schedules indicate the chronological order of execution for concurrent transactions.
• All instructions are included; the original order of instructions must be maintained within each transaction.

Example Schedules

• Serial schedules execute transactions sequentially, preserving consistency.
• Concurrent schedules (conflict and view serializable) can be used for better database performance. Some (e.g., Schedule 4) might not be suitable.

Serializability

• A schedule's outcome is equivalent to a serial schedule if consistency is preserved.
• Two main forms: Conflict serializability: instructions that conflict are ordered; View serializability: outcome is equivalent to a serial schedule based on read/write values. Schedule 9 is an example that is view-serializable, but not conflict-serializable).

Conflict Serializability

• Instructions conflict if they access same data item (Q) and at least one updates Q.
• Example: read(Q), read(Q) don't conflict; read(Q) write(Q) conflicts.

Recoverability

• A recoverable schedule ensures that commit operations occur after any actions that involve reading data from another transaction, ensuring no inconsistent states are written.
• Cascading rollback: the failure of one transaction leads to the rollback of other dependent transactions.
• Cascadeless schedule is recoverable, but ensures no cascading rollback occurs (each transaction's commit operation comes before a read from another transaction.)

Implementation of Isolation

• Schedules must: be conflict or view serializable; be recoverable; be preferable cascadeless.
• Concurrency control schemes must balance concurrency and overhead.

Transaction Definition in SQL

• Implicit transaction start and explicit end with commit or rollback (to end a current transactional command).
• SQL defines levels of consistency including serializable (default), repeatable read, read committed, and read uncommitted.

Levels of Consistency in SQL-92

• Serializable: default, only committed data is read, consistently read in all instances.
• Repeatable read: only committed records are read, repeated reads return same data.
• Read committed: only committed data can be read, however successive reads of same record might return different values.
• Read uncommitted: even uncommitted data can be read.

Testing for Serializability

• Precedence Graph: a visual tool that determines transactions' linear ordering; an acyclic precedence graph indicates conflict serializability.
• Cycle detection (n^2 time) or more efficient (n + e) algorithms can identify whether a precedence graph is acyclic.
• Acyclic precedence graph means schedule can be reordered in consistent way.

Testing for View Serializability

• The precedence graph test for conflict serializability needs modification to test for view serializability.
• Checking for view serializability is an NP-complete problem, thus designing an efficient algorithm is challenging.
• Practical algorithms with sufficient conditions for view serializability do exist.

Concurrency Control vs. Serializability Tests

• Testing serializability after execution is late.
• Goal of concurrency control protocols is to produce serializable schedules, without explicitly examining precedence graphs.

This quiz covers the foundational concepts of transaction processing within database security. It explores essential principles such as atomicity, consistency, isolation, and durability (ACID properties), along with the handling of concurrent transactions. Dive into the details of transaction states, implementation strategies, and SQL definitions to enhance your understanding.