Things in SQL Server

1. Four properties of a transaction

Atomic Atomicity, consistency consistency, isolation isolation, persistent durability, acid properties.

atomicity : A transaction must be a unit of complete work, either fully executed or not executed at all.

consistency : At the end of the transaction, all internal data is correct.

isolation : When multiple transactions are concurrent, each transaction does not interfere with internal data, and it processes data that is before or after another transaction.

Persistence : After a transaction commits, the data is permanent and cannot be rolled back.

2. Transactions in SQL Server are divided into 3 categories of common transactions

autocommit Transactions : Is the default transaction pattern for SQL Server, and each SQL statement is treated as a transaction. If executed successfully, it is automatically committed and automatically rolled back if the error occurs.

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 the transaction. When a transaction is completed, this mode automatically enables the next transaction, committing the transaction with commit Transaction only, Rollback Transaction rollback the transaction.

3. The Grammar of Things

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 save point inside the transaction is that the transaction can not be rolled back and only rolled back here, guaranteeing that the transaction is not error-prone.

4. Example

---open transactions

BEGIN Tran

Begin try--here we can add the wrong catch mechanism

Insert into A (Id,name,typeid) VALUES (1, ' Xiao Wang ', 1)--the statement is correct

--Save Tran Pigonein--here we can add a savepoint to save the correct data before

Insert into A (Id,name,typeid) VALUES (2, ' Xiao Li ', ' students ')--incorrect statement type

Insert into A (Id,name,typeid) VALUES (1, ' Xiao Zhang ', 2)--the statement is correct

End Try

Begin Catch

SELECT Error_number () as ErrorNumber,--error code

Error_severity () as errorseverity,--error severity level less than ten try Catch caught

Error_state () as ErrorState,--Error status code

Error_procedure () as Errorprocedure,-the name of the stored procedure or trigger in which the error occurred.

Error_line () as ErrorLine,--the line number where the error occurred

Error_message () as errormessage--specific information about the error

if (@ @trancount >0)-The global variable @ @trancount, the transaction turns on this value +1, and he is used to judge that there is an open transaction

Rollback TRAN--error rollback, table 0 data

--Rollback TRAN Pigonein--error rollback, table 1 data

End Catch

if (@ @trancount >0)

Commit Tran

SELECT * from A-if successful in table A, there will be 3 data.

5. Using Set Xact_abort

Specifies whether to roll back the current transaction (Xact_abort on/off) , when on, if the current SQL error occurs, the entire transaction is rolled back, and if the SQL error is rolled back to the current SQL statement, the other statements run the read-write database as usual.

Note : Xact_abort is only useful for errors that occur at run time, and xact_abort is useless if there is an error in the SQL statement itself.

Example:

Set Xact_abort off

BEGIN Tran

Insert into A (Id,name,typeid) VALUES (1, ' Xiao Wang ', 1)--the statement is correct

Insert into A (Id,name,typeid) VALUES (2, ' Xiao Li ', 12313212313212313)--Arithmetic overflow error, will be inserted into the other two. If this is ' student ', xact_abort will expire without inserting any data

Insert into A (Id,name,typeid) VALUES (1, ' Xiao Zhang ', 2)--the statement is correct

Commit Tran

SELECT * FROM A

6. Things concurrency

In the case of multiple users accessing the same data resource concurrently with a transaction, the following data errors are caused.

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.

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.

Dirty Read : The first transaction reads the data table that the second transaction is updating, and if the second transaction has not yet been updated, then the first transaction will read half the data that has been updated, half of which has not been updated, and this data is meaningless.

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 found missing or new

We solve these problems by locking in the data that is being manipulated, and when a transaction is manipulating some chunks of data, another transaction cannot get into the blocks.

Locking from a database perspective can be broadly divided into 6 types:

shared Lock (S): For a read operation (SELECT), you can also call it a read lock. Multiple transactions can read data concurrently, but any transaction cannot modify the data until the data read is complete and the shared lock is freed. s lock usually data is read and released immediately.

Exclusive lock (X): For write operations (INSERT, DELETE), you can also call him an exclusive lock, write lock. Only one transactional data is allowed, that is, if you make additions or deletions to the data resource, no other transaction is allowed to manipulate the resource until the exclusive lock is released, preventing multiple operations on the same resource at the same time. The x lock is released until the end of the transaction.

update Lock (U): Used to book an X lock on this page, it allows other transactions to read, but does not allow you to apply again. U lock is to prevent deadlock mode, when two transactions to a data resource first read in the case of modification, the use of shared and exclusive locks sometimes deadlock phenomenon, and the use of update locks can avoid the occurrence of deadlocks. A resource's update lock can only be assigned to one transaction at a time, and if the resource needs to be modified, the update lock becomes an exclusive lock, or it becomes a shared lock. The U lock will not be released until the end of the transaction.

Intent lock : SQL Server needs to acquire shared locks, exclusive locks, and update locks on the underlying resources in the hierarchy (such as rows, columns). For example, an intent shared lock is placed at the table level, which means that the transaction will use a shared lock on the table's page or row. Placing an intent lock on a row of a table prevents other transactions from acquiring other incompatible locks. Intent locks can improve performance because the data engine does not need to detect every row in a resource's column to determine whether a compatible lock can be obtained for that resource. The intent lock consists of three types: Intent Shared lock (IS), intent exclusive Lock (IX), intent Exclusive shared lock (SIX).

schema lock : The lock that is accessed concurrently when the table structure is prevented from being modified.

Bulk Update lock : Allows multiple threads to insert bulk data concurrently into the same table, and not allow other processes to access the table while it is being loaded.

The mutual compatibility between these locks, that is, whether they can exist simultaneously.

	the existing licensing model
The requested mode	Is	S	U	Ix	SIX	X
Intent Sharing (IS)	Is	Is	Is	Is	Is
Share (S)	Is	Is	Is
Update (U)	Is	Is
Intent Exclusive (IX)	Is			Is
Intent exclusive Sharing (SIX)	Is
Exclusive (X)

7. Deadlock

Deadlock means that each process in a set of processes occupies a resource that will not be freed, but is in a permanent state of waiting for each other to request resources that are not freed by other processes.

There are several ways to reduce deadlocks:

Access Objects in the same order: Concurrent transactions Access objects in the same order, and the likelihood of deadlocks is reduced.

Keep the transaction short : Try not to let a transaction handle overly complex reads and writes, the transaction is too complex, the resource usage increases, processing time grows, and the concurrent execution of things usually happens to deadlock.

avoid user interaction in transactions : Try not to require a user response in a transaction, because any locks held by a transaction are freed only after the transaction commits or rolls back, waiting for the user to respond, which can lead to blocking or deadlock.

reduce concurrency and time-consuming data operations : Minimize the concurrency of the database and reduce the amount of time that transactions wait.

use a lower isolation level: It takes less time to use a lower isolation level than to hold a shared lock with a higher isolation level. This reduces lock contention. Note: First determine whether the transaction can run at a lower isolation level.

using row versioning-based isolation levels: If the read_committed_snapshot database option is set to on, transactions that run under the read-committed isolation level will use row versioning instead of shared locks during read operations.

8. Setting isolation levels for transactions

The so-called thing isolation level is the read depth level of the concurrent transaction to the same resource. Divided into 5 kinds.

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 : The data that the transaction is processing cannot be read, nor can it be modified before the transaction data is processed.

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.

Grammar

Set TRAN Isolation level < levels >

Example 1:read uncommitted

BEGIN Tran
Set Deadlock_priority Low
Update A set name= ' Xiao Wang 1 ' where id=1--original data Xiao Wang
WAITFOR DELAY ' 0:0:5 '--wait 5 seconds to execute the following statement
Rollback Tran

Set TRAN Isolation LEVEL READ UNCOMMITTED
SELECT * FROM A--read data is the data being modified, dirty read
WAITFOR DELAY ' 0:0:5 '--5 seconds after data has been rolled back
SELECT * FROM A--data after rollback

Get name= ' Xiao Wang '.

Example 2:read committed
BEGIN Tran
Update A set name= ' Xiao Wang '
WAITFOR DELAY ' 0:0:10 '--wait 10 seconds to execute the following statement
Rollback Tran

Set TRAN Isolation LEVEL Read Committed
SELECT * from a--get less than a, not dirty read
Update A set Name= ' Xiao Wang 2 ' where id=1--can be modified
WAITFOR DELAY ' 0:0:10 '--10 seconds after the previous transaction has been rolled back
SELECT * from a--modified data, not A

Get Name= ' Xiao Wang 2 '.

Things in SQL Server