"SQL"-Basics Grooming (eight)-Transactions and locks

The concept of a transaction

Transaction: The smallest execution unit of an operational database consisting of several T-SQL instructions, which either succeeds or fails altogether. (Concurrency control)

four properties of a transaction : atomicity, consistency, isolation, persistence. is called the Acid property of a transaction.

• Atomicity (atomicity) A transaction is an inseparable unit of work, and all operations included in the transaction are either done or not.
• Operations within a consistency (consistency) transaction cannot violate database constraints or rules, and internal data structures must be correct when a transaction completes.
• Isolation (isolation) When multiple transactions are concurrent, each transaction does not interfere with internal data, and it handles data that is before or after another transaction, and cannot be the data in another transaction.
• Persistence (durability), also known as permanence (permanence), refers to the fact that once a transaction is committed, its changes to the data in the database should be permanent. The next operation or failure should not have any effect on it.

3 Common types of transactions in SQL Server

Autocommit transactions: Is a way to automate and rollback transactions automatically, which is the default transaction mode for T-SQL. For example, when deleting a table record, if the record has a primary foreign key relationship, the deletion will be affected by the primary foreign KEY constraint, then the deletion will be canceled.
Explicit transactions: T-SQL indicates that the start of a transaction begins with begin Transaction and commits the transaction by commit Transaction, Rollback Transaction the end of the rollback transaction.
Implicit transaction: Use Set implicit_transactions on to open the implicit transaction mode without begin Transaction to open the transaction, when a transaction ends, this mode automatically enables the next transaction, with only commit Transaction Commit transactions, Rollback Transaction rollback transactions.

Statements commonly used in transactions

Begin Transaction: Marks the beginning of a transaction.
Commit Transaction: The transaction has been successfully executed and the data has been processed properly.
Rollback Transaction: Error during data processing, rollback to data state before processing, or rollback to a savepoint inside the transaction.
Save Transaction: The savepoint of the transaction's internal settings, that is, the transaction can not be rolled back, only rolled back here, to ensure that the transaction within the premise of error

Write a simple transaction

--start transaction begin TRAN Tran_addtable1--Error Trapping beginTry--statements correctly insert into table1 (id,name,value,sex) VALUES (4,'Michael2','CHAOSHUAI2',1); --add to save point--Save Tran Pigonein--the sex is int type error INSERT INTO table1 (id,name,value,sex) VALUES (5,'Michael3','Chaoshuai3','the weather is raining.') insert INTO table1 (id,name,value,sex) VALUES (6,'Michael4','Chaoshuai4',1); endTrybeginCatch    SelectError_number () asErrorNumber,--error Code error_severity () asErrorSeverity,--error severity level, level less than 10Try CatchError_state not captured () asErrorState,--Error Status Code error_procedure () asErrorprocedure,--the name of the stored procedure or trigger where the error occurred. Error_line () asErrorLine,--the line number where the error occurred error_message () asErrorMessage--specific information about the errorif(@ @trancount >0)--Global variable @ @trancount, transaction turn on this value +1He used to judge that there was an open transaction rollback TRAN Tran_addtable1---because of an error, the transaction is rolled back to the origin, and the first statement is not inserted successfully. EndCatchif(@ @TRANCOUNT >0) Commit Tran Tran_addtable1--Commit a transaction

Execution results

Analysis: Due to an error in inserting Table1, depending on the atomicity of the transaction, or all done, it is all right, so a data is not inserted

concurrency control for transactions

In the case of multiple users accessing the same data resource concurrently with a transaction, the following data errors are caused
1. Update lost: Multiple users at the same time to update a data resource, will inevitably produce overwritten data, resulting in data read and write exceptions.
2. Non-repeatable READ: If a user reads one piece of data multiple times in one transaction while another user updates the data at the same time, the first user is inconsistent with the data read multiple times.
3. Dirty read: The first transaction reads the data table that the second transaction is updating, and if the second transaction has not been completed, then the first transaction will read half the data that is updated and half of the data that has not been updated, which makes no sense.
4. Phantom read: After the first transaction reads a result set, the second transaction, the result set by row add and delete operations, but the first transaction again in the result set query, the data discovery is missing or new.

Set TRANSACTION ISOLATION LEVEL

READ UNCOMMITTED: This isolation level is the lowest, can read to a transaction is processing the data, but the transaction has not yet committed, this level of reading is called Dirty read.
Read Committed: This level is the default option, cannot be dirty read, cannot read the transaction is processing uncommitted data, but can be modified.
REPEATABLE READ: Cannot read the data that the transaction is processing, nor can it modify the data before the transaction data.
Snapshot: Specifies that the transaction starts with a snapshot of the submitted data, so the current transaction can only see the changes made to the data before the transaction begins.
Serializable: The highest transaction isolation level, only the data before the transaction is visible.

The concept of Locks

The Microsoft SQL Server Database engine locks resources using different lock modes, which determine how concurrent transactions access resources.

Classification of Locks

• Shared Lock: Allows concurrent transactions to read (SELECT) resources under closed concurrency control. When there is a shared lock (S Lock) on the resource, no other transaction can modify the data. Once the read operation is complete, the shared lock (S Lock) on the resource is released immediately;
• Exclusive locks: You can prevent concurrent transactions from accessing resources. When an exclusive lock is used, no other transaction can modify the data; Data modification statements (such as INSERT, UPDATE, and DELETE) merge modify and read operations, typically requesting shared and exclusive locks
• Update Lock: Prevents common deadlocks. This transaction reads the data [gets the shared lock (S Lock) of the resource (page or row)], and then modifies the data [this operation requires the lock to be converted to an exclusive lock (X Lock)]. If two transactions acquire a shared mode lock on the resource and then attempt to update the data at the same time, a transaction attempts to convert the lock to an exclusive (X) lock. The conversion of a shared mode to an exclusive lock must wait for a period of time, because the exclusive lock of one transaction is incompatible with the shared mode lock of other transactions; a lock wait occurs. The second transaction attempted to obtain an exclusive lock (X lock) to update. A deadlock occurs because two transactions are converted to exclusive (X) locks, and each transaction waits for another transaction to release the shared-mode lock.

The update lock (U Lock) allows only one transaction at a time to obtain an update lock (U Lock) for the resource. If the transaction modifies the resource, the update lock (U Lock) is converted to an exclusive lock (X lock)

• Intent Lock: The database engine uses intent locks to secure shared locks (S locks) or exclusive (X) locks on the underlying resources of the lock hierarchy. They can be obtained before lower-level locks, so the intention is to place the lock at a lower level.

For example, a shared intent lock is requested at the table level before a shared lock (S Lock) is requested on the table's page or row. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive lock (X Lock) on the table that contains that page. Intent locks can improve performance because the database engine only checks the intent lock on the table to determine whether a transaction can safely get locks on the table. You do not need to check each row in the table or lock on each page to determine whether the transaction can lock the entire table.

• Intent locks include intent sharing (IS), intent Exclusive (IX), and intent exclusive sharing (SIX).
• Schema Lock: The database engine uses schema modification (SCH-M) locks during table data definition language (DDL) operations, such as adding columns or dropping tables. During the lock, the SCH-M lock prevents concurrent access to the table.
• Bulk Update locks: Bulk Update locks (BU locks) allow multiple threads to load data concurrently into the same table, while preventing other processes that do not bulk load data from accessing the table.

Lock mode compatibility

How to reduce the deadlock to a minimum

Access the object in the same order.
Avoid user interaction in a transaction.
Keep the transaction short and in a batch.
Use a lower isolation level.
Use the isolation level based on row versioning.
Set the READ_COMMITTED_SNAPSHOT database option to on so that committed read transactions use row versioning.
Use snapshot Isolation.
Use a bound connection.

SQL-Basics Grooming (eight)-transactions and locks