Dbms

PRESENTATION ON TRANSACTION

1. Submitted By: Naresh Sharma A PRESENTATION ON TRANSACTION Submitted To: Mr . Pankaj Sir

2. TRANSACTION INTRODUCTION TRANSACTION CONCEPT STATES ACID PROPERTY IMPLEMENTATION OF ATOMICITY AND DURABILITY CONCURRENT EXECUTION SERIAL AND SORTING RECOVERABILITY TRANSACTION DEFINITION IN SQL

3. INTRODUCTION A transaction consists of a sequence of query and/or update statements . The SQL standard specifies that a transaction begins implicitly when an SQL statement is executed . One of the following SQL statements must end the transaction : Commit work Rollback work

4. A transaction is a unit of program execution that accesses and possibly updates various data items. Usually , a transaction is initiated by a user program written in a high-level data-manipulation language (typically SQL), or programming language (for example, C++, or Java), with embedded database accesses in JDBC or ODBC . A transaction is delimited by statements (or function calls) of the form begin transaction and end transaction. The transaction consists of all operations executed between the begin transaction and end transaction. TRANSACTION CONCEPT

5. STATE DIAGRAM OF A TRANSACTION

6. STATES We establish a simple abstract transaction model. A transaction must be in one of the following states: Active , the initial state; the transaction stays in this state while it is executing. Partially committed , after the final statement has been executed. Failed , after the discovery that normal execution can no longer proceed. Aborted , after the transaction has been rolled back and the database has been restored to its state prior to the start of the transaction. Committed , after successful completion.

7. ACID PROPERTY ATOMICITY :- Either all operations of the transaction are reflected properly in the database, or none are. CONSISTENCY :- Execution of a transaction in isolation (that is, with no other transaction executing concurrently) preserves the consistency of the database. ISOLATION :- Even though multiple transactions may execute concurrently, the system guarantees that, for every pair of transactions Ti and Tj , it appears to Ti that either Tj finished execution before Ti started or Tj started execution after Ti finished. Thus, each transaction is unaware of other transactions executing concurrently in the system . DURABILITY :- After a transaction completes successfully, the changes it has made to the database persist, even if there are system failures.

8. 1.The recovery- management component of a database system implements the support for atomicity and durability. E.g . the shadow-database scheme : 2.All updates are prefabricated on a shadow copy of the database db_pointer is made to repair to the updated shadow copy after the transaction reaches inclined commit and all updated pages make been flushed to disk. IMPLEMENTATION OF ATOMICITY AND DURABILITY

9. 3.db_pointer ever points to the modern conformable repeat of the database. In case transaction unsuccess, old consistent duplicate pointed to by db_pointer can be used, and the shade reduplicate can be deleted . 4.The shadow-database scheme : Assumes that exclusive one transaction is live at a instant. Assumes disks do not break Useful for book editors, but extremely inefficient for macro databases Variant called shadow paging reduces copying of information, but is still not pragmatic for large databases

10. 5.Does not interact concurrent transactions

11. The shadow-database scheme: Assumes disks to not disappoint Useful for book editors, but extremely inefficient for large databases: executing a single transaction requires copying the entire database.

12. A schedule is a collection of many transactions which is implemented as a unit. Depending upon how these transactions are arranged in within a schedule, a schedule can be of two types : Serial:  The transactions are executed one after another, in a non-preemptive manner. Concurrent:  The transactions are executed in a preemptive, time shared method CONCURRENT EXECUTION

13. In Serial schedule, not more than a single transaction is executing at any point of time. However , a serial schedule is inefficient in the sense that the transactions suffer for having a longer waiting time and response time, as well as low amount of resource utilization . In concurrent schedule, CPU time is shared among two or more transactions in order to run them concurrently.

14. SERIAL AND SORTING Basic Assumption – Each transaction preserves database consistency . Thus serial execution of a set of transactions preserves database consistency. A (possibly concurrent) schedule is serializable if it is equivalent to a serial schedule. Different forms of schedule equivalence give rise to the notions of: 1 . conflict serializability 2 . view serializability We ignore operations other than read and write instructions, and we assume that transactions may perform arbitrary computations on data in local buffers in between reads and writes . Our simplified schedules consist of only read and write instructions.

15. CONFLICT SERIALIZABILITY Instructions I and J of transactions I and J respectively, conflict if and only if there exists some item Q accessed by both I and J , and at least one of these instructions wrote Q. 1. I = read(Q), J = read(Q). The order of I and J does not matter, since the same value of Q is read by Ti and Tj , regardless of the order. 2. I = read(Q), J = write(Q). If I comes before J , then Ti does not read the value of Q that is written by Tj in instruction J . If J comes before I, then Ti reads the value of Q that is written by Tj. Thus, the order of I and J matters.

16. 3. I = write(Q), J = read(Q). The order of I and J matters for reasons similar to those of the previous case . 4. I = write(Q), J = write(Q). Since both instructions are write operations, the order of these instructions does not affect either Ti or Tj . However, the value obtained by the next read(Q) instruction of S is affected, since the result of only the latter of the two write instructions is preserved in the database . If there is no other write(Q) instruction after I and J in S, then the order of I and J directly affects the final value of Q in the database state that results from schedule S.

17. We say that I and J conflict if they are operations by different transactions on the same data item, and at least one of these instructions is a write operation . The final result of these swaps, schedule 6 of Figure 14.8, is a serial schedule .

18. RECOVERABILITY An integral part of a database system is a recovery scheme that can restore the database to the consistent state that existed before the failure . The recovery scheme must also provide high availability ; that is, it must minimize the time for which the database is not usable after a failure.

19. Failure Classification 1. Transaction failure . There are two types of errors that may cause a transaction to fail : 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.

20. 2. System Crash There are problems − external to the system − that may cause the system to stop abruptly and cause the system to crash. 3. Disk Failure Disk failures include formation of bad sectors, unreachability to the disk, disk head crash or any other failure, which destroys all or a part of disk storage.

21. Recovery and Atomicity When a DBMS recovers from a crash, it should maintain the following − It should check the states of all the transactions, which were being executed. A transaction may be in the middle of some operation; the DBMS must ensure the atomicity of the transaction in this case. It should check whether the transaction can be completed now or it needs to be rolled back. No transactions would be allowed to leave the DBMS in an inconsistent state.

22. There are two types of techniques, which can help a DBMS in recovering as well as maintaining the atomicity of a transaction − Maintaining the logs of each transaction, and writing them onto some stable storage before actually modifying the database. Maintaining shadow paging, where the changes are done on a volatile memory, and later, the actual database is updated .

23. TRANSACTION DEFINITION IN SQL A transaction is a set of T-SQL statements that are executed together as a unit like as a single T-SQL statement. If all of these T-SQL statements executed successfully, then a transaction is committed and the changes made by T-SQL statements permanently saved to database. If any of these T-SQL statements within a transaction fail, then the complete transaction is cancelled/ rolled back.

24. Types Of Transactions 1. Implicit Transaction Implicit transactions are maintained by SQL Server for each and every DDL (CREATE, ALTER, DROP, TRUNCATE), DML ( INSERT, UPDATE, DELETE) statements. All these T-SQL statements runs under the implicit transaction. If there is an error occurs within these statements individually, SQL Server will roll back the complete statement . 2. Explicit Transaction Explicit transactions are defined by programmers. In Explicit transaction we include the DML statements that need to be execute as a unit. Since SELECT statements doesn’t modify data. Hence generally we don’t include Select statement in a transaction.

25. THANK YOU !