Database-Transactions and locks

Transaction

The so-called transaction is a user-defined series of database operations, which are either fully executed or not executed at all, and are an inseparable unit of work. For example, in a relational database, a transaction can be an SQL statement, a set of SQL statements, or an entire program.

Give a chestnut:

Small it is shopping online and its payment process includes at least the following steps of database operations:

1. Update the inventory information of the products purchased by customers;
2. Generate orders and save to database;
3. Update user-related information, such as the number of shopping;

Normally, the operation runs smoothly and the final transaction is successful, and all database information related to the transaction is updated successfully. However, if there is a mistake in any part of the process, such as an exception when updating commodity inventory information, the customer's bank account is under-deposited, and so on, will cause the transaction to fail. Once the transaction fails, all the information in the database must remain unchanged before the transaction, such as failure to update the user information in the last step, resulting in the failure of the transaction, then it must be ensured that the failed transaction does not affect the state of the database-the inventory information has not been updated, the user has not paid, and the order has not been generated. Otherwise, the database information will be chaotic and unpredictable.

Database transactions are the technology used to ensure the smoothness and predictability of transactions in this situation.

ACID properties of a transaction a (atomicity) atomicity

The transaction must be an atomic unit of work, either all executed or not executed for its data modification. Typically, the operations associated with a transaction have a common purpose and are interdependent. If the system only performs a subset of these operations, it may break the overall goal of the transaction. Atomicity eliminates the possibility of a system processing a subset of operations.

C (consistency) Consistency

When a transaction is complete, you must keep all data in a consistent state. In a related database, all rules must be applied to transaction modifications to maintain the integrity of all data. At the end of the transaction, all internal data structures, such as B-tree indexes or doubly linked lists, must be correct. Some of the responsibility for maintaining consistency is borne by application developers who must ensure that all known integrity constraints are enforced by the application. For example, when developing an application for transfer, avoid arbitrarily moving the decimal point during the transfer process.

I (Isolation) Isolation

In a concurrency environment, when different transactions manipulate the same data at the same time, each transaction has its own full data space. Modifications made by a concurrent transaction must be isolated from modifications made by any other concurrent transaction. When a transaction views the data update, the data is in the state it was in before the other transaction modifies it, or the state after the other transaction modifies it, and the transaction does not view the data in the middle State .

D (Durability) Persistence

This means that as long as the transaction completes successfully, the updates it makes to the database must be persisted. Even if a system crash occurs, the database can be restored to the state at the end of the transaction when the database system is restarted.

The Acid property of a transaction is implemented by a relational database management system (RDBMS, database System). The database management system uses logs to ensure the atomicity, consistency and persistence of transactions. The log records the updates that the transaction makes to the database, and if a transaction has an error during execution, it can undo the updates that the transaction has made to the database based on the log, returning the database to the initial state before the transaction was executed. The database management system adopts the lock mechanism to realize the isolation of the transaction. When multiple transactions update the same data in the database at the same time, only the transaction that holds the lock is allowed to update the data, and the other transaction must wait until the previous transaction releases the lock, and the other transaction has the opportunity to update the data.

A complete transaction structure

begin A transaction;// set the starting point of a transaction commit a transaction;// commit the transaction so that the data committed by the transaction becomes a persistent immutable part ROLLBACK a transaction;// revokes a transaction, rolls back, and makes it the state before the transaction begins save a transaction; Create a label that is used as a partial rollback to restore the status to the beginning of the label

Syntax of a transaction

BEGIN Tran[saction] [<transaction_name>|< @transaction variable>][ with mark['< description>']][;] COMMIT [tran[saction][<transaction_name>|< @transaction varible>]][;] ROLLBACK Tran[sacrion][<transaction name>|<save Point name>|< @transaction varible>|< @save Point varible >] [;] SAVE tran[saction][<save point name>|< @svae point varible>][;]

"[]" is the part that needs to be added.

Give a chestnut:

For the entire example below, first build a table (using SQL Server) as follows:

　　

BEGIN TRANTran_money--Start a transactionDECLARE @tran_error int;SET @tran_error = 0; BEGINTRYUPDATETb_moneySETMymoney=Mymoney-  - WHEREName= 'Liu Bei'; SET @tran_error = @tran_error + @ @ERROR; --test the error code to see if Liu Bei's money is reduced and whether Guan Yu's money will increase        --SET @tran_error = 1;        UPDATETb_moneySETMymoney=Mymoney+  - WHEREName= 'Guan Yu'; SET @tran_error = @tran_error + @ @ERROR; ENDTRYBEGINCATCHPRINT 'An exception occurred, error number:' + Convert(varchar, Error_number ())+ ', error message:' +error_message ()SET @tran_error = @tran_error + 1ENDCATCHIF(@tran_error > 0)    BEGIN        --performing an error, rolling back a transaction        ROLLBACK TRAN; PRINT 'transfer failed, cancel transaction!'; ENDELSE    BEGIN        --no exception, COMMIT transaction        COMMIT TRAN; PRINT 'Transfer Success!'; END

This chestnut originates from SQL Server transaction syntax

Lock

The database, like the operating system, is a shared resource used by multiple users. When multiple users access data concurrently, in the database, multiple transactions are generated concurrently to access the same data. If the concurrency operation is not controlled, it is possible to read and store incorrect data and compromise the consistency of the database. Locking is a very important technology to realize concurrency control of database. In practical applications often encounter lock-related anomalies, when two transactions require a set of conflicting locks, and cannot continue the transaction, there will be a deadlock, serious impact on the normal execution of the application.
There are two basic types of locks in the database: Exclusive lock (Exclusive Locks, X Lock) and shared lock (Share Locks, S lock). When a data object is added to an exclusive lock, other transactions cannot read and modify it. Data objects with shared locks can be read by other transactions, but cannot be modified. The database uses these two basic types of locks to control the concurrency of a database's transactions.

Several cases of deadlock

The first case of deadlock
A User A accesses table A (locks up table A), then accesses table B, another user B accesses table B (Locks table B), and then attempts to access table A; User A because User B has locked table B, it must wait for User B to release table B to continue, and User B waits for user A to release table A to continue. This is where the deadlock occurs.

Workaround:
This kind of deadlock is more common, is due to the bug of the program, in addition to adjusting the logic of the program there is no other way. Careful analysis of the logic of the program, for the database of multi-table operations, as far as possible in the same order to deal with, try to avoid locking two resources at the same time, such as the operation of A and B tables, always according to the order of a after B, must lock two resources at the same time, to ensure that at any time should be in the

Deadlock in the second case  
User A queries a record and then modifies the record, at which time User B modifies the record, at which point the nature of the lock in User A's transaction is raised to an exclusive lock by the shared lock of the query, and the exclusive lock in User B is due to a There is a shared lock that must wait for a to release the shared lock, and a lock that cannot be raised due to the exclusive lock of B cannot release the shared lock, and a deadlock occurs. This kind of deadlock is more subtle, but it often happens in slightly larger projects. In the case of a project, the button clicked on the page, did not make the button immediately invalid, so that the user will quickly click the same button multiple times, so that the same piece of code on the same record of the database multiple operations, it is easy to see this deadlock situation.  

Workaround:  
1, for the button and other controls, click to invalidate it immediately, do not allow users to repeat the click, to avoid the same record operation.  
2. Use optimistic locking for control. Optimistic locks are mostly implemented based on the data version (versions) recording mechanism. is to add a version identity to the data, which is typically done by adding a "version" field to the database table in the version solution based on the database table. When the data is read, the version number is read together, and then the version number is added one after the update. At this point, the version data of the submitted data is compared to the current version information of the database table corresponding to the record, and if the submitted version number is greater than the current version number of the database table, it is updated, otherwise it is considered to be outdated data. The optimistic locking mechanism avoids the database lock-up overhead in long transactions (both user A and User B do not locking the database data), which greatly improves the overall performance of the system under large concurrency. Hibernate has built-in optimistic locking implementations in its data access engine. It is important to note that since the optimistic locking mechanism is implemented in our system, user update operations from external systems are not controlled by our system and may result in dirty data being updated into the database.  
3. Use pessimistic locks for control. Pessimistic locks are implemented in most cases by the locking mechanism of the database, such as Oracle's SELECT ... for UPDATE statement, to ensure maximum operation exclusivity. But it comes with a lot of overhead for database performance, especially for long transactions, which are often unsustainable. As a financial system, when an operator reads a user's data and modifies it on the basis of the user's data being read (such as changing the user's account balance), a pessimistic locking mechanism means that the entire operation (from the operator reads the data, starts the modification, and commits the modified result. Even when the operator takes the time to cook the coffee, the database record is always locked, and you can imagine that if you face hundreds of concurrent cases, it will result in catastrophic consequences. Therefore, the use of pessimistic lock control must be considered clearly.  

The third case of deadlock
If an UPDATE statement that does not meet the criteria is executed in the transaction, the full table scan is performed, and the row-level lock is raised to a table-level lock, and after many such transactions are executed, it is easy to create deadlocks and blockages. In a similar situation, when the amount of data in a table is very large and the index is too small or inappropriate, a full table scan often occurs, and eventually the application system slows down and eventually becomes blocked or deadlocked.
Workaround:
The SQL statement does not use a query that is too complex to correlate multiple tables, parses the SQL statement using the execution plan, and optimizes the corresponding indexes for SQL statements with full table scans.

In general, the generation of memory overflow and lock table is due to bad code writing, so improving the quality of the code is the most fundamental solution. Some people think that the implementation of the function first, there is a bug in the testing phase of the correction, this idea is wrong. Just as the quality of a product is determined in the process of manufacturing, rather than in quality testing, the quality of the software has been decided during the design and coding stages, and testing is only a validation of the quality of the software, since it is impossible to find all the bugs in the software.

How to avoid deadlocks

1 when using transactions, try to shorten the logical processing of the transaction, commit or rollback the transaction as soon as possible;
2 Set the deadlock timeout parameter to a reasonable range, such as: 3 minutes-10 minutes; Over time, automatically abandon this operation, to avoid the process of hanging;
3 All SPS must have error handling (via @error)
4 generally do not modify the default level of SQL Server transactions. Forced lock is not recommended
5 Optimize the program, check and avoid the deadlock phenomenon;
1) Arrange table access order reasonably
2) Try to avoid user intervention in the transaction and try to make a transaction less task.
3) The use of dirty reading technology. Dirty reads avoid lock collisions because they do not lock the table being accessed. In a client/server application environment, some transactions often do not allow the reading of dirty data, but under certain conditions, we can use dirty read.
4) Data access time domain Discretization method. The time domain discretization method of data access refers to the use of various control methods in the client/server structure to control the period of access to objects in the database or database. Mainly through the following ways: The reasonable arrangement of the background transaction execution time, the use of workflow to the background transaction management unified. Workflow in the management of tasks, on the one hand limit the number of threads of the same class of tasks (often limited to 1), to prevent excessive consumption of resources; On the other hand, reasonable scheduling of different tasks to execute timing, time, as far as possible to avoid multiple background tasks at the same time, in addition, avoid running background tasks during peak hours of reception
5) data storage space discretization method. The data storage space discretization method refers to the use of various means to spread the data logically in a table into a number of discrete spaces, in order to improve the access performance of the table. Mainly by the following methods: First, the large table by row or column decomposition into a number of small tables; Second, according to different user groups decomposition.
6) Use the lowest possible isolation level. The isolation level is the degree to which multi-user transactions are isolated to ensure the integrity and consistency of database data, SQL92 defines 4 isolation levels: uncommitted read, read-committed, Repeatable read, and serializable. If you choose too high an isolation level, such as serializable, although the system can achieve greater isolation to ensure the integrity and consistency of the data, but the conflict between the transactions and the chance of deadlock greatly increased, greatly affecting the system performance.
7) Use bound Connections. Bound connections allows two or more transactional connections to share transactions and locks, and any one transaction connection to request a lock is like another transaction requesting a lock, so these transactions can be allowed to share data without locking conflicts.
8) Consider using optimistic locking or having the transaction get an exclusive lock first.

Conflict issues

1. Dirty Reading

The data that a transaction reads is data that is being processed by another transaction. Another transaction may be rolled back, causing the data read by the first transaction to be incorrect.

2. Non-repeatable reading

The data is read two times in one transaction, but another transaction has changed the data involved in the first transaction, causing the first transaction to read into the old data.

3. Phantom Reading

Phantom reading refers to a phenomenon that occurs when a transaction is not executed independently. For example, the first transaction modifies the data in a table, which 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.

4, Update lost

When multiple transactions read one data at a time, one transaction successfully processed the data and was written back to the original value by another transaction, resulting in the loss of the first transaction update.

Lock mode

1. Shared lock

A shared lock (S-Lock) allows concurrent transactions to read (SELECT) resources under closed concurrency control. For more information, see Types of concurrency control (pessimistic and optimistic locks). When there is a shared lock (S lock) on the resource, no other transaction can modify the data. As soon as the read operation is complete, the shared lock (S lock) on the resource is freed, unless the transaction isolation level is set to repeatable read or higher, or a shared lock (S lock) is reserved with a lock hint for the duration of the transaction.

2. Update lock (U lock)

Update locks are combined with shared and exclusive locks. An update lock means that when an update is made, a shared lock may be converted to an exclusive lock after the scan has completed qualifying data.

There are two steps in this:

1) When the scan gets the where condition. This section is an update query, and this is an update lock.

2) If a write update is performed. The lock is now upgraded to an exclusive lock. Otherwise, the lock is turned into a shared lock.

Update locks can prevent common deadlocks.

3. Exclusive lock

An exclusive lock (X Lock) prevents concurrent transactions from accessing resources. An exclusive lock is not compatible with any other lock. When an exclusive lock (x Lock) is used, no other transaction can modify the data, and the read operation is performed only when the NOLOCK hint or uncommitted read isolation level is used.

Pessimistic lock

Pessimistic locking refers to the assumption that concurrent update conflicts occur, so the lock mechanism is used regardless of whether the conflict actually occurs.
Pessimistic locks perform the following functions: Lock the Read record and prevent other transactions from reading and updating the records. Other transactions will continue to block until the transaction ends.
Pessimistic lock is the use of database transaction isolation function, based on exclusive access to resources, so as to ensure the consistency of read data to avoid loss of modification.

Pessimistic locks can use the repeatable read transaction, which fully satisfies the pessimistic lock requirements.

Optimistic lock

An optimistic lock does not lock anything, that is, it does not depend on the transaction mechanism of the database, and the optimistic lock is a purely application-level thing.

If you use optimistic locking, the database must have a version field, otherwise you can only compare all fields, but because floating-point types cannot be compared, it is not feasible to actually have a version field.

Transaction ISOLATION Level

There are 4 isolation levels for database transactions, from low to high, READ UNCOMMITTED,Read Committed,Repeatable read, andSerializable, which can be resolved individually by each of the four levels Problems such as dirty reading, non-repetition reading, and Phantom reading.

read uncommitted-not submitted

The READ UNCOMMITTED transaction can read records that have been modified but not committed by the transaction.

The READ UNCOMMITTED transaction generates a dirty read (Dirty read).

The Read uncommitted transaction has the same effect as the SELECT statement plus nolock, which is the least restrictive of all isolation levels.

This chestnut originates from the database transaction isolation level

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 salary has been to the account, is 5000 yuan 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.

The above situation, that is what we call dirty Read, two concurrent transactions, "transaction A: lead to Singo payroll", "Transaction B:singo query Payroll account", transaction B read the transaction A has not yet committed data.

When the isolation level is set to READ UNCOMMITTED, dirty reads can occur and how to avoid dirty reads, see the next isolation level.

Read committed-reading commit

Once the statement that created the shared lock is executed, the lock is released at the top.

Read committed is the default isolation level for SQL Server.

Read Committed can only prevent dirty reads.

--Create the table first:CREATE TABLETB (IDint, Valint) INSERTTbVALUES(1,Ten) INSERTTbVALUES(2, -then in connection 1, execute:SET TRANSACTION Isolation  Level READ COMMITTEDBEGIN TRANSACTION    SELECT *  fromTb--When this select is finished, the shared lock is released          WAITFORDELAY'00:00:05'  --analog transaction, wait 5 seconds          SELECT *  fromTb--Select TB Table againROLLBACK  --rolling back a transactionin connection 2, perform theUPDATETbSETVal=Val+ Ten WHEREId= 2; -------- go back to connection 1. You can see that. Two times the results of the select are different. Because the select is finished under the default Read Committed isolation level. The shared lock will be released immediately.

Singo take the payroll card to spend, the system read to Cary really have 2000 yuan, and at this time her wife also just in the online transfer, the Singo Pay card of 2000 yuan to another account, and before Singo submitted the business, when Singo deduction, System Check to Singo's payroll card has no money, deduction failure, Singo very puzzled, obviously card money, why ...

The above situation, that is what we call non-repeatable read, two concurrent transactions, "transaction A:singo consumption", "Transaction B:singo wife online transfer", transaction A in advance read the data, transaction B immediately updated the data, and committed the transaction, and transaction a read the data again, The data has changed.

When the isolation level is set to Read Committed, dirty reads are avoided, but may cause non-repeatable reads.

The default level for most databases is read committed, such as SQL Server, Oracle. To resolve the issue of non-repeatable reads, see the next isolation level.

Repeatable read-Repeat Read

Repeatable read transactions do not produce dirty reads, and no other transaction can modify records that are currently read by the transaction until the transaction is complete.

Other transactions can still insert new records, but must conform to the search criteria of the current transaction-meaning that a phantom read (Phantom read) is generated when the current transaction re-queries the record.

you can avoid non-repeatable reads when the isolation level is set to repeatable read. 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.

Although repeatable read avoids non-repeatable reads, it is possible to have 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's wife will singo the current 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 such a generation.

Note: The default isolation level for MySQL is repeatable read.

Serialization of serializable-

Serializable can prevent all consistency issues except for update loss, namely:

1. Statements 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.

SNAPSHOT

The records that are read by any statement in the snapshot transaction are data at the start of the transaction.

This is equivalent to when a transaction starts, the database generates a dedicated "snapshot" of the transaction. In the current transaction, you see that no other transaction has been modified by the data after the current transaction has been started.

The snapshot transaction does not require locking when the record is read, the snapshot transaction that reads the record does not lock other transactions to write to the record, and the transaction that writes the record does not lock the snapshot transaction to read the data.

Database-Transactions and locks