Concurrency exception, transaction isolation level, lock

The level at which the transaction is prepared to accept inconsistent data is called the isolation level. The isolation level is the degree to which a transaction must be isolated from other transactions. Low isolation levels can increase concurrency, but the cost is to reduce data correctness. On the contrary, high isolation levels can ensure data correctness, but may have a negative impact on concurrency. The isolation level required by the application determines the locking behavior used:

The following uncertainties may occur when the database is accessed by the majority of customers:

1.Dirty read (dirty reads ):One Transaction starts to read a row of data, but another transaction has updated the data but cannot be committed in time. This is quite dangerous.

Assume that the balance (deposit) data item written by T1 is read before transaction T1 is committed in transaction T2. This problem may occur when the write lock on balance is released before transaction T1 is committed: since the balance read by transaction T2 is a value after the transaction is not committed, this value may not eventually appear in the database.

2.Non-repeatable reads ):A transaction reads data from the same row twice but returns different results. For example, in the middle of two reads, another transaction modifies the row of data and submits the data.

For example, if list is a passenger list, count is the number of passengers in this list.

T1 read list-> T2 read list-> T2 booked a seat-> T2 read count-> T2 Add the Count value to 1-> T2 submit-> T1 read count.

Transaction T1 displays the list before a new passenger joins the new passenger and the count after the new passenger joins the new passenger, and there is an inconsistency problem.

3.Lost updates problem ):The exception cannot be read repeatedly. two concurrent transactions read the same row of data at the same time, and one of them changes and commits it, and the other changes and commits it. This will invalidate the first write operation.

For example: t1 reads balance-t2 reads balance-t2 deposits, and then calculates and updates the balance-t2 submits-T1 deposits. After T1 deposits, the balance-T1 is updated based on the Balance read by T1. submit

In the final balance, the deposit of transaction T2 is not reflected, and the update of transaction T2 is lost.

4.Phantom reads): Also known as phantom ). The transaction performs two queries during the operation. The results of the second query contain data not found in the first query (the same SQL statement is not required here ). This is because another transaction inserts data during the two queries.

ExampleFor example, there are two tables, accounts, where each tuples corresponds to an account in the bank; depositors table, where each tuple corresponds to a depositor. The depositors table has a totalbalance attribute, which refers to the sum of the balances of all accounts of a specific depositor.

There are two transactions accessing this table. For audit transaction T1 corresponding to Mary, the total balances of all accounts of Mary are first queried from the account table.

Select sum (balance) from accounts a where a. Name = 'Mary'

Then, compare the return value of this select statement with the return value of the following SELECT statement:

Select D. totalbalance from depositors d Where D. Name = 'Mary'

At the same time, there is also a concurrent transaction T2, which creates a new account for Mary. It creates a new account with an initial balance of 100 for Mary and adds a tuple to the accounts table using the following statement:

Insert into accounts values ('20170101', 'Mary ', 10021)

Then, use the following statement to update the appropriate tuples in the depositors table and add the totalbalance value of Mary to 100.

Update depositors set totalbalance = totalbalance + 100 Where name = 'Mary'

Assume that transaction T1 obtains the lock on the tuples of the Mary account in the accounts table when executing the first select statement, but this does not prevent transaction T2 from inserting a brand new tuple into the table. Therefore, the triples cannot be bound to generate serializable scheduling. Then, you can generate a schedule that is not serializable because it fully controls the access to the table.

The root cause of this problem is that transaction T1 deems that it has locked all the tuples with name = 'Mary ', but transaction T2 modifies the set of tuples corresponding to name = 'Mary. This new tuple is calledPhantom.Phantom may generate non-serializable scheduling, and thus may generate incorrect results.

Four transaction isolation levels are defined in the standard SQL specification to avoid the preceding situations. Different isolation levels process transactions differently.

1. Read uncommitted ):Dirty reading is allowed. If a transaction has started to write data, the write operation on the other data is not allowed at the same time, but other transactions are allowed to read this row of data. This isolation level can be achieved through the exclusive write lock. The lowest level of transaction isolation, only to ensure that the physical damage data is not read. In contrast to the Read committed isolation level, it allows reading data that has been modified by other users but has not yet been submitted for determination.

2. Read committed ):Repeated read is allowed, but dirty read is not allowed. This can be achieved through "instant shared read lock" and "exclusive write lock". Transactions that read data allow other transactions to continue to access this row of data, but transactions that are not committed to write data will prohibit other transactions from accessing this row. The default SQL server level. At this isolation level, the SELECT command does not return data that has not been committed or dirty data.

3. Repeatable read ):Do not allow repeated reads or dirty reads. However, phantom data may sometimes appear, which can be achieved through the "shared read lock" and "exclusive write lock". Reading data transactions will prohibit write transactions (but allow read transactions ), writing a transaction prohibits any other transactions. At this isolation level, the data read by the SELECT command is not changed throughout the command execution process. This option affects the system performance. It is best not to use this isolation level unless necessary.

4. serializable ):Strict transaction isolation is provided, which requires transaction serialization and execution. transactions can only be executed one by one, but cannot be executed concurrently. If transaction serialization is not possible only through the "Row-Level Lock", other mechanisms must be used to ensure that the newly inserted data is not accessed by the transaction that just executes the query operation. The highest level of transaction isolation, full isolation between transactions. If the transaction runs at the serializable read isolation level, it can ensure that any concurrent overlapping transactions are serialized.

At the isolation level, you need to use the set command to set its syntax as follows:
SET transaction isolation level
{Read committed
| Read uncommitted
| Repeatable read
| Serializable}
The following are four isolation levels that allow different types of behavior.
Isolation level dirty reads cannot read phantom repeatedly)
Read not submitted is yes
Read committed? Yes
Repeatable read? No Yes
Serializable no

The higher the isolation level, the more data integrity and consistency can be guaranteed, but the greater the impact on concurrency performance. For most applications, you can set the database system isolation level to read committed, which can avoid dirty reads and has good concurrency performance. Although it may cause non-repeated read, virtual read, and second-type update loss concurrency problems, in some scenarios where such problems may occur, it can be controlled by applications using pessimistic or optimistic locks.

A lock is a restriction on resource access in a multi-user environment. After locking a data source, the data source has certain access restrictions. The data source is "locked ". In SQL Server, you can lock the following objects:
Row: a single row of data on the data page;
Index row (key): The data of a single row on the index page, that is, the key value of the index;
Page: a page is the basic unit for SQL Server to access data. Its size is 8 KB;
Disk Area (extent): A disk area consists of eight consecutive pages;
Table );
Database ).

In SQL Server, there are two types of locks.
(1) from the perspective of the Database System
There are three types of locks:
1. exclusive lock)
A resource with an exclusive lock can only be used by locked programs. Other operations on the resource are not accepted. SQL Server automatically uses an exclusive lock when executing the data update command, namely the insert, update, or delete command. However, an exclusive lock cannot be applied to an object when other locks exist. The exclusive lock cannot be released until the transaction ends.
2. Shared lock)
A shared lock can be read by other users, but cannot be modified by other users. When the SELECT command is executed, SQL Server usually locks the object through a shared lock. Generally, after the data page with the shared lock is read, the shared lock will be released immediately.
3. Update lock)
Update locks are created to prevent deadlocks. When SQL Server is preparing to update data, it first locks the data object so that the data cannot be modified but can be read. When SQL Server determines that it wants to update data, it will automatically replace the update lock with an exclusive lock. However, when other locks exist on the object, the update locks cannot be locked.
2) from the programmer's perspective
There are two types of locks:
1. Optimistic lock)
Optimistic locks assume that when processing data, you can directly lock the record without doing anything in the application code, that is, you can rely entirely on the database to manage the lock. Generally, when a transaction is processed, SQL Server automatically locks the table updated within the transaction processing range.
2. pessimistic lock)
Pessimistic locks do not catch a cold in the Automatic Management of database systems. Programmers need to directly manage data or lock processing on objects and acquire, share, and discard any locks on the data being used.

Locking optimization program prompts and descriptions
Optimization Program prompt description of optimization program prompt
Holdlock remains locked until the transaction ends
No lock is used when nolock is used to retrieve data.
Paglock use page lock
Tablock
Tablockx uses an exclusive table lock
Updlock update lock

Example: Select * from authors (paglock holdlock Index = aunmind)
Deadlock and prevention:

A deadlock occurs when multiple users or multi-process users use the same resource. Generally, two users occupy one resource, both of them want to use the resources of the other party, but they do not want to give up their own resources at the same time, they will always wait for the other party to give up the resources. If external interference is not performed, they will continue to consume.
Deadlocks can cause a lot of waste of resources and even cause system crashes.In SQL Server, the principle for solving deadlocks is to "sacrifice one to be better than both", that is, pick out a process as the victim and roll back its transactions, and sends an error code 1205 to the program that executes the process.To prevent deadlocks, you must follow the following principles:
Avoid executing statements that involve data modification concurrently;
Each transaction is required to lock all data to be used at one time, otherwise it will not be executed;
All transactions in a blocking order must be blocked in this order. For example, the update execution sequence of objects in different processes should be consistent as far as possible;
The execution time of each transaction cannot be too long. For a transaction with a long program segment, consider splitting it into several transactions.