Transactions and locks in SQL Server

Transactions and locks in SQL Server

 

Transactions are all about atomicity. The concept of atomicity refers to the ability to treat things as a unit. From the database perspective, it refers to the minimum combination of one or more statements that should be fully executed or not executed.

Points that can be marked in the transaction:

1. Begin Tran: Set the start point.

2. Commit Tran: make transactions a permanent and irreversible part of the database.

3. Rollback Tran: in essence, you want to forget that it has happened. Even if you roll back, you can return to the beginning of the transaction.

4. Save Tran: create a specific Identifier and only perform partial rollback.

How SQL Server logs work

Resources that can be locked in SQL Server:

1.Database:Lock the entire database. This usually happens when the entire database mode changes.

2.Table:Lock the entire table. This includes all data-related objects associated with the table, including the actual data rows (each row of the table) and keys in all indexes associated with the table.

3.Section:Lock the entire CIDR block. Because a segment consists of eight pages, therefore, segment locking means locking the control segment, controlling 8 data or index pages in the segment, and 8 data rows in the segment.

4.Page:Lock all data or index keys on this page.

5.Key:There is a lock on a specific key or a series of keys in the index. Other keys on the same index page are not affected.

6.Row or row identifier (RID ):Technically speaking, the lock is placed on the row identifier (which is the internal structure of SQL Server), but the entire data row is essentially locked.

Lock mode of SQL Server lock:

1.Shared lock:A shared lock is used to read data without changing any content.

2. exclusive lock:Cannot coexist with other locks.

3.Update lock:An update lock is a combination of a shared lock and an exclusive lock. It is a special placeholder. That is, data is shared first and can be read. When you need to change the data, you can convert it to an exclusive lock to effectively prevent deadlocks.

4.IntentLock ):Is a real placeholder used to deal with object hierarchy issues.

5.SchemaLock ):It can be divided into Mode Modification locks (Object Mode change) and mode stability locks (maintaining mode stability ).

6.Batch update lock:Data can be loaded in parallel.

Lock compatibility:

SQL Server deadlock error code: 1205

ACID of the transaction:

1.Atomicity ):The transaction is either executed in full or not in full.

2.Consistency ):All constraints and other data integrity rules must be observed, and all related objects (data pages and index pages) must be completely updated ).

3.Isolation (Isolation ):Each transaction is completely isolated from any other transaction. One transaction will not be disturbed by another transaction action.

4.Durability ):After the transaction is completed, its function will always exist in the system.