Database transaction

Database transaction (a)

Summary

  Database transaction (Database Transaction), is defined as a series of operation of a single logical unit of work performed. Transaction processing can be ensured unless all the transaction unit are completed successfully, otherwise not permanently updated data oriented resources. By combining a set of related operations combined into one or all successful either all the failed unit, can simplify error recovery and make the application more reliable. A logical unit of work to become a business, must satisfy the so-called ACID (atomicity, consistency, isolation and durability) properties.

Operation process

  A trading plan of online shopping, the payment process includes at least the following steps of database operations:
  · Update customer purchased goods inventory information
  · Save customer payment information -- may include interaction with the banking system
  · To generate orders and saved to the database
  · Update the relevant information to the user, such as shopping quantity etc.
  Under normal circumstances, these operations will go smoothly, the ultimate success of the transaction, all database information related to the transaction was successfully updated. However, if in this series process, any link error, for example in the updated inventory information when abnormality occurs, the customer bank account deposit insufficiency and so on, will cause the transaction failure. Once the transaction fails, all the information in the database must remain before the transaction state unchanged, as the last step the user information update failed and led to the failure of the transaction, you must ensure that the failure of the transaction does not affect the state -- inventory information database has not been updated, the user does not pay, no production orders. Otherwise, the database information would be chaotic and unpredictable.
  A database transaction is used to ensure that transactions under the stability and predictability of Technology.

The ACID property database transactions

Atomicity(atomic)
  Transactions must be atomic unit of work; for the modification of data, or all, or are not executed. Usually, and a transaction operations associated with the common goal, and are dependent on each other. If a sub system only performs these operations set, the overall goal may be destroyed affairs. Atomic eliminates the possibility of system operation subset.
Consistency(consistent)
  Transaction when completed, must make all data in a consistent state. In a relational database, all the rules must be used in transactions, in order to maintain the integrity of all data. At the end of the transaction, all internal data structures (such as B tree index or two-way linked list) must be correct. Some consistency maintenance responsibility borne by the application developer, integrity constraints, they must ensure that the application has mandated that all known. For example, when the application for the transfer, should avoid any moving decimal point in the transfer process.
Isolation(insulation)
  Modify the isolation by concurrent firm as modifications must be made with any other concurrent transactions. State affairs to view the data in the, either before another concurrent transaction modify its state, or another transaction modifies its state, intermediate state data transaction does not see. This is called serializability, because it can reload the initial data, and replay a series of transactions, so that the data at the end of the state and the original transaction execution state of the same. When a transaction isolation level serializable will receive the highest. At this level, obtained from a group can be executed in parallel transaction results and by continuous operation of each firm results obtained with the same. Due to the high isolation may limit the number of concurrent execution of transactions, so some applications to reduce the isolation level in exchange for greater throughput. To prevent data loss
Persistence(durability)
  After a transaction has completed, it is permanent for system effects. The modified even fatal failure of the system will stay.

Database transaction (two)

Data concurrency problems

A database may have multiple access to the client, the client can concurrent access to databases. The same data in the database can also be multiple transactional access, if not take the necessary measures, will lead to various problems, damage the integrity of the data. These problems can be classified into 5 types, including 3 types of data read the questions (dirty reads, phantom read and not repeatable read) update problem and 2 types of data (the first lost updates and second lost updates). Here, we explain the problems caused by the scene through the example.

Dirty reads(dirty read)
In explaining the dirty read before, we first tell a joke: a stammer person in the beverage shop counter around, the boss is very enthusiastically welcome: “ drink a bottle? ”, stammer hurriedly said: “ I drink … … &hellip ” drink;, the boss quickly open the cans to stutter, stuttering finally broke out of his words: &ldquo … I drink drink; … … drink not ah!”. In this case, drink shop owner on the dirty read on.
Data changes A transaction reads the B has not yet been submitted, and operations on this data based on. If you happen to B transaction rollback transaction, then A read data is not recognized. Cause of withdrawal transaction and transfer transaction concurrency when dirty read scene:
    

Time
Transfer transaction A
Cash transaction B
T1
Begin transaction
T2
Begin transaction
T3
    
Checking account balance is 1000 yuan   
T4
       
Take out the balance of 500 yuan to 500 yuan
T5
Checking account balance is 500Yuan (dirty read)
T6
Undo transactions balances back to 1000 yuan
T7
Into the 100 yuan to 600 yuan to the balance
T8
Commit transaction

  In this scenario, B hopes to cash 500 yuan and then withdrawing the action, and A to the same account transfer 100 yuan, because A transaction reads the B transaction uncommitted data, resulting in account nothing lost 500 yuan.

Unrepeatable read(unrepeatable read)
Unrepeatable read is to change the data refers to the A transaction reads the B transaction has been submitted. Suppose A in cash transaction, B to the account transfer 100 yuan, A two read the account balance inconsistent:

Time
Cash transaction A
Transfer transaction B
T1
Begin transaction
T2
Begin transaction
                         
T3
                             
Checking account balance is 1000 yuan    
T4
Checking account balance is 1000 yuan
                         
T5
                 
Take out the balance of 100 yuan to 900 yuan
T6
Commit transaction                 
T7
Checking account balance is 900Element (and T4Read inconsistent)

  In the same transaction, T4 time and T7 time read the account balance is not the same.
Phantom read(phantom read)
The new data of A transaction reads B transactions, then the A transaction will appear phantom read problem. Phantom read generally occurs in the calculation of statistical data of transaction, as an example, assume that the banking system in the same transaction, the total amount of the two statistical account, in the two statistical process, just add a deposit account, and deposit 100 yuan, at this time, two times the total the amount will be inconsistent:

Time
The amount of statistics transaction A
Transfer transaction B
T1
Begin transaction
T2
Begin transaction
            
T3
Total deposit amount is 10000 yuan
            
T4
Add an account, deposit of 100 yuan
T5
Commit transaction    
T6
Once again the total deposit number is 10100Yuan (phantom read)

  If new data just meet transaction query condition, the new data into the affairs of the field, resulting in a two statistical discrepancy.
  Phantom read and not repeatable read is two easily confused concepts, the former refers to read new data of other already submitted the affairs, while the latter refers to read change data has been submitted Affairs (change or delete), in order to avoid these two kinds of circumstances, the countermeasure is not the same, prevent change the data read operation, only need to add data row level locks, stop the operation of the data changes, and prevent read to add new data, often need to add a table level lock — — the whole table lock, prevent new data.

The first kind of lost updates
The A transaction cancellation, to update the data has been submitted to the B transaction coverage. This error can be caused by a very serious problem, through the following account money transfer can be seen:
   

Time
Cash transaction A
Transfer transaction B
T1
Begin transaction
T2
Begin transaction
T3
Checking account balance is 1000 yuan   
T4
Checking account balance is 1000 yuan
T5
Into the 100 yuan to 1100 yuan to the balance
T6
Commit transaction
T7
Take out the balance of 100 yuan to 900 yuan
T8
Undo transactions
T9
Balance recovery was 1000Yuan (lost update)


  A transaction on the cancellation, “ careless ” B transactions have to account to erase.
Second lost updates
  The A transaction covering B transactions have submitted data, resulting in B firms operating loss:

Time
Transfer transaction A
Cash transaction B
T1
Begin transaction
T2
Begin transaction
                        
T3
              
Checking account balance is 1000 yuan   
T4
Checking account balance is 1000 yuan
                        
T5
Take out the balance of 100 yuan to 900 yuan
T6
Commit transaction          
T7
To 100 yuan
T8
Commit transaction
T9
The balance is 1100Yuan (lost update)


The above example for checking transfers affairs covering the withdrawal transaction deposit balance updates, causes the bank finally lost 100 yuan, if the transfer transaction submit, then the user account will lose 100 yuan.

Database transaction (three)

Transaction isolation level
Although the database provides a DML mode of operation for the user, but directly use lock management is very troublesome, so the database to provide users with automatic locking mechanism. As long as the user specifies the transaction isolation level session, database analysis of SQL statement in the transaction, and then automatically for business operation data resources add suitable lock. In addition, the database will also maintain the lock when the lock, a number of resources too much, automatic lock upgrades to improve system performance, and this process is completely transparent to the user.
ANSI/ISO SQL 92 standard defines the transaction isolation level 4 grades, the same data environment, using the same input, perform the same work, according to the different isolation levels, can lead to different results. Ability of different data concurrency transaction isolation level can be resolved is different.
       Table 1 transaction isolation levels of concurrency problems

Isolation level
Dirty reads
Must not
Repeatable read
Phantom read
The first kind of lost updates
Second lost updates
READ UNCOMMITED
Allow
Allow
Allow
Not allowed
Allow
READ COMMITTED
Not allowed
Allow
Allow
Not allowed
Allow
REPEATABLE READ
Not allowed
Not allowed
Allow
Not allowed
Not allowed
SERIALIZABLE
Not allowed
Not allowed
Not allowed
Not allowed
Not allowed


The transaction isolation level and database concurrency is opposite, both the increase for long. In general, the use of READ UNCOMMITED isolation level database has the highest concurrency and throughput, and uses the SERIALIZABLE isolation level database concurrency minimum.

SQL 92 READ UNCOMMITED is mainly to provide the ability to non blocking read, although Oracle also support READ UNCOMMITED, but it does not support the dirty reads, because Oracle uses multi version mechanism completely solve the read dirty data problem and ensure the consistency, read in the non blocking read so, level READ COMMITTED isolate Oracle it has met the READ isolation level REPEATABLE SQL 92 standard.

SQL 92 is recommended to use the REPEATABLE READ to read data consistency, but the user can select the appropriate isolation level according to the application requirements.

Posted by Cynthia at November 23, 2013 - 6:44 AM