SQL Transaction Properties

10 Key excerpts on "SQL Transaction Properties"

• 

  eBook - PDF

  Understanding Databases

  Concepts and Practice

  • Suzanne W. Dietrich(Author)
  • 2021(Publication Date)
  • Wiley

    (Publisher)

  9 Transaction Management LEARNING OBJECTIVES • To describe the ACID properties of a transaction • To understand how databases recover from failures • To express how databases provide support for concurrent access • To explain how database recovery and concurrency control support the ACID properties of a transaction One of the fundamental features of a database system is the inherent support for the properties of a transaction in the presence of concurrent execution and system failures. A transaction is a unit of code that takes the database state from one consistent state to another. However, during the execution of the transaction, the database may be in a temporarily inconsistent state. What if there is a system failure at that point in time when the database is inconsistent? How does the con- current execution of transactions by the operating system of the computer affect the outcome of the transactions? This chapter describes how the recovery and concurrency control mechanisms of a relational database support transactions in the presence of system failures and concurrent execution so that the database remains in a consistent state. 9.1 ACID Properties of a Transaction The properties of a transaction that guarantee the correctness of the database for concurrent exe- cution and system failures are known as its ACID properties: Atomicity, Consistency, Isolation, Durability. • Atomicity: The individual operations of a transaction must be performed in their entirety or not at all. • Consistency: The concurrent execution of transactions maintains the correctness of the database. • Isolation: The transaction should not reveal its uncommitted results to other transactions. • Durability: The effects of a committed transaction must be preserved. Table 9.1 shows the pseudocode for sample transactions over an online banking scenario, including TransferFunds, ATMWithdrawal, Dashboard, GetBalance, DepositFunds.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Database

  Principles Programming Performance

  • Patrick O'Neil(Author)
  • 2014(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  We will see a num-ber of such examples in what follows. At this point it is reasonable to review the four defining properties of a transaction, known as the ACID properties, that we met in the introduc-tion to this chapter. We say that transactions are Atomic. The set of updates contained in a transaction must succeed or fail as a unit. Consistent. Complete transactional transformations on data elements bring the database from one consistent state to another. For example, if money can neither be created nor destroyed, then successive states must all have the same balance totals. 9.2 Interleaved Read/Write Operations Isolated. Even though transactions execute concurrently, it appears to each 657 successful transaction that it has executed in a serial schedule with the oth-ers. (Some transactions might be aborted to guarantee isolation; they are not successful, but can be retried.) Durable. Once a transaction commits, the changes it has made to the data will survive any machine or system failures. The properties of atomicity, isolation, and durability are all guaran-teed by the database system to make the programmer's job easier. The idea of consistency is a logical property that the programmer must maintain in writing the logic for individual transactions acting under isolated circum-stances. By guaranteeing the isolation property, the system also guarantees that consistency is maintained, even in the presence of interleaved opera-tions and system failure. Interleaved Read/Write Operations We have just learned that a serial history, in which all operations of one transaction must complete before any operations of another can be exe-cuted, is not prey to the inconsistencies arising from interleaved operations of read and write.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Encyclopedia of Computer Science and Technology

  Volume 38 - Supplement 23: Algorithms for Designing Multimedia Storage Servers to Models and Architectures

  • Allen Kent, James G. Williams, Allen Kent, James G. Williams(Authors)
  • 2021(Publication Date)
  • CRC Press

    (Publisher)

  In this article, we adopt the second definition: A transaction is a partially ordered set of read/write operations', it represents the effect on the database of the processing of programs executing functions required by the users. Transaction processing systems pioneered many concepts in distributed computing and fault-tolerant computing. They introduced the notion of transaction ACID properties —atomicity, consistency, isolation, and durability —as unifying concepts for fault-tolerant and correct computations in both centralized and distributed settings. A transaction can be considered as a collection of operations with the following properties: Atomicity (also called all-or-nothing property): It refers to the fact that all the operations of a transaction must be treated as a single unit; therefore, either all operations are executed or none are. Consistency: It deals with the correctness of concurrently executing transactions. If executed alone, a transaction transforms a database from one consistent state to another consistent state. When transactions are executed concurrently, the DBMS must assure that the database consistency is preserved as if each transaction were executed alone. Isolation: It requires each transaction to observe a consistent database, that is, not to read intermediate results of other transactions. Durability: It requires the results of a committed transaction be made permanent in the database in spite of possible system failures.nn Example 1. Consider a banking debit transaction on Mr. Money’s current account. Such a transaction would consist of releasing money and updating the account. It is atomic if it never happens that only one operation is performed; for instance, the money is released but the account is not updated. It is consistent if the amount of money released is the same as the amount debited to the account

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  NoSQL

  Database for Storage and Retrieval of Data in Cloud

  • Ganesh Chandra Deka(Author)
  • 2017(Publication Date)
  • Chapman and Hall/CRC

    (Publisher)

  Transaction processing basically deals with processing information or data that are divided into individual, indivisible rather atomic operations called transactions. It is designed to maintain the integrity database or file systems in a consistent state, ensuring that mutually dependent operations on the system are either all completed successfully or all terminated successfully (Sheetlani Jitendra 2012). A transaction generally represents any change in database with two main purposes: (a) providing reliable units of work that allow correct recovery from failures and keeping a database consistent even in cases of system failure and (b) providing isolation between programs accessing a database concurrently.

  A database transaction, by definition, must be atomic, consistent, isolated, and durable. Database practitioners often refer to these properties of database transactions using the acronym ACID, and ACID properties are considered to be the essential properties of relational DBMS (RDBMS) to ensure successful transaction. (ACID properties for transaction are described in detail in the subsequent sections.)

  RDBMSs are compliant with the ACID, but NoSQL (Non-SQL/Not Only SQL) follows a different approach. It follows the CAP theorem laid down by Eric Brewers. This theorem states that it is mathematically impossible for an NoSQL DBMS to comply with all three (C, A, P) features listed down. Therefore, an NoSQL DBMS may only choose two of the features.

  The various approaches of how transactions access data in a distributed database in transaction processing are as follows: remote SQL (Structured Query Language) statements, distributed SQL statements, shared SQL for remote and distributed statements, remote transactions, and distributed transactions.

  1.2.3.1Distributed Transaction Processing in Relational Database

  A relational database is a digital database whose organization is based on the relational model of data, as proposed by E. F. Codd in 1970. The various software systems used to maintain relational databases are known as a RDBMS. Generally, all RDBMS uses SQL as the language for querying and preserving the database.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Service-Oriented Computing

  Semantics, Processes, Agents

  • Munindar P. Singh, Michael N. Huhns(Authors)
  • 2006(Publication Date)
  • Wiley

    (Publisher)

  At the elementary level for traditional databases, the transaction concept offers the fol- lowing deal to the application programmers. If each programmer individually implements his or her transactions correctly, then the DBMS will ensure that any concurrently executing mix of those transactions will execute correctly. Given some assumptions about system recovery, the DBMS will ensure that the results of committed transactions are durable and the results of uncommitted transactions are not reflected in the database. 11.1.1 ACID Properties Traditional database transactions satisfy the so-called ACID properties [Gray and Reuter, 1993]. A transaction happens entirely or not at all, does not violate consistency constraints, does not expose any partial results, and, if successful, has permanent results. Table 11.1 illustrates these properties. Notice that because of atomicity and isolation, the above examples of Mike’s and Munin- dar’s programs are functionally equivalent. If the deposit and withdrawal operations both succeed or both fail, then their relative order is irrelevant. In general, though, there could be dependencies among the operations because of the flow of information among them or consequences on other decision making by the program logic. For this reason, a transaction is defined with a specific order in mind and the order of operations of a transaction cannot be modified arbitrarily. Transactions are essential whenever we need definite and correct guarantees about access (viewing and modification) of data. But while the traditional transaction properties are desir- able some of the time, they can also be a hindrance at other times. Further, setting up tran- sactions across services is nontrivial, as examined in Chapter 13. In distributed settings, some sort of mutual commit (e.g., two-phase commit) is necessary to prevent violation of the ACID properties.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Introduction to DBMS

  Designing and Implementing Databases from Scratch for Absolute Beginners

  • Dr. Hariram Chavan, Prof. Sana Shaikh(Authors)
  • 2022(Publication Date)
  • BPB Publications

    (Publisher)

  Once a transaction commits, its effects can only be altered by running further transactions. For example, if someone is underpaid, the corrective action is to run another transaction that pays an additional sum. Adjustment of a bad transaction is done via further compensating the transactions.

  Such post facto transactions are called compensating transactions. The transaction concept emerges with the following properties:

  • Atomicity : It either happens or it does not; either all are bound by the contract or none are.
  • Consistency : The transaction must obey legal protocols.
  • Isolation : No interference from concurrently executing transactions.
  • Durability : Once a transaction is committed, it cannot be abrogated.
  • Serializability : Important in a multiuser database and for the concurrent execution of the transactions.

  Transactions can be simple or complex. A simple transaction consists of a linear sequence of operations. A complex transaction is the one where one operation triggers another operation or initiates a transaction depending on the outcome of the previous operations.

  When only one transaction is executed at any instance of time in a database system, serializability and isolation of the database are automatically maintained. This is observed in a single-user database system. In a multi-user database system, since multiple transactions execute concurrently, serializability and isolation must be guaranteed by the implementation of controls. Yet the other ACID properties such as atomicity, consistency, and durability need to be ensured by the database system, be it a single-user or a multi-user. A multi-user database system is specifically required to implement the concurrency control techniques to preserve the ACID properties. Apart from the ACID properties, a database system must manage recovery from the application errors as well as errors introduced by the operating system interruptions and power failure.

  To better understand the transaction concept, let us continue with the example of a movie ticket booking system. A movie ticket booking system consists of multiple theatres, multiple screens, various movies, different movie slots, category-wise seating capacity, pricing, and numerous customers. In order to book a movie ticket for a customer on a particular screen of a theatre for a definite movie slot, a movie ticket booking system consists of a set of transactions. In some cases, after the ticket booking, the customer might be willing to change the ticket to another movie slot rather than cancelling the ticket. In a relational database for the movie ticket booking systems, we refer to these terms as database items for the ease of understanding. These database items can be read or written by a transaction.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  SQL: 1999

  Understanding Relational Language Components

  • Jim Melton, Alan R. Simon(Authors)
  • 2001(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  Transaction Management Introduction We first discussed the concept of transactions in Chapter 2, Getting Started with SQL:1999. In that chapter, we discovered what a transaction was and learned about some of the characteristics of transactions, such as the ACID properties: 9 Atomicity: The all-or-nothing illusion that all operations within a trans- action are performed, or none takes place 9 Consistency: The transaction concept that permits programmers to declare consistency points and validate the correctness of incorporated transfor- mations through application-supplied checks 9 Isolation: The regulation that concurrent transactions have no effect on one another (except in some implementations, where some of the concur- rent transactions may be blocked and therefore delayed or even forced to abort and restart) 9 Durability: The condition that all updates of a transactionmthat is, the new states of all objectsmmust be preserved, even in case of hardware or software failures In this chapter, we discuss how transactions are constructed and managed within SQL. We look at applicable statements, as well as at various phenomena 511 512 Chapter 15 Transaction Management and respective SQL: 1999 isolation levels under which your database transactions may be managed. SQL:1999 Transaction Syntax In SQL-92, there was no explicit statement that starts a transaction as there is in SQL: 1999 and in some database management systems. In SQL, you explicitly end a transaction by executing a COMMIT or ROLLBACK statement (SQL-89 required you to write COMMITWORK and ROLLBACKWORK,b u t SQL-92 made the keyword WORK optional). Transactions are started implicitly whenever you execute a statement that needs the context of a transaction and no transaction is active. For example, a SELECTs t a t e m e n t , an UPDATE statement, and a CREATE TABLE statement all require the context of a transaction.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Wiley Pathways Introduction to Database Management

  • Mark L. Gillenson, Paulraj Ponniah, Alex Kriegel, Boris M. Trukhnov, Allen G. Taylor, Gavin Powell, Frank Miller(Authors)
  • 2012(Publication Date)
  • Wiley

    (Publisher)

  This stands for atomicity, consistency, isolation, and durability. Let’s look at each of these separately. ▲ Atomicity: either all of the statements in the transaction are executed, or none of the statements are executed. An atomic transaction represents a complete unit of work. ▲ Consistency: the transaction preserves database consistency. If a transac- tion executes successfully and performs its database updates properly, the database is transformed from one consistent state to another consistent state. ▲ Isolation: a transaction must appear as though it executed by itself with- out interference from any other concurrent transaction. In practice, how- ever, many concurrent database transactions execute simultaneously. ▲ Durability: changes made to the database must be permanent. Even if and when there are subsequent failures, the effects must persist in the database. One way this is ensured is through the transaction log and the recovery process after a failure, as discussed earlier. Factors relating to isolation deserve a closer look. To ensure complete isola- tion, the DBMS would not be able to process any other transactions while the current transaction is running. This could have disastrous effects on performance while transactions build up in a queue, waiting for other transactions to com- plete. For this reason, operations of one transaction are interleaved with opera- tions of other transactions. However, the database system must guarantee the effects of database updates for every pair of concurrent transactions to be as though one transaction executed from beginning to end and then the second started, or vice versa. Transactions must execute independently, and intermedi- ary or partial results of incomplete transactions must be transparent to other transactions. 10.1.4 Understanding Transaction Scope Transaction scope support and scope options are somewhat DBMS-specific.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Essentials of Microservices Architecture

  Paradigms, Applications, and Techniques

  • Chellammal Surianarayanan, Gopinath Ganapathy, Raj Pethuru(Authors)
  • 2019(Publication Date)
  • Taylor & Francis

    (Publisher)

  Hence, in order to maintain the accuracy and logical correctness and consistency of the database, transactions should occur serially. Database management systems typically use a concurrency control mechanism to implement serialization. Each transaction is assigned a unique timestamp value when it starts. So, the database operations can be ordered according to the timestamp. Isolation —This property emphasizes that each transaction should occur in isolation. This means that when a transaction is in progress, the other transactions cannot see the intermediate results of the ongoing transaction. The locking mechanism is used to implement isolation. When a resource is accessed by a transaction, it is locked. If any other transaction needs to access the resource which is already locked, the transaction manager has to check the type of lock (read lock and write lock) and type of access (read or write) and accordingly it will take the decision to release the resource. Durability —Once a transaction completes successfully, the changes it has made into the database should be made permanent even if there is a system failure. The recovery-management component of database systems ensure the durability of the transaction. Transaction logs (which are flat files) can be used to rectify errors due to hardware failures. That is, a database management system reviews the transaction logs for any uncommitted transactions, and it rolls back uncommitted transactions. Database Transactions in a Single Server When the database is deployed in a single machine, i.e., with centralized databases, implementing a transaction is simple

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Introduction to Database and Knowledge-Base Systems

  • S Krishna(Author)
  • 1992(Publication Date)
  • WSPC

    (Publisher)

  We require that a transaction represent a correct program. That is, if the database state was correct before commencement of the transaction and the transaction executes in isolation and completes its operations, it will leave the system again in a correct state. During intermediate stages of a transaction, this correctness may not be maintained. We may therefore define a transaction as follows: Definition: A transaction is an execution of a series of operations which should be successfully completed in its entirety to preserve correctness of data in a database. For example, consider a transaction in a banking database which trans-fers a certain amount of money from one account, a savings account, to another, a current (or checking) account. The constraint here will be that the total sum in the two accounts should remain the same, unchanged by the transaction. The sequence of operations of the transaction TRANSFER could be: Transaction TRANSFER (Amount) Read Savings_balance Subtract Amount from Savings_balance Write Savings_balance Read Current_balance Add Amount to Current_balance Write Current_balance It can easily be seen that when the operations of the above transaction are carried out in sequence, at intermediate stages, for instance when opera-tions on the savings account are done but those on the current account have not yet been performed, the constraint stated earlier does not hold. We cannot therefore insist that the database should remain in a correct state after each basic operation but can insist on such a condition for a complete transaction. The system, then, should be able to easily recognize the commencement and end of transactions so as to coordinate its own synchronization, 8.2 Integrity Control 199 integrity check and related funcdons.

  Sign up to read

  Learn more about book