T-sql: Concurrent processing under transaction lock (15)

1. Business

In SQL Servce, a transaction is a unit of work that may contain queries and modify data, as well as modify data definitions, and can also display or implicitly define transaction boundaries

Show definition transaction begin TRAN If you want to commit a transaction using commit TRAN to revoke a transaction (rollback) ROLLBACK TRAN The following example

BEGIN TRAN;INSERT  intosales.orders (CustID, Empid, OrderDate, RequiredDate, ShippedDate, ShipperID, freight, ShipName, shipaddr ESS, ShipCity, Shippostalcode, ShipCountry)VALUES      ( -,5,'20090212','20090301','20090216',       3,32.38N'Ship to 85-b'N'6789 rue de l''Abbaye'N'Reims', N'10345'N'France');Commit Tran

A simple display transaction can also be an implicit transaction

GO INSERT  intosales.orders (CustID, Empid, OrderDate, RequiredDate, ShippedDate, ShipperID, freight, ShipName, shipaddr ESS, ShipCity, Shippostalcode, ShipCountry)VALUES      ( -,5,'20090212','20090301','20090216',       3,32.38N'Ship to 85-b'N'6789 rue de l''Abbaye'N'Reims', N'10345'N'France');GO

Automatically commit a transaction through go when the current batch execution is completed by default, SQL Server will automatically commit the transaction after each statement ends as a single transaction or by setting the session to change the default settings

SET  on

When set to ON, you do not need to specify a BEGIN TRAN statement to start a transaction but must end with a commit TRAN or tollback TRAN tag

Transactions have 4 properties-atomicity, consistency, isolation, persistent acronym acid

atomicity (atomicity): A transaction is an atomic unit of work, and all modifications in a transaction are either committed or revoked.  If the system fails before the transaction commit instruction is logged to the transaction log, SQL Server revokes the modification when it restarts. If an error occurs in a transaction, it is automatically rolled back by default and can be detected by the @ @TRANCOUNT or if the current environment is in the transaction if it returns 1 No, it is 0.

SELECT    @ @TRANCOUNT

Consistency (consistency): an exponential data state in which each level in the isolation level is accessible only if the transaction remains consistent at the level of consistency. In the constraints also refers to the main foreign key library will convert the consistency state of the database to another consistency state to maintain consistency

Isolation (Isolation): Isolation is a mechanism for controlling access to data, ensuring that the firm-wide data is the data at the level of conformance it expects. Two different modes are supported in SQL Server to handle isolation: traditional lock-based mode and row versioning (new mode) but the default is lock mode and the current lock is a shared lock, and if the data state is inconsistent, the read data is blocked until the state is consistent. Instead of waiting or sharing locks when you change the version control mode to read, this mode is a way to improve concurrency when data is not displayed in a timely manner. The specific implementation depends on the isolation level used.

persistence (Durability): Data modification is always written to the transaction log disk of the database before it is written to the database disk. After committing, the instruction is logged on the transaction log disk, and the transaction is persistent until the disk data has been modified, and rollback is simply the deletion of all modification records of the transaction log.

The following is a complete example of a transaction:

BEGIN TRAN; DECLARE @neworderid  as INT;BEGINTRYINSERT  intosales.orders (CustID, Empid, OrderDate, RequiredDate, ShippedDate, ShipperID, freight, ShipName, Shipadd Ress, ShipCity, Shippostalcode, ShipCountry)VALUES      ( -,5,'20090212','20090301','20090216',       3,32.38N'Ship to 85-b'N'6789 rue de l''Abbaye'N'Reims', N'10345'N'France'); SET @neworderid = scope_identity(); SELECT @neworderid  asNeworderid; INSERT  intosales.orderdetails (OrderID, ProductID, UnitPrice, qty, Discount)VALUES(@neworderid, One,14.00, A,0.000),          (@neworderid, the,9.80,Ten,0.000),          (@neworderid, the,34.80,5,0.000);COMMIT TRAN;ENDTRYBEGINCATCHROLLBACK TRAN;ENDCatch

2. Lock

A lock is a control resource acquired by a transaction to protect a data resource against conflicting or incompatible access to other transactions.

Locks have two main lock mode exclusive and shared locks

When you try to modify the data, the affairs will request an exclusive lock on the data resource, and it will be blocked until the end of the transaction and any other transaction requests will be unblocked. For a single statement transaction, the lock is automatically dismissed as long as the statement ends. For multiple statement transactions, the transaction is unlocked only if he finishes all the statements and passes the COMMIT TRAN or ROLLBACK TRAN command

Exclusive lock : If a transaction is modifying a row until the transaction completes, no other transaction can modify the same row. But being able to read the same row depends on its isolation level.

shared locks : The default isolation level for shared lock SQL reads is added by default when reading transaction locks read COMMITTED because this isolation level causes transaction requests to read resources by default plus shared locks multiple transactions can have the same data resource share lock at the same time. This mode also causes phantom reads because of concurrency although the lock and duration cannot be modified while the data is being modified, you can control how the lock is handled by changing the isolation level while reading the data.

In fact, the essence of the lock is that the isolation level of the data can also be achieved by controlling the isolation level, and the effect is better.

The default isolation level in SQL Read COMMITTED SNAPSHOT This isolation relies on row versioning, not locks, in which the reader does not need to share the lock because it does not need to wait and relies on row versioning technology to provide isolation. If a transaction modifies a data row under the Read Committed isolation level until the transaction completes, the other transaction cannot read the same row. This concurrency control is called " pessimistic concurrency " If a transaction modifies a data row under the Read COMMITTED snapshot isolation level at which time another transaction reads the same line to get the available state for the last commit. This concurrency control, called optimistic concurrency, can be a good solution for modifying and demonstrating concurrency problems in optimistic concurrency.

Concurrent processing between such transactions is known as lock compatibility

Request Lock mode	Request an exclusive lock	Request a shared lock
Request an exclusive lock	Whether	Whether
Request a shared lock	Whether	Is

Crossover no represents incompatible request lock mode will be rejected cross is indicates that the compatible request lock mode will be received

So what resources can locks be locked?

Locked resources include rids, keys, rows, pages, objects, tables, databases, scopes, allocation units, heaps, B-trees.

What is the lock request process rule?

For example, getting an exclusive lock transaction on a row must first obtain an intent exclusive lock on the page on which the row resides, and an intent exclusive lock that owns the Page object, and the same shared lock is this step.

Why should I apply for an intent lock?

In order to detect lock-incompatible requests at a higher level, and to prevent these lock requests from being granted, this is why the same data row is blocked when an exclusive lock is requested.

For example, a transaction holding row is locked, and another transaction is requesting an incompatible lock on the page where the row is located, such as a row with the first transaction locked with an exclusive lock with a table intent lock. The request was rejected.

But intent locks do not reject lock requests for lower-level objects

For example: An intent lock on a page does not block an exclusive lock on other transactions on the page we can use a table to carefully understand the compatibility requests for these locks.

Request Lock mode	Request an exclusive lock	Request a shared lock	Request Intent Exclusive Lock	Request Intent shared Lock
Request Exclusive Lock	Whether	Whether	Whether	Whether
Request a shared lock	Whether	Is	Whether	Is
Request Intent Exclusive Lock	Whether	Whether	Is	Is
Request Intent shared Lock	Whether	Is	Is	Is

These locks give us the desired content for our most desirable concurrent processing locks, that is, the number of rows affected. Locks are required for memory resources and internal management overhead, and the current system resources are considered when locks are required.

One situation has to say that more than 5,000 in a row lock automatically upgrades the lock to the table lock and then triggers the default lock escalation for each additional 1250 locks

You can set the Lock_escalation table option to control lock escalation by using the ALTER TABLE statement. can also be disabled. You can also change the upgrade method such as partition level (the physical organization of a table into a small cell C becomes a partition) lock to the end of this statement next time how to gracefully exclude concurrency caused by blocking data processing.