Database knowledge of the business

Business (Transaction), generally refers to the things to do or do. In computer terminology, it refers to a program execution unit (unit) that accesses and possibly updates various data items in a database. In computer terminology, transactions are usually referred to as database transactions.

Concept

A database transaction typically contains a sequence of operations that read or write to the database. Its existence consists of the following two purposes:

1. Provides a way for database operations to recover from a failure to a normal state, while providing a way for the database to remain consistent even in an abnormal state. 2. When multiple applications access the database concurrently, an isolation method can be provided between these applications to prevent interference with each other's operations.

When a transaction is submitted to the DBMS (database management system), the DBMS needs to ensure that all operations in the transaction are completed successfully and that the results are persisted in the database, and that if none of the operations in the transaction are completed successfully, all operations in the transaction need to be rolled back to the state before the transaction is executed (or fully executed). , or none), while the transaction has no effect on the execution of the database or other transactions, and all transactions appear to be running independently.

But in reality, the risk of failure is high. During the execution of a database transaction, it is possible to encounter a transaction failure, a database system/operating system failure, or even a failure of the storage media. This requires the DBMS to perform a recovery operation on a failed transaction, restoring its database state to a consistent state (the consistent state of the data is guaranteed). To achieve the ability to restore the database state to a consistent state, the DBMS typically needs to maintain the transaction log to track all operations in the transaction that affect database data.

features

A transaction should have 4 properties: atomicity, consistency, isolation, persistence. These four properties are often called acid properties.

• atomicity: All operations in a transaction (transaction) are either complete or not complete and do not end up in the middle of a link. When an error occurs during execution, the transaction is rolled back (Rollback) to the state before the transaction begins, as if the transaction had never been executed.

• consistency: the integrity of the database has not been compromised until the transaction begins and after the transaction has ended. This means that the data being written must fully conform to all of the preset rules, which include the accuracy of the data, the concatenation, and the subsequent database's ability to perform the scheduled work spontaneously.

• isolation: the ability of a database to read and write and modify its data at the same time for multiple concurrent transactions, which prevents inconsistencies in data resulting from cross-execution when multiple transactions are executing concurrently. Transaction isolation is divided into different levels, including read UNCOMMITTED, Read Committed, REPEATABLE READ (repeatable Read), and serialization (Serializable).

• persistence: after transaction processing, modifications to the data are permanent, even if system failures are not lost.

Example

An example of how to use a common "a account to remit money to a B account" shows how to guarantee the accuracy and completeness of data through database transactions. Familiarity with relational database transactions requires 6 actions from account A to account B:

1. Read the balance from the A account (500).

2, to a account to do subtraction operation (500-100).

3. Write the results back to the A account (400).

4. Read the balance from the B account (500).

5, to the B account to do the addition operation (500+100).

　　6. Write the results back to the B account (600).

Atomic nature:

Ensure that 1-6 of all processes are either executed or not executed. Once a problem occurs during the execution of a step, a rollback operation is required. If the B account is suddenly unavailable (such as being logged out) at the fifth step, all previous operations should be rolled back to the state before the transaction was executed.

Consistency

Before the transfer, the accounts of A and B have 500+500=1000 dollars. After the transfer, the accounts of A and B have 400+600=1000 yuan. That is, the state of the data changes from one state to another after the transaction operation has been performed. At the same time, consistency can guarantee that the account balance will not become negative.

Isolation of

In the whole process of a to B transfer, as long as the transaction has not been submitted (commit), query A and B accounts, the amount of money in two accounts will not change. If a transfer to B at the same time, there is another transaction executed C to B transfer operations, then when the two transactions are finished, b account of the money should be a to B money plus C to B money plus their original money.

Durability

Once the transfer is successful (transaction submission), the money inside the two accounts will change really (the data will be written to the database for persistent saving)!

Atomicity and Isolation lines

Consistency and atomicity are closely related, atomic destruction can lead to inconsistent database, the consistency of data is not all related to atomicity. For example, in the fifth step, the addition of the B account is only added 50 yuan. The process can be atomic, but there is a problem with data consistency.

Therefore, the atomicity and consistency of the transaction are indispensable.

Dirty Read

Dirty reading means that when a transaction is accessing the data and the data has been modified, and the modification has not yet been committed to the database, another transaction accesses the data and then uses that data.

e.g.? The original salary of 1.Mary was 1000, and the finance staff changed Mary's salary to 8000 (but not the transaction). 2.Mary read their own wages and found their wages changed to 8000, a rapturous! ? 3. While the financial discovery was wrong, and the transaction was rolled back, Mary's salary was changed to $.like this, Mary's salary of 8000 is a dirty data.

non-repeatable read

is to read the same data multiple times within a transaction. When this transaction is not finished, another transaction accesses the same data. Then, between the two read data in the first transaction, the data that the first transaction two reads may be different because of the modification of the second transaction. This way, the data that is read two times within a transaction is not the same, so it is called non-repeatable read. ? e.g.? 1. In transaction 1, Mary reads her own salary of 1000, and the operation is not completed ? 2. In transaction 2, the Financial officer modified Mary's salary to 2000 and submitted the transaction. ? 3. In transaction 1, when Mary reads her salary again, the salary becomes

WORKAROUND: You can avoid this problem if you can only read the data after the transaction has been fully committed.

Phantom Reading

A phenomenon that occurs when a transaction is not executed independently, such as when the first transaction modifies data in a table that involves all rows of data in the table. At the same time, the second transaction modifies the data in the table by inserting a new row of data into the table. Then the user who will be working on the first transaction in the future finds that there are no modified rows of data in the table, as if the illusion had occurred.

e.g. currently employs 10 people with a salary of 1000. 1. Transaction 1, read all employees with a salary of 1000. 2. At this point, transaction 2 inserts an employee record into the employee table, and the salary is 3. Transaction 1 reads all employees with a salary of 1000 to a total of 11 records, Workaround: If the operation transaction completes the data processing, No other transaction can add new data, you can avoid the problem

the key to non-repeatable reading is to modify :

The same condition that you read the data, read it again and find that the value is different

The focus of Phantom reading is to add or delete

The same conditions, the 1th and 2nd readings of the number of records are not the same

JDBC Transaction ISOLATION level

Ansi/iso SQL defines four standard isolation levels, from high to the bottom: Serializable (Serializable), REPEATABLE READ (repeatable reads), commit read (Read committed), uncommitted read (read UNCOMMITTED).

UNCOMMITTED read (READ UNCOMMITTED): uncommitted read (READ UNCOMMITTED) is the lowest isolation level. At this level of transaction isolation, one transaction can read data that is not committed by another transaction. non-committed reads can cause dirty reads

Commit Read (Read Committed): Read Committed can also be translated into Read Committed, in a transaction to modify the data process, if the transaction has not been committed, other transactions can not read the data. Read-Committed this isolation level guarantees that any data read is a committed data and avoids dirty reads (dirty reads). But it is not guaranteed to read the same data when the transaction is re-read, because other transactions can modify the data just read after each time the data is read. so the reading cannot solve the read phenomenon of non-repeatable reading.

REPEATABLE READ (repeatable reads): REPEATABLE READ (repeatable reads), because the read-through isolation level produces non-repeatable reads. Therefore, the problem of non-repeatable reads can be resolved by a higher level of isolation than the submission read. This isolation level is called repeatable reading. The repeatable read isolation level resolves non-repeatable read behavior. but it can be read repeatedly in this isolation level, but it can't solve the phantom read

Serializable (Serializable): serializable (Serializable) is the highest isolation level, which is not resolved by all the isolation levels mentioned earlier, and can be resolved in the Serializable isolation level. Although serializable solves the phenomenon of dirty reading, non-repeatable reading, and Phantom reading. However, serializing a transaction has the following effects:

1. Cannot read records that have been modified but not committed by another transaction.

2. Other transactions cannot modify records that have been read by the current transaction until the current transaction is complete.

3. The index key value of a new record inserted by another firm before the current transaction is completed cannot be in the index key range read by any statement of the current transaction.

Database knowledge of the business