1. TCL

1.1 What are Transactions?

It is a set of SQL commands which work as a single unit. i.e if they are executed, either they have to completely successful or rollback (revert) to original state of DB. No partial commits. Every transaction has to follow ACID properties.

• A unit of work, a single statement
• By default, transactions are set to Auto Commit
• Commit
  Finalize the statement and save changes
• Rollback
  Undo all statements or changes

所谓事务存储点就是在事务过程当中插入若干个标记，当事务执行中出现错误时，可
以不撤销整个事务，只是撤销部分事务，将事务退回到某个事物存储点。一旦事务提
交或回滚，则事务结束。

1.2 Types of Transactions

1.2.1 Implicit

• Allows you to run transactions without saying Begin Trans for each one
• At the end of the transactions statements, you choose Commit or Rollback
• Slower in performance
• only need SET IMPLICIT_TRANSACTIONS ON

User don't begin a transaction but has to commit or rollback. User has to make sure that SET IMPLICIT_TRANSACTIONS ON executed to start implicit transactions. If explicit ROLLBACK or COMMIT statement is not given by user, all the open transactions for the connection will be lost (rolled back) once the connection is disconnected.

1.2.2 Explicit (most used) 多数用这个 most transactions

• Requires you to specifically say when a transaction Begins and then either Commit or Roll it back
• Doesn’t require statements to be separated by GO statements
• Faster in performance
• need begin and committed
• can be nested
• if you need rollback,must have save point

1.2.3.1 implicit & complicit

• implicit中可以套explicit
• explicit就算commit可以执行，如果implicit roll back了，explicit也不能执行，因为是嵌套的

implicit	explicit
1.developer need not to start a transaction	developer has to start a trans using begin tran
nesting is not possible with just implicit trans	nesting is possible
need to specify SET IMPLICIT_TRANSACTIONS ON	no need to alter any session settings
performs slow compared to explicit	performance is better

1.2.3 auto commited

• something is haven
• defult setting
• have problems: 第一笔transaction发生，然后系统崩溃，没有把钱转到第二笔，就有问题了
• only work at statement level, roll back in this statement

1.3 ACID Properties

1.3.1 Automaticity

The statements/statement (transaction) should be completed successfully or rollback to original state. Using BEGIN TRAN and COMMIT/ROLLBACK.

• Transactions will be “all or nothing”

1.3.2 Consistency

DB has to be in consistent state before and after transaction. Making sure that all rules on the DB are validated for all transactions and should follow business rules. Using CHECK CONSTRAINTS, RULES, TRIGGERS, PROCEDURES, FUNCTIONS.

• Data will always be valid for each transaction
• eg: from transaction level wrong,invalid the business rule 银行每天取钱不能超过1000刀

1.3.3 Isolation

Every transaction should be independent of each other or a transaction should not interfere with other transaction. Using ISOLATION Levels

• Transactions will not interfere with each other
• isolation level

eg: t1 should not touch t2

1.3.4 Durability

Once the transaction (data)is committed it should stay in that state until it is modified by another transaction. Data should not be lost. Using COMMIT command and proper Backups (mainly log back ups), High Availability (Mirroring, Replication, Log Shipping, Clustering).

Transactions will remain committed and final

• using backups (如果服务器坏了，没有存上新的数据，就尴尬了）

1.4 视频代码

declare @int int

set @int =6

print @int

---comit transaction
go
declare @int int
begin tran
set @int =6
commit tran
print @int

----------------roll back & commit
go
declare @int int
begin tran
set @int =6
rollback tran

begin tran
set @int=2
commit tran
print @int


---------undo transaction
go
select * from client

select * from rank_test_table

begin tran
truncate table rank_test_table
rollback     transaction

select * from rank_test_table

-------------------------save point
go
begin tran t1
save tran s1
    update rank_test_table
    set name ='tim'
    where id = 4
rollback tran s1
    update rank_test_table
    set name ='superman'
    where id = 5
commit tran t1

select * from rank_test_table


-------------------------with mark
go
begin tran t1 with mark  'updating table'
save tran s1
    update rank_test_table
    set name ='tim'
    where id = 4
rollback tran s1
    update rank_test_table
    set name ='superman'
    where id = 5
commit tran t1

select * from rank_test_table


------implicit
go
Set Implicit_Transactions On

update rank_test_table
set name = 'tim'
where id =4

rollback transaction

update rank_test_table
set name ='superman'
where id =5
commit transaction

set Implicit_Transactions Off

select * from rank_test_table

1.5 error hadling in transaction

• use try catch

https://www.cnblogs.com/knowledgesea/p/3714417.html
链接有其他笔记

1.6 with mark

• to know which part we rollback in the log file
• Specifies that the transaction is marked in the log.
• If WITH MARK is used, a transaction name must be specified. WITH MARK allows for restoring a transaction log to a named mark.
  https://docs.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql?view=sql-server-2017

---

2. Isolation Levels & Locks

2.1 Locks in SQL

• confine, strict dataset
• prevent mess up data

21.1 Shared Locks

• Shared locks allow multiple users to SELECT the same data, but none can modify the data as it’s being read. By default the shared lock will be released as soon as the data is read, unless specified otherwise
  when selecting data (= reading)

2.1.2 Exclusive Locks

• Exclusive locks prevent access to the same data by multiple transactions. No other transactions can read or modify the data
• Commonly used for DML operations to make sure data isn’t modified by different operations
  还可以叫他独占锁、写锁。就是如果你对数据资源进行增删改操作时，不允许其它任何事务操作这块资源，直到排它锁被释放，防止同时对同一资源进行多重操作。
• exclusive to 1 transaction
  eg: t1 用着ex lock,t2想用shared lock也不可以
• dml operations will be given

2.1.3 Update Locks

• Fixes a common deadlock issue, often an update between two transactions will result in two exclusive locks on the same data. However, an update lock can be given to only one transaction and if that transaction does not update, then it will convert to an exclusive lock, otherwise it’ll become a shared lock
  防止出现死锁的锁模式，两个事务对一个数据资源进行先读取在修改的情况下，使用共享锁和排它锁有时会出现死锁现象，而使用更新锁则可以避免死锁的出现。资源的更新锁一次只能分配给一个事务，如果需要对资源进行修改，更新锁会变成排他锁，否则变为共享锁。
  Only one transaction can obtain an update (U) lock to a resource at a time. If a transaction modifies a resource, the update (U) lock is converted to an exclusive (X) lock.l

dead lock: both dml operation,nither transaction can be done,

---

https://www.jianshu.com/p/8b9065bb6a30

更新锁——Update lock
更新锁（U锁）。当T1给资源A加上更新锁后，代表该资源将在稍后更新，更新锁与共享锁兼容，更新锁可以防止例2里那种一般情况的死锁发生，更新锁会阻塞其他的更新锁和排他锁。因此相同资源上不能存在多个更新锁。

更新锁允许其他事务在更新之前读取资源。但不可以修改。因为其他事务想获取资源的排他锁时，发现该资源已存在U锁，则等待U锁释放。

在T1找到需要更新的数据时，更新锁直接转为排他锁，开始更新数据，不需要等待其他事务释放共享锁啥的。

那么就问了，共享锁为什么不可以直接升级为排他锁，而必须等待其他共享锁都释放掉才可以转为排他锁呢？

这就是共享锁和更新锁的一个区别了，共享锁之间是兼容的，但是更新锁之间互不兼容，因此仅有一个更新锁直接转为排他锁是安全的，而多个共享锁问也不问直接转为排他锁，那怎么行呢，排他锁只能有一个的，这就是为什么共享锁需要等待其他共享锁释放才可以升级为排他锁的原因了。

例4：
T1：

begin
select * from table with (updlock) (加更新锁)
update table set column1='hello' (重点：这里T1做update时，不需要等T2释放什么，而是直接把更新锁升级为排他锁，然后执行update)

T2：

begin
select * from table （T1的更新锁不影响T2的select）
update table set column1='world' （T2的update需要等待T1的update执行完）

分析：（1）T1先到达，T1的select句对table加更新锁，此时T2紧接着到达，T2的select句对table加共享锁，假设T2的select先执行完，要开始T2的update，发现table已有更新锁，则T2等，T1此时执行完select，然后将更新锁升级为排他锁，开始更新数据，执行完成，事务结束，释放排他锁，此时T2才开始对table加排他锁并更新。

（2）T2先到，T1紧接着，T2加共享锁 => T1加更新锁 => 假设T2先结束select => 试图将共享锁升级为排他锁 => 发现已有更新锁 => 之后的情况同（1）

---

2.1.4 Intent Locks

• Intent locks are SQL server’s way of reserving data to be locked. Lets say we want to use a table and specify a shared lock, SQL server places an intent shared lock on the table so no one else places a lock on it to prevent us from using it

• I.Shared I.Exclusive I.S&E

2.1.5 Schema Locks

• Schema locks are used when there is a DDL operation being performed on a table. This doesn’t prevent shared or exclusive locks, so people can still use the data. It does prevent OTHER DDL operations from occurring on the same table

2.1.6 Bulk Update Locks

• Prevents other processes that are not bulk operations copying data into a specified table from having any access to the data during the process
• To use this lock there must be TABLOCK specified (Hint)

2.2 Isolation Levels in SQL

It is a mechanism/process/property used by SQL Server to control the concurrency of transactions.
It uses locks to control the concurrency.

2.2.1 Read Uncommitted

• Data can be read even if it is being modified
• No shared locks used on table
• exclusive locks used on table
• Often results in “dirty data”
• Least restrictive
• Same as NOLOCK (Hint)
  当t1在被更新的时候，t2想读取数据，但是t2并没有用 shared lock

2.2.2 Read Committed (default)

• Data cannot be read that has been modified and not committed
• Prevents “dirty data”
• Data can be modified by other transactions, even if another transaction is running
• Creates Non-Repeatable Reads or Phantom Data
• Can use Snapshot (can copy)

2.2.3 Repeatable Read

• Data cannot be read that has been modified and not committed
• Data cannot be modified that has been read and not committed
• Shared locks are place on all data read
• New rows can still be inserted that fall into the search (issue)
• If it retries and gets new data, it’ll count as Phantom Data

2.2.4 Snapshot

• Takes a copy of the data being read or modified
• ALLOW_SNAPSHOT_ISOLATION must be on to use
• Snapshot cannot be used if a transaction is in another isolation level. Meaning it can’t be set during the middle of a transaction
  eg:网站截图，截图是静止的，但是时间其实还是在流动的
  underlying table 变动，也不会影响这个snapshot
• no lock in data
• problem: copy in temodb(overloading); modification

What are the 2 issues with Snapshot Isolation Level?
1. Snapshot maintains a ROWVERSIONING of transactions in TempDB, this is a overhead that SQL Server has to deal with. Along with copies of SNAPSHOTs stored in TempDB.
2. When a transaction started with Snapshot ISOLATION level and if it modifies some data within the snapshot and before this is committed if there is another transaction modifies the same data (and this trans started after the snapshot tran), data modifications by snapshot is not allowed.

2.2.5 Serializable ：phantom

• Data cannot be read that has been modified and not committed
• Data cannot be modified that has been read and not committed
• Cannot insert new rows into the table that falls in the search range
• Uses Range Locks

2.3 视频代码

set transaction isolation level read committed

begin tran
    select*
    from AdventureWorks2017.HumanResources.Employee
commit tran

2.4 data wrong problem

• dirty data:
  When a transaction reads data that is updated/inserted by other transaction but not committed/rolled back. READ UNCOMMITTED cannot handle this issue.
• mon repeatable data:
  If a transaction 1 is reading same data multiple times within the transaction and at the same time some other transaction (Transaction 2) modifies the data read by transaction 1. If Transaction1 displays different data for each read operation, it is called non repeatable read. READ COMMITTED, READ UNCOMMITTED cannot handle this issue.
• phantom data:
  If a transaction (Tran 1) is reading same data multiple times within the transaction and at the same time some other transaction (Transaction 2) inserts data within the range of data read by transaction 1. If Transaction1 displays those rows inserted by transaction 2, it is called phantom read. READ COMMITTED, READ UNCOMMITTED, REPEATABLE READ cannot handle this issue.

更新丢失：多个用户同时对一个数据资源进行更新，必定会产生被覆盖的数据，造成数据读写异常。
不可重复读：如果一个用户在一个事务中多次读取一条数据，而另外一个用户则同时更新啦这条数据，造成第一个用户多次读取数据不一致。
脏读：第一个事务读取第二个事务正在更新的数据表，如果第二个事务还没有更新完成，那么第一个事务读取的数据将是一半为更新过的，一半还没更新过的数据，这样的数据毫无意义。
幻读：第一个事务读取一个结果集后，第二个事务，对这个结果集经行增删操作，然而第一个事务中再次对这个结果集进行查询时，数据发现丢失或新增。

2.5 dead lock

• It is a situation in which 2 transactions are requesting for exclusive access to same data or data that is being read by each other. Remember truck example from class.

2.5.1 how to identify the dead lock?

1. Use SQL Profiler to identify what are 2 transactions and who is victim.
2. PerfMon can identify how many deadlocks are happening.
3. Use Extended Events
4. Trace Flags: DBCC TRACEON (1204)
   https://www.mssqltips.com/sqlservertip/1036/finding-and-troubleshooting-sql-server-deadlocks/
5. Let users/DBA tell you.

2.5.2 how to solve dead locks?

1. Usually SQL server identifies and kills a process which is cheaper to redo based on resources the transaction takes.
2. Use more optimistic isolation levels which would minimize the locking on resources leading to less conflicts on resource locking there by less dead locks.
3. Use dead lock priority for queries which you don't want to be killed. This is not a solution, it a way of not being a victim in dead lock scenario.
4. See if you can change timings of the queries or procs which conflict in Dead Lock scenarios.