Week 9 - Transaction Processing and JDBC

Question 1

Conflict

Answer 1

Occurs when multiple users try to access/modify entries in the DB at the same time.

Question 2

Ways of addressing conflicts

Answer 2

Serialisation
Recovery (rollback)
Locking

Question 3

Transaction

Answer 3

An action or series of actions carried out by a single user or application program, which reads/updates the contents of a DB.

Question 4

ACID: Atomicity

Answer 4

A transaction should be treated as an indivisible unit. It should happen entirely or not happen at all.

Question 5

ACID: Consistency

Answer 5

A transaction must transform the DB from one consistent state to another consistent state - only valid data will be written to the DB.

Question 6

ACID: Isolation

Answer 6

Transactions should execute independently of each other.
Ensures the result of concurrent transactions is state equivalent to some serial order of execution.

Question 7

ACID: Durability

Answer 7

Effects of a successful transaction must be permanently recorded in the DB

Question 8

Which stages of ACID are managed by what?

Answer 8

Atomicity&raquo_space;> Transaction Recovery Subsystem
Consistency&raquo_space;> Programmers / DBMS module
Isolation&raquo_space;> Concurrency Control Subsystem
Durability&raquo_space;> Transaction Recovery Subsystem

Question 9

Concurrency

Answer 9

The ability to execute multiple operations or transactions out-of-order or in partial order without affecting the outcome.

Question 10

Out of order execution

Answer 10

Operations can be performed in any order as long as the final result is consistent

Question 11

Partial order execution

Answer 11

Some operations can be executed in parallel without order, while others may need to follow a specific order.

Question 12

Schedule

Answer 12

A list of atomic actions performed by one of a number of transactions.

Question 13

Scheduling errors: Lost Update

Answer 13

A completed update by one user can be overridden by another user.

Question 14

Scheduling errors: Uncommitted Dependency

Answer 14

Also known as “dirty read” (reading uncommitted data).
Reasons for not committing vary (e.g. connection or system problems).
Failure to commit causes a rollback, but other transactions are unaware of the rollback.

Question 15

Scheduling errors: Inconsistent Analysis Problem

Answer 15

When a transaction’s analysis is inconsistent due to reading data that is in the middle of being updated by another transaction.

Question 16

Serial Schedule

Answer 16

All operations from each transaction are executed consecutively, without operations from different transactions interleaving.

Question 17

Non-serial Schedule

Answer 17

Operations from different transactions are interleaved. Interleaving is what causes the three scheduling errors.

Question 18

Conditions for two operations to conflict

Answer 18

• They belong to different transactions
  AND
• They access the same item
  AND
• At least one of the operations is a write()

Question 19

Two schedules are conflict-equivalents if?

Answer 19

• They involve the same transactions
• They have the same set of conflicting operations (whether that is none, or 1, or more)

Question 20

Precedence graph

Answer 20

Good for visualizing conflicts between schedules
- Node for each transaction

Question 21

Desirable property of schedules

Answer 21

They can be rolled back in their entirety.

Question 22

When is a schedule recoverable?

Answer 22

If every transaction commits only after all transactions whose changes they read, commit.

Question 23

ACA Schedule

Answer 23

Avoiding Cascading Aborts (ACA)
- A schedule disallowing dirty reads
- Every ACA schedule is recoverable

Question 24

What is a Lock?

Answer 24

A solution to conflicts.

Locks prevent several transactions from accessing/changing items while another transaction accesses/changes it.

Question 25

How are locks represented?

Answer 25

A variable describing the status of an item.
Information held in a lock table.

Question 26

Deadlock

Answer 26

Each transaction in a set of transactions is waiting for an item locked by another transaction in the same set.

Question 27

Binary Lock

Answer 27

Two possible states: locked or unlocked
Two operations: lock(x), unlock(x)

Transaction must lock data item before read/write operations.
Transaction must unlock data item after finishing.
No two transactions can access same data item concurrently.

Question 28

Shared/Exclusive Lock

Answer 28

Multiple states
Three operations: read_lock(x), write_lock(x), unlock(x)

If a transaction has a shared lock on an item, it can read the item but not update it.
If a transaction has an exclusive lock on it, it can both read and update it.
Some systems allow upgrades to exclusive, and downgrades to shared.

Question 29

Two Phase Locking (2PL)

Answer 29

Guarantees serialisability

Every transaction must lock an item before accessing it.
Once a lock has been released, no new items can be locked.

Question 30

Solutions for deadlock: Timeout

Answer 30

Timeouts, Deadlock prevention, deadlock detection and recovery

A transaction waits a specified amount of time for a lock. If it isn’t granted, the transaction is aborted.

Question 31

Solutions for deadlock: Deadlock Prevention

Answer 31

All data items have to be locked at the beginning of a transaction (conservative 2PL)

Disadvantages:
- Locks are held longer, time consuming to determine whether all required locks are free, waits for all required locks

Question 32

Why do real DB applications in industry need both SQL and a GPPL (general purpose programming language e.g. java, python)

Answer 32

SQL for simplicity and efficiency of data manipulation
GPPL for its support of complex computation, complex structures of large programs, GUI and integration with other apps.

Question 33

Why use ODBC? (Open Database Connectivity/Connector)

Answer 33

• Provides unique interface between variety of GPPLs and a given DBMS
• Multiple programs written in multiple languages can access DBMS through same interface
• Program can change over time without changing the code for connecting to / accessing the DBMS
• GUI and database engine can be separated as two different products

Question 34

What are ODBC and JDBC

Answer 34

ODBC and JDBC are essentially APIs for accessing DBMSs (usually called drivers)

Question 35

Missing content? Check slides