SQL Server Transactions and Locks

A transaction is a SQL statement that acts as a logical unit of work, and if any one statement fails, the entire operation fails, and the operation is rolled back to the pre-operation state, or to the previous node. A transaction can be used to ensure that it is either executed or not executed. Locks are the key to implementing transactions, and locks guarantee the integrity and concurrency of transactions.

The lock in the database is also a mechanism for resolving various conflicts during concurrent access

What is the purpose of the lock?

The main solution to multiple users concurrent operation of the database will bring the following data inconsistencies:

• Missing updates while modifying one piece of data
• Read dirty, a modified data, b after reading A and then cancel the modification, B read dirty
• Non-repeatable read, a user read the data, then B users read the data and modify, at this time a user read the data again and again found the value of two times inconsistent
• There is also the illusion of reading, this situation does not seem to be many.

The main method of concurrency control is blocking, a lock is a period of time to prohibit users from doing certain operations to avoid inconsistent data

What are the granularity of locks?

• Database Lock: Locks the entire database, which usually occurs when the entire database schema changes.
• Table Lock: Locks the entire table, which contains all data related objects associated with the table, including the actual rows of data (each row) and the keys in all indexes associated with the table.
• Block Lock: Locks the entire segment because a section consists of 8 pages, so the block lock is the lock that controls the section, controls the 8 data or index pages in that section, and all the rows of data in the 8 pages.
• Page Lock: Locks all data or index keys in the page.
• row or row identifier: Although technically, the lock is placed on the row identifier, but essentially, it locks the entire data row.

SQL Server Transactions and Locks