sqlserver--transaction-Lock and isolation levels

Isolation is actually implemented through locks, acting on the entire transaction, which is usually specified before the transaction begins, such as SET TRANSACTION isolation level read Committed, which specifies that subsequent transactions are read-committed The lock is implemented by specifying a lock pattern in from in when we execute a specific SQL statement, which overrides the type of lock applied under the specified isolation level. The isolation level compromises concurrency to achieve consistency.

Concurrency: Refers to the same data that is accessed by multiple users at the same time. It usually causes the following problems: Dirty reading, missing update, non-repetition degree, phantom reading;

• Dirty Read : One process read data that has not yet been submitted by another process.
• non-repeatable reading : a process that reads data two times is different if another process modifies the data in the middle of a process two reads.
• Phantom reads : The transaction performs the same query two times during execution, and the result of the second query contains data that is not present for the first query or that does not appear for the first query. This is because between the execution of the two queries, this is because the transaction does not lock the scope of the read, and in between the two queries, another transaction has inserted or deleted the delete.
• update is missing (Lost update). Two processes read the same data and make modifications, and one process overwrites the modification of another process. Or two transactions both modify the same data, but the second transaction is rolled back due to an error, causing the data modification of two transactions to be lost. This is because the system does not perform any lock operations and the concurrent transactions are not quarantined.

Isolation level

Isolation level	Dirty Read	Non-REPEATABLE READ	Phantom image	Description
Uncommitted read (READ UNCOMMITTED)	Is	Is	Is	If other transactions are updated, whether committed or not, execute immediately
Commit read (Read committed default)	Whether	Is	Is	Reads the submitted data. If other transactional updates are not committed, wait for
REPEATABLE READ (Repeatable Read)	Whether	Whether	Is	No other transaction update is allowed during the query
Serializable read (serializable)	Whether	Whether	Whether	No other transaction is allowed during the query insert or delete

Submit Read

Assume that table A is present, as shown below

A1	A2	A3
11	21st	31
12	22	32

Open Query Analyzer and open two connections, and enter the following two transactions, respectively:

--Transaction Ⅰ

SET TRANSACTION Isolation Level READ Committed

BEGIN Tran

Update A Set A2 = where A1 = 11

WAITFOR DELAY ' 00:00:10 '

Rollback Tran

--Transaction Ⅱ

SET TRANSACTION Isolation Level READ Committed

SELECT * from A where A1 = 11

If the transaction Ⅰ is run first and then the transaction Ⅱ is run, the transaction Ⅱ waits for 10 seconds (a connection cannot query the data block until it is unlocked when the data block is modified). And vice versa: cannot write and modify when reading.

If the transaction ⅱ is changed to the following

SET TRANSACTION Isolation Level READ UNCOMMITTED

SELECT * from A where A1 = 11

Then the transaction Ⅱ does not need to wait and execute immediately (you can see that the READ UNCOMMITTED transaction select does not issue a shared lock on the data)

Lock: (This is mainly about shared and exclusive locks, two commonly used locks)

Shared locks are primarily for shared read (select), and if there is a transaction (one or more) that has a shared lock on the data in the table (depending on how much of the lock data is dependent on the granularity of the lock), the locked data is not allowed to be updated (update) (from the lock point of view, the transaction is not allowed to acquire an Wait until all the shared locks are released). Conversely, if a transaction already has an exclusive lock on the data (only one), other transactions cannot acquire shared and exclusive locks on the locked data (that is, exclusive and shared locks are not compatible, see Lock compatibility for more information), where the locked data is highlighted, because some of the data explains "When a connection is written, Another connection can write ", in fact, this is the case that each connection reads and writes data is not the same row, that is, each connection locked data is different.

Based on the above analysis, we summarize six words as "shared read, exclusive write".

Once you understand the lock situation, it involves a problem. How long will the transaction remain locked?

In general, the lock time of a shared lock is related to the isolation level of the transaction, and if the isolation level is the default level of Read Committed, locks are held only during the read (select) period, which is the release of the lock when the data is queried, or if the isolation level is higher repeatable Read or serializable to release the lock until the end of the transaction. Also, if the HOLDLOCK hint is specified in the SELECT statement, the lock will not be released until the end of the transaction.

Exclusive locks are not released until the end of the transaction.

Having made the above analysis, we may now have questions about what kind of locks are issued when executing SQL statements, which is determined by the isolation level of the transaction. In general, a read statement (select) issues a shared lock, and the Write statement (Update,insert,delete) emits an exclusive lock. However, if this does not meet our requirements how to do, there is no more choice, don't worry, SQL Server provides us with the concept of locking hints.

The lock prompt specifies the SQL statement, which overrides the isolation level of the transaction. The following is a breakdown of the individual locking tips (see the online Help for SQL Server for more information), which I have made.

Type 1

①readuncommitted: Do not issue a lock

②readcommitted: Sends a shared lock to the end of the read

③repeatableread: Issue a shared lock to the end of the transaction

④serializable: Issue a shared lock to the end of the transaction

Type 2

①nolock: Do not issue a lock. Equivalent to ReadUncommitted

②holdlock: A shared lock is issued and remains until the end of the transaction. Equivalent to Serializable

③xlock: An exclusive lock is issued and remains until the end of the transaction.

④updlock: Issue an update lock, which is persisted to the end of the transaction transaction. (Update Lock: does not block anything else, allowing other things to read data (that is, the update lock is compatible with a shared lock), but he ensures that the data has not been updated since the last time the data was read)

⑤readpast: A shared lock is issued, but the lock line is skipped, and it is not blocked. Applicable conditions: Commit read isolation level, row level lock, select statement.

Type 3

①rowlock: Row-level lock

②paglock: page-level lock

③tablock: Table lock

④tablockx: Table Exclusive lock

After the completion of the lock, the following combination of a specific example, the specific look at the use of locks.

In many systems, this is often the case, to keep a number unique, such as the number of vouchers in the accounting software. A number is processed so that the maximum number in the table is saved to the table, and then added to the number to form a new number. This process is very demanding for concurrent processing, so let's simulate this process to see how to keep the numbering unique.

Create a new table code to hold the maximum number of vouchers. The fields are as follows: No.: BH (Numeric (18,0)), voucher table name Pinzheng (varchar (50))

Let's say that there is a record in the table:

Bh	Pinzheng
18000	Accounting vouchers

Create a new stored procedure to generate the voucher number, as follows:

CREATE PROCEDURE UP_GETBH as

Begin Tran

Declare @numnewbh Numeric (18,0)

Select @numnewbh = BH from Code with (updlock,rowlock) where Pinzheng = ' Accounting voucher '

Set @numnewbh = @numnewbh + 1

Update code Set BH = @numnewbh where Pinzheng = ' Accounting voucher '

Print @numnewbh

Commit Tran

GO

Then, open the Query Analyzer, and open a few more connections (the author opened 8 connections, the simulation of 8 people simultaneously, the reader can open more connections to experiment), and similar to the following statements copied into each connection window,

DECLARE @i numeric (18,0)

Set @i = 1

While @i = 1

Begin

If GETDATE () > ' 2004-07-22 14:23 '--set a time to execute the UPGETBH stored procedure at the same time

Set @i = 0

End

EXEC UP_GETBH

Then, running each connection in succession, at the moment of 2004-7-22 14:23, each connection runs UP_GETBH at the same time. From the running results you can see that the connection sequence appears with a 18001-digit number, and there is no sign of the number or the phenomenon of missing.

Analysis: Because the SELECT statement in UP_GETBH uses update locks, because the update locks are incompatible, each connection waits until all other connections release the lock to execute, and the release of the update lock waits until the end of the transaction, so that no error occurs.

Attached: Compatibility table for Locks

The existing licensing model

sqlserver--transaction-Lock and isolation levels