SQL Server concepts

1. Transaction and error check
   • The most important knowledge of SQL Server is transactions, such as many OLTP (online transaction processing) applications. What is a transaction? A transaction is a collection of SQL statements. Transactions include implicit transactions (such as insert and update statements) and explicit transactions (explicitly indicated by the tran in TRAN Statement ). Errors are usually checked using @ error, for example, begin tran
     Update a set id = 5 where id = 1
     If @ error <> 0
     Rollback Tran update a set id = 5 where id = 2
     If @ error <> 0
     Rollback tran
     Commit tran
2. Lock)
   • By lock granularity, locks can be divided into the following categories:
     Key lock ---> row lock ---> page lock ---> extent lock ---> table lock) ---> database lock)
   • Locks can be divided into the following categories:
     Holdlock, xlock, updlock, schlock, and intent lock. If you want to view the lock type, use the system stored procedure sys_lock to view it.
3. Isolation level (isolation level)
   • There are 4 isolation levels in the transaction:
     1. Read uncommitted (uncommitted read) --- read uncommitted, can read information that has been modified in the memory but not saved to the hard disk, that is, dirty Data Reading is allowed.
     2. Read committed (submit read) --- read commit: only the information submitted to the hard disk can be read. If the information is modified in the memory but not submitted to the hard disk, no commit Tran, the other transaction cannot read anything and is blocked by another transaction. Once the transaction that modifies the data is commit Tran, the transaction that reads the data runs immediately and the modified data is read.
     3. Repeatable read --- when transaction a sets the isolation level to Repeatable read, transaction B modifies the data, because the Repeatable read isolation level sets an update lock for the entity (row or table) of the operation, transaction B cannot update the data at this time, but transaction B can insert new data, because the Repeatable read isolation level does not set an exclusive lock for the entity (row or table) of the operation, transaction a can read the Phantom.
     4. Serializable (Serial read) --- serializable, that is, the transaction operates one by one, including the update, insert, select, and so on the operating entity.
   • In fact, the relationship between the isolation level and the lock is inseparable. The implementation of the isolation level is essentially to operate on the lock, since we cannot accurately judge what specific locks should be applied when operating an object, SQL Server database opens up a simple way for us to do so, the isolation level is used. The higher the entity isolation level, the more entity locks, the more complex the types. The higher the entity isolation level, the lower the chance of parallelization, and the higher the chance of serialization.
4. Save point)
   • The emergence of the storage point is to make the transaction recovery more rapid and easy, because you don't need to restore all the operations, but just restore to the storage point, details on how to recover and more specific knowledge will be detailed in the recovery blog of the transaction. For example, begin tran
     Update a set id = 4 where id = 1
     Save Tran T1
     Update a set id = 3 where id = 2
     If @ error <> 0
     Rollback T1
     Update a set id = 5 where id = 3
     Commit tran