SQL Recovery

sql - SQL Recovery
image source:-http://www.taskmanagerfix.com

Transaction recovery

image source:-http://www.taskmanagerfix.com

The database management system allows as a method to manage the data tables and it’s related information. Adata table set of records in a tables format. A transaction a programming unit whose execution may no change the container of the database, the transaction is executed as a single unit and help to recover the data also the transaction are defined as command and execute on the base of the given data table. The database must be a consistent state at the time of transaction processing.

The transaction terminates processing the instruction or due to lack of resource.

  • Sucidial transmission-> The transaction get terminate due to error while processing the transaction terminate and roll back the operation.
  • murderous termination-> The database get terminate due to its management software or operating system is known as murderous termination.
  • Successful termination-> the transaction terminated after successful execution of transaction known as successful termination.

Transaction log

the transaction is a process for managing the recorded and maintain lof for the recovery process. The transaction log also known as DBMS journal.

  • WHL(wright head logging)

It is a process of the database that allows managing the updation of record using transaction log.

The update is first written to the log before it’s applied to the database.

A transaction is not allowed to change the database record until the current value of the database is written to the stable storage device. It also helps to recover the data from transaction log when the database is inconsistency state.

  • Checkpoint

It is also a way to manage the transaction log and to manage the transaction log and its also represented as a checkpoint for data recovery the record is written into the log when all the change it up to this point and it reflected int he database. The record is saved after executing the transaction then can’t be a rollback to undo the checkpoint process make change permanent into the database after the successful execution.

Transaction state

The transaction is executed ar a different state from managing the data record. It helps us to update the record in the database and maintain life cycle. The data are processed in the consistent state of the transaction. The transaction gets stat execution process with database and commits the record when the transaction terminates successfully otherwise stop rollback or abort. The transaction. At the end of transaction state. The processing is terminated after successful execution.

Acid property

The transactions are executed on the base of particular property as ACID.

  1. Atomivity->It is the property transaction the run compilation as an indivisible unit of data at the end of the transaction either there is no change or the database has been changed in a consistent state. The change in a consistent state. The change of database is not reflected type execution of the atomic unit of transaction. It will be accessible to another transaction as well as the process mange at the outside of the database.
  2. Consistency->This property of transaction implies that if a database was in the consistent state before the execution of the transaction. After execution of transaction the database also in the consistent state.
  3. Isolation-> Isolation property manage the database transaction in Isolation format. A transaction process particular action performed by the database will be hidden from for other database transaction until it executed to terminate.
  4. Durablilty-> This property of database transaction implies that update and any change through a transaction will be reflected in the database after getting lose of failure after committed will not be managed by the previous transaction in this property the rollback of the transaction can’t perform after committed.

Concurrency control

The database is shared between multiple users and uses the different resource with a different system so we need to protect the data records and allow in a multiprogramming environment. The concurrency control helps us to manage the data record between multiple users. It allows protecting the data by unauthorized users. It provides required processing to the users like update read, writes, etc. In networking multiple users process a different type of job with the database, concurrency control manage user and provide required processing to each user and solve the error also.

1. Serializabilty->

The processing control manages the transaction and execute in a serial format. If the transaction is executed one after another without any inter-leaved operation on the other transaction it means a transaction is successfully executed or terminated than select another translated for execution. The transaction is eliminated when the current transaction generated an error at execution time. The transaction is managed in the particular schedule and execute one by one then generate output in some sequence. this schedule was known as a serialisability sequence.

 2. Recoverability->

The transaction is processed in particular schedule so easy to recover the data if the transaction gets failed. A transaction defines in the schedule for executing, until all the transaction in the schedule that has written a data, in particular, are committed, this schedule is not recovered the data item because the scheduled transaction is committed.

3.concurrency control scheme->

The concurrency control use a technique to manage the transaction an is processing.

  • Locking-> The data item is locked by a translation and provide access or modify to the users. A method that provides locking and unlocking process for database known as a lock manager.
  • Exclusive lock-> It is a locking system that allows recording the contained as well as modify. No other transaction can lock a data item when it is an exclusive lock by another transaction. In this transaction, we can update the record with a particular condition.
  • The concept of two-phased locking-> In this locking system all locks on required data item are first acquired before any lock is released. The number of locks increased from zero to maximum for a transaction that is known as growing phase and the number of locks gets decreased from maximum to zero that is known as a contracting phase. These phases are managed in the two-phase locking system. It has locked all the data items it needs for execution of the transaction.
  • Basic two-phase locking-> Two-phase locking method use growing and contracting phase basic locking feature that is known as of two-phase.

4. comparative two phase->

In this phase locking, a transaction locks all the required data item before execution of the existing transaction. The data item can’t be locked if the transaction doesn’t lock for ant item, instead, it weight until all the item required are available for locking. This locking is the deadlock-free mechanism.

5.Strick two locking phase->

It is locking system that manages the growing and containing phase but is also helpful to recover the data after failure the transaction.

6. Rigorous two-phase locking->

A transaction doesn’t release any of its locks like exclusive or share until commit or abort. This locking system is more the strict two-phase locking.


It is a method of database concurrency to identify the size of the data item is too large then the system gets an error for locking the processor problem. The lock manager use locking method on particular data items. The locking process control on the base of granularity or size of the data item. The degree of concurrency will be high for every small data item and vice-versa. Granularity select should be neither reduce the concurrency nor overhead of lock manages.

Dead lock->

It is a way to manage the locking processes which generate between the transactions chain. A transaction has claimed data item from the particular table and is waiting for another data item of another table. The data item of another table claimed another transaction and it waiting for third table data item.

The third table is locked by another transaction and this transaction and this transaction waiting for the data item of the first table but the first table release on the transaction and the chain of waiting continues. This condition identifies and deadlock. The deadlock conditions occurred when there is a circular chain of transaction waiting together of release the data item and claim for another transaction.

Deadlock dilution-> In wait for the graph, the deaths occur due to the cycle of the transaction. A deadlock can be detected by examining the unit wait for graph. If a cycle is detected in the graph then the system identifies deadlock occurs.

Deadlock recovery-> The transaction are connected together in a chain in wait for the graph, need to break the cycle of existing transaction condition by rollback operation.

Deadlock avoidance->It is the approach of transaction that generate in deadlock avoidance. If the requesting transaction is older than the existing transaction which is holding lock on require data item then requesting transaction allow to wait for the current transaction but if the requesting transaction is younger than the current transaction that is holding is abort or rollback or die.


Wound wait

If the requesting transaction is older than the transaction which holding the lock on requiring data item the younger transaction will be aborted or die but if the requesting transaction is younger then the transaction which is holding the required data item then the requiring transaction it allows for the wait.

About the author

Tutorial Circle

Add Comment

Click here to post a comment