Database must have four features 1: atomicity: All operations that the transaction contains are either all successful or fail back, the success must be fully applied to the database, and the failure cannot have an impact on the database; 2: Consistency: The transaction must be in a consistent state before and after execution. The sum of User A and User B is 5000; Regardless of how the AB users convert from one to the other, the sum of two users after the end of the transaction is 5000, which is the consistency of the transaction. 3: Isolation: When multiple users access the database concurrently, the database is opened for each user transactions, not by the operation of other transactions interference, multiple concurrent transactions to be isolated from each other; 4: Persistence: Once a transaction is committed, changes to the data in the database are permanent, The operation of things is not lost even in the event of a failure of the database system. Isolation: When multiple threads open transactions to manipulate data in the database, the database system is quarantined to ensure the accuracy of each thread's access to the data. There are several problems that can arise without considering the isolation of transactions: 01: Dirty reads refers to the data in one transaction that reads another uncommitted transaction, and then uses this data; For example: User A transfers 100 yuan to User B, a informs B to view the account, B does arrive before the discovery, And then, regardless of whether the second SQL executes, as long as the transaction is committed, all operations are rolled back, and when B views the account again, it is not actually credited. 02: Non-repeatable READ non-repeatable read refers to a transaction, read the same data multiple times, the transaction has not ended, another transaction also accesses the same data, but because of the modification of the second transaction, the first transaction two reads the data may be different, so called non-repeatable read , that is, the original data read in the same transaction is not repeatable. Note: The difference between non-repeatable reads and dirty reads is that a transaction reads dirty data that is not committed by another transaction; Non-repeatable reads are read from the previous transaction: a phenomenon that occurs when a transaction is not executed independently, such as when the first transaction modifies data in a table, which involves all rows of data in the table, and the second transaction modifies the table data, which is the addition/deletion of a piece of data to the table. Then the user who operates the first transaction discovers that the data in the table has not been modified, as if the illusion had occurred, and this is where the phantom read occurred. Note: Both Phantom and non-repeatable reads read another transaction that has already been committed, and the difference is that the non-repeatable read query is the same data item, and the Phantom Read is for a whole batch of data. Four isolation levels provided by the database: 01:read uncommitted (READ UNCOMMITTED): lowest level, any situation will occur. 02:read Committed (Read Committed): Can avoid the occurrence of dirty reading. 03:repeatable Read (Repeatable Read): Can avoid dirty read, non-repeatable read occurrence。 04:serializable (serialization): Avoid dirty reading, non-repeatable reading, the occurrence of phantom reading. Note: Four isolation levels highest: seralizable level, the lowest is the read uncommitted level; the higher the level, the lower the execution efficiency; the isolation level setting is only valid for the current link, and for the JDBC Operations database, a Connection object is equivalent to a link , only the isolation level set on the connection object is valid for that connection object, regardless of other linked connection objects. The default isolation level for 01:MYSQL is: Repeatable READ: Repeatable read;02:oracle database, only seralizable (serialization) level and Read Committed () are supported; The default is read Committed level The following four isolation levels for the scene design: 01:read Uncommitted read not submitted, the company paid, the leader of the 5000 yuan to the Singo account, but the transaction did not submit, and Singo just to check the account, found that the wages have arrived, is 5000 yuan the whole, Very happy. Unfortunately, the leadership found that the amount of wages issued to Singo is not correct, is 2000 yuan, and then quickly rolled back to business, modify the amount, the transaction will be submitted, and finally singo the actual salary of only 2000 yuan, Singo empty joy a game. 02:read committed read has been submitted; Singo take the payroll card to spend, the system read to the Cary really has 2000 yuan, and at this time her wife also just in the online transfer, the Singo Pay card of 2000 yuan transferred to another account, and before the Singo submitted a business, When the Singo deduction, the system checks to Singo's payroll card has no money, the deduction failed, Singo very puzzled, clearly card money, why ... 03:repeatable read repeat reading when Singo took the payroll card to spend, once the system began to read the Payroll card information (that is, the start of the transaction), Singo's wife could not change the record, that is, Singo wife can not be transferred at this time. Repeat reading may appear phantom reads: Singo's wife works in the banking department, and she often views Singo's credit card consumption records through the internal banking system. One day, she was inquiring into the total consumption amount of credit card in Singo month (select SUM (amount) from transaction where month = this month) was $80, and Singo at this time was good to eat outside the sea plug at the cashier to pay, spend 1000 yuan , which adds a $1000 consumption record (insert transaction ... ）, and submitted a transaction, then Singo wife will Singo month credit card consumption details printed to A4 paper, but found that the total consumption of 1080 yuan, Singo wife is very surprised, thought there was an illusion, the illusion of this produced. Serializabale: Highest transaction isolation level, cost-effective, low-performance, rarely used, at this level, transaction order execution, avoiding the above-mentioned situation.
Four characteristics of the database, and the isolation level of the transaction
