Transactions and locks in SQL Server

transactions and locks in SQL Server

The transaction is all about Atomicity (atomicity). The concept of atomicity refers to the idea that some things can be treated as a unit. From a database standpoint, it refers to the smallest combination of one or more statements that should be executed or all not executed.

Points that can be marked in a transaction:

1. Begin Tran: Sets the starting point.

2. Commit Tran: Make the transaction a permanent, irreversible part of the database.

3. Rollback Tran: Essentially, you want to forget that it ever happened, even if you roll back, back to the beginning of the transaction.

4. Save Tran: Creates a specific identifier that runs only as part of the rollback.

How the SQL Server log Works

Resources that can be locked in SQL Server:

1. Database: Lock the entire database. This usually occurs when the entire database schema changes.

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

3. Section: Lock the entire section. Because a section is made up of 8 of pages, Block locking refers to locking the section, controlling the 8 data or index pages within that section, and all data rows in 8.

4. Page: Locks all data or index keys on the page.

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

6. Row or row identifier (RID): Although technically the lock is placed on the row identifier (which is the internal structure of SQL Server), it essentially locks the entire data row.

Lock mode for SQL Server locks:

1. Shared locks: Shared locks are used when only reading data is required, that is, nothing is changed.

2. Exclusive lock: Cannot coexist with other locks.

3. Update Lock: The update lock is a mixture of shared and exclusive locks and is a special placeholder. That is to share data first, can read, when need to change, convert to exclusive lock, can effectively prevent deadlock.

4. Intent Lock (Intentlock): is a real placeholder for dealing with object-level problems.

5. Mode Lock (Schemalock): Divided into modes to modify the lock (on the object mode change) and mode stability Lock (maintain mode stability).

6. Batch update Lock: Allows data to be loaded in parallel.

Compatibility of Locks:

Error number for deadlock in SQL Server: 1205

Acid of the transaction:

1. atomicity (atomicity): The transaction either executes all or does not execute.

2. Consistency (consistency): All constraints and other data integrity rules need to be adhered to, and all related objects (data pages, index pages) are completely updated.

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

4. Persistence (Durability): When the transaction is completed, its effect will always exist in the system.