Week 8 L1

Question 1

What do we mean by transactions

Answer 1

A transaction is a logical unit of work.
A transaction consists of the execution of an application-specified sequence of operations.
e.g., SELECY, INSERT, UPDATE, DELETE

Question 2

What do we mean by commit/roll protocol?

Answer 2

atomicity is implemented by the commit/rollback protocol.
User must define boundaries of a transaction.
Begin : START TRANSACTION
Make permanent changes: COMMIT
Undo transaction : ROLLBACK

Question 3

What are log files?

Answer 3

Keep track of database transactions.
Contain information about all changes to a database.
These support the commit/rollback protocol.

Question 4

What do log files contain ?

Answer 4

Before-image of a data item, i.e., value before change
After-image of a data item. i.e., value after change
Lots of other information- e.g. timestamps etc

Question 5

How do commits and rollback use log files

Answer 5

ROLLBACK uses before image values backwards.

Commit uses after image values forward.

Question 6

What do we mean by concurrency control?

Answer 6

The process of managing simultaneous transactions without on interfering with one another.
Concurrency allows much more efficient use of resources, resulting in improved database performance.

Question 7

What are the problems with concurrency?

Answer 7

Concurrent access can cause interference and result in data inconsistencies.
Lost update- more than one transaction trying to update the same data.
Uncommitted dependency
Inconsistent analysis

Question 8

What do we mean by serialisation?

Answer 8

If transactions were only allowed to execute in series, then these concurrency problems would not exist.
The serialisation principle:
When two transactions operate in a parallel interleaved manner, then their effects should be the same as if they operated in a purely serial manner.

Question 9

How do we guarantee serialisability?

Answer 9

We use the two-phase locking protocol.

Question 10

What is a transaction lock

Answer 10

A transaction lock on data can prevent access to other transactions to prevent incorrect results.

Question 11

transaction lock two types?

Answer 11

Shared read lock (S-lock)- shared reading but not writing.
Exclusive write lock(X-lock)- exclusive access
A transaction may have to wait for a lock to be released until placing its own lock on the data item.

Question 12

What Do COMMIT AND ROLLBACK do to locks?

Answer 12

They release them.

Question 13

What do we mean by Two phase locking (and what are they)?

Answer 13

All locking operations must precede the first unlock operation in the transaction.
There are two phases:
Growing phase- acquires all locks.
Shrinking phase – releases all locks but acquires none.

Question 14

What does the lost update solution cause?

Answer 14

A deadlock

Question 15

How to solve deadlock ?

Answer 15

This is when two or more transactions are in a simultaneous wait state, each waiting for locks held by the other to be released.
Breaking the deadlock involves the lock manager, choosing one of the transactions and rolling it back – these releases its locks and allows the other transaction to proceed
Some systems will then re-execute the rolled back transaction.

Question 16

What do we mean by starvation? give example of a solution

Answer 16

When a transaction waits for an indefinite period of time while others continue normally
For example, when transaction wants X lock which it can never get because of new transactions continually gaining S-locks.
Solutions include locking in the order requested or increasing priority according to length of waiting time and number of previous aborts.

Question 17

What do we mean by recovery?

Answer 17

Restoring the database to a correct state in the event of failure
The transaction represents the basic unit of recovery.
Recovery must ensure atomicity by undoing uncommitted transactions and durability by redoing committed transactions after failure.
Recovery makes use of log files to restore the database.

Question 18

What do we mean by Checkpoint ? What are the operations

Answer 18

A synchronisation point between the database and log file . All buffers are forced-written to secondary storage.
This involves the following operations:
Write all log records to secondary storage
Write contents of buffers to secondary storage
Write a checkpoint record to the log file. This includes a list of all transactions in progress.

Question 19

What are the steps in the recovery technique?

Answer 19

Write ahead to the log file before the database as follows:
When transaction starts, register in log.
When the write operation performed, write data record to log.
Once the log record is written, write the update to the database buffers.
The updates to the database itself are written when the buffers are next flushed to secondary storage.
When the transaction commits, register in log.