States of a transaction

Active state

• This is the first state in the lifecycle of the transaction. When a transaction starts execution, it enters the active state. It stays here as long as the sequential steps within the transaction are executed. While executing, the transaction will make certain changes to the state of a database. These changes are stored in buffered memory temporarily.

Partially committed state

• Once the transaction completed execution in the active state, it enters the partially committed state. This state is so named because the transaction is not yet complete and hence partial executed. The transaction is not completed until unless the changes are reflected in the database. A transaction with changes written in buffer memory is called as a partially committed transaction as the changes are not reflected in the database.

Committed state

• Once the transaction executes properly and is ready to be committed to database, it enters the committed state. This state deals with writing and reflecting the changes of a transaction within the database. Once a transaction reaches this state, it cannot be rolled back. If one has to revoke the changes, a new transaction with given reverted changes must be executed.

Failed state

• When a given transaction is executing in active state or is present in the partially committedstate,ifanyabrupteventhappensthenthetransactionmovestothefailed state and the changes are not reflected in the database.

Aborted state

• Once the transaction enters failed state, the changes made by the transaction must beundone. This is donebyrollingbackthetransactionandthenentering theaborted state.

• This is the last state in the lifecycle of a transaction. Whether a transaction completes execution or doesn’t, it will always reach the terminated state. This represents the closure of a given transaction.

Every new transaction goes through these states while executing. Every transaction will begin at start and arrive at end, not matter which route is taken. The goal for transaction execution is same, they must stick to ACID properties and preserve the integrity as well as consistency of the database.

2. Discuss the actions taken by the read_item and write_item operations on a database.

Solution:

While read data item is performed then data item is accessed from the database and available to users to read the same while in write data item is fetched from database and make the necessary changes to the data item.

3. What is meant by interleaved concurrent execution of database transactions in a multi-user system? Discuss why concurrency control is needed, and give informal examples.

Solution:

Many computer systems, including DBMSs, are used simultaneously by more than one user. This means the computer runs multiple transactions (programs) at the same time. For example, an airline reservations system is used by hundreds of travel agents and reservation clerks concurrently. Systems in banks, insurance agencies, stock exchanges and the like are also operated by many users who submit transactions concurrently to the system. If, as is often the case, there is only one CPU, then only one program can be processed at a time. To avoid excessivedelays,concurrent systems executesomecommands from oneprogram (transaction), then suspended that program and execute some commands from the next program, and so on. A program is resumed at the point where it was suspended when it gets its turn to use the CPU again. This is known as interleaving.

4. Discuss different types of possible transaction failures, with some examples.

Solution:

A transaction has to abort when it fails to execute or when it reaches a point from where it can’t go any further. This is called transaction failure where only a few transactions or processes are hurt.

Reasons for a transaction failure could be

• Logical errors − Where a transaction cannot complete because it has some code error or any internal error condition.

• System errors − Where the database system itself terminates an active transaction because the DBMS is not able to execute it, or it has to stop because of some system condition. For example, in case of deadlock or resource unavailability, the system aborts an active transaction.

5. Transactions cannot be nested inside one another. Why? Support your answer with an example.

Solution:

A nested transaction is a transaction that is created inside another transaction. A nested transaction’s purpose is to support transactions in stored procedures that can be called from a process already in a transaction or from a process that has no active transaction.

BEGIN TRAN Tran1 GO

BEGIN TRAN Nested Tran

INSERT INTO Table1 DEFAULT Values

GO 10

COMMIT TRAN Nested Tran

SELECT * FROM Table1

ROLLBACK TRAN Tran1

Since rolled back the outer transaction, the entire transaction is rolled back

6. Compare binary locks with exclusive/shared locks. Why is the latter type of locks preferable?

Solution:

Binary locks:-Binary locks are type of lock. It has only two states of a lock, it is too simple, and it is too restrictive. It is not used in the practice.

Exclusive/shared lock:-Exclusive/shared locks that may provide more general locking capabilities and that are used in practical database locking schemas. Read-lock as a shared lock and Write-lock as an exclusive lock.

From the above locks, exclusive/shared lock is preferable, because, Share-lock is the read-locked item through this other operations are allow to read the item and where as a write-locked is a single transaction exclusively holds the lock on the item. Here these are three locking operations.

That are

Read-lock (X)

Write-lock (X), and

Un lock (X)

7. Discuss why the schedule below is a non-serializable schedule. What went wrong with the multiple-mode locking scheme used in the example schedule?

Solution:

The reason this non-serializable schedule occurs is that the items Y in T1 and X in T2 were unlocked too early. To guarantee serialisability, we must follow an additional protocol concerning the positioning of locking and unlocking operations in every transaction. The best known protocol, two-phase locking

8. Given the graph below, identify the deadlock situations.

11 a) Without Deadlock b) Without deadlock c) With deadlock

Solution: d) With deadlock

As per the above figure there in no cycle is formed in figure a so no deadlock condition. In figure b also no cycle is formed so again no deadlock situation.

In figure c a cycle is formed T2→T6→T5→T2 so deadlock situation in this situation. In figure d a cycle is formed T2→T3→T6→T2 so deadlock situation in this situation.

9. What is two-phase locking protocol? How does it guarantee serialisability?

Solution:

Two Phase Locking Protocol also known as 2PL protocol is a method of concurrency control in DBMS that ensures serializability by applying a lock to the transaction data which blocks other transactions to access the same data simultaneously. Two Phase Locking protocol helps to eliminate the concurrency problem in DBMS.

This locking protocol divides the execution phase of a transaction into three different parts.

• In the first phase, when the transaction begins to execute, it requires permission for the locks it needs.

• The second part is where the transaction obtains all the locks. When a transaction releases its first lock, the third phase starts.

• In this third phase, the transaction cannot demand any new locks. Instead, it only releases the acquired locks.

The Two-Phase Locking protocol allows each transaction to make a lock or unlock request in two steps:

• Growing Phase: In this phase transaction may obtain locks but may not release any locks.

• Shrinking Phase: In this phase, a transaction may release locks but not obtain any new lock

It is true that the 2PL protocol offers serializability. However, it does not ensure that deadlocks do not happen.

In the above-given diagram, you can see that local and global deadlock detectors are searching for deadlocks and solve them with resuming transactions to their initial states.

10. Analyse the relationships among the following terminology: Problems of concurrency access to database (lost update, uncommitted dependency); serializable schedule; basic 2PL; deadlock; conservative 2PL; wait-die and wound-wait

Solution:

The lost update problem occurs when two transactions that access the same database items havetheiroperations interleavedin awaythatmakes thevalueofsomedatabaseitem incorrect.

That is, interleaved use of the same data item would cause some problems when an update operation from one transaction overwrites another update from a second transaction. An example will explain the problem clearly. Suppose the two transactions T1 and T2 introduced previously are submitted at approximately the same time. It is possible when two travel agency staff help customers to book their flights at more or less the same time from a different or the same office. Suppose that their operations are interleaved by the operating system

Uncommitted dependency occurs when a transaction is allowed to retrieve or (worse) update a record that has been updated by another transaction, but which has not yet been committed by that other transaction. Because it has not yet been committed, there is always a possibility that it will never be committed but rather rolled back, in which case, the first transaction will have used some data that is now incorrect - a dirty read for the first transaction.

A schedule S of n transactions is a serialisable schedule if it is equivalent to some serial schedule of the same n transactions. Notice that for n transactions, there are n possible serial schedules, and many more possible non-serial schedules. We can form two disjoint groups of the non-serial schedules: those that are equivalent to one (or more) of the serial schedules, and hence are serialisable; and those that are not equivalent to any serial schedule, and hence are not serialisable.

Atransactionissaidtofollowthetwo-phaselockingprotocol(basic2PLprotocol)ifalllocking operations (read_lock, write_lock) precede the first unlock operation in the transaction. Such a transaction can be divided into two phases: an expanding (or growing) phase, during which new locks on items can be acquired but none can be released; and a shrinking phase, during which existing locks can be released but no new locks can be acquired.

Conservative 2PL

A variation of the basic 2PL is conservative 2PL also known as static 2PL, which is a way of avoiding deadlock. The conservative 2PL requires a transaction to lock all the data items it needs in advance. If at least one of the required data items cannot be obtained then none of the items are locked. Rather, the transaction waits and then tries again to lock all the items it needs. Althoughconservative2PLisadeadlock-freeprotocol,thissolutionfurtherlimitsconcurrency.

Two schemes that use transaction timestamp to prevent deadlock are wait-die and wound-wait. Suppose that transaction Ti tries to lock an item X, but is not able to because X is locked by some other transaction Tj with a conflicting lock. The rules followed by these schemes are as follows:

• wait-die: if TS(Ti) < TS(Tj) (Ti is older than Tj) then Ti is allowed to wait, otherwise abort Ti (Ti dies) and restart it later with the same timestamp. •

• wound-wait: if TS(Ti) < TS(Tj) (Ti is older than Tj) then abort Tj (Ti wound Tj) and restart it later with the same timestamp, otherwise Ti is allowed to wait.

In wait-die, an older transaction is allowed to wait on a younger transaction, whereas a youngertransaction requestinganitem held byan oldertransactionis abortedandrestarted. The wound-die approach does the opposite: a younger transaction is allowed to wait on an older one, whereas an older transaction requesting an item held by a younger transaction preempts the younger transaction by aborting it. Both schemes end up aborting the younger of the two transactions that may be involved in a deadlock, and it can be shown that these two techniques are deadlock-free.

11. How does the granularity of data items affect the performance of concurrency control? What factors affect selection of granularity size for data items?

Solution:

The following are the factors that an affect the performance of concurrency control

1. Database Record

2. Field value

3. Block of disk

4. Complete file

5. Database

The granularity can affect the performance of concurrency control and recovery.

12. Discuss multi-version two-phase locking for concurrency control.

Solution: