SQL Server Concurrency

并发存在会产生的问题，直接导致了我们需要的并发控制模型。SQL SERVER的每一种并发控制模型都是针对这些问题而设计的，所以首先我们要了解并发的潜在问题有哪些，然后去探索并发控制的模型。 并发控制模型，采用的是锁机制，详细了解了各种锁的兼容机制，才

并发存在会产生的问题，直接导致了我们需要的并发控制模型。SQL SERVER的每一种并发控制模型都是针对这些问题而设计的，所以首先我们要了解并发的潜在问题有哪些，然后去探索并发控制的模型。

并发控制模型，采用的是锁机制，详细了解了各种锁的兼容机制，才能更好了解隔离模型之间的兼容性。锁，所涉及到的概念很多，锁的对象，锁的所属对象，锁的持续时间，锁的种类等等。

知道锁的概念就要学会适当的去用锁。两种方法，事务隔离机制以及锁暗示(lock hint).

1 使用事务隔离机制

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">transaction</span> <span class="hljs-keyword">isolation</span> <span class="hljs-keyword">level</span> serializable
<span class="hljs-keyword">begin</span> <span class="hljs-keyword">transaction</span>
<span class="hljs-keyword">select</span> top <span class="hljs-number">10</span> * <span class="hljs-keyword">from</span> dbo.fctdbsize
<span class="hljs-keyword">commit</span> <span class="hljs-keyword">transaction</span></span></code>

这里实例用了最高级别的隔离机制，当目前这个事务在运行时，其他事务都将等待这个事务里用到的资源。除了serializable, 还有 read uncommitted, read committed, read snapshot, read repeated.将这些代入上面的set 语句就可以了。

所需要关心的是各个隔离机制之间是如何兼容的，比如 read uncommitted事务与 serializable 事务之间的竞争关系。从上到下的理解，事务其实用的还是锁，事务之间的兼容最终还是回归到锁之间的兼容。

看下提交一个serializable 事务，但是不提交，看看中间状态的事务，都有哪些特性?

<code class=" hljs sql">use lenistest3
go
<span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">transaction</span> <span class="hljs-keyword">isolation</span> <span class="hljs-keyword">level</span> serializable
<span class="hljs-keyword">begin</span> <span class="hljs-keyword">transaction</span> trans_serializable <span class="hljs-keyword">with</span> mark <span class="hljs-string">'test for serializable transaction'</span>
<span class="hljs-keyword">select</span> top <span class="hljs-number">10</span> * <span class="hljs-keyword">from</span> dbo.fctdbsize</span></code>

这里给事务标记一个事务名字，并附上Mark.

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">select</span> db_name(dbt.database_id) <span class="hljs-keyword">as</span> databaseName
, <span class="hljs-keyword">at</span>.name <span class="hljs-keyword">as</span> transactionName
, <span class="hljs-keyword">at</span>.transaction_id
,<span class="hljs-keyword">at</span>.transaction_begin_time
, <span class="hljs-keyword">case</span>
<span class="hljs-keyword">when</span> <span class="hljs-keyword">at</span>.transaction_type = <span class="hljs-number">1</span> <span class="hljs-keyword">then</span> <span class="hljs-string">'read and write transaction'</span>
<span class="hljs-keyword">when</span> <span class="hljs-keyword">at</span>.transaction_type = <span class="hljs-number">2</span> <span class="hljs-keyword">then</span> <span class="hljs-string">'read only transaction'</span>
<span class="hljs-keyword">when</span> <span class="hljs-keyword">at</span>.transaction_type = <span class="hljs-number">3</span> <span class="hljs-keyword">then</span> <span class="hljs-string">'system transaction'</span>
<span class="hljs-keyword">when</span> <span class="hljs-keyword">at</span>.transaction_type = <span class="hljs-number">4</span> <span class="hljs-keyword">then</span> <span class="hljs-string">'distributed transaction'</span>
<span class="hljs-keyword">end</span> <span class="hljs-keyword">as</span> transaction_type
, <span class="hljs-keyword">case</span>
<span class="hljs-keyword">when</span> <span class="hljs-keyword">at</span>.transaction_state = <span class="hljs-number">0</span> <span class="hljs-keyword">then</span> <span class="hljs-string">'The transaction has not been completely initialized yet.'</span>
<span class="hljs-keyword">when</span> <span class="hljs-keyword">at</span>.transaction_state = <span class="hljs-number">1</span> <span class="hljs-keyword">then</span> <span class="hljs-string">'The transaction has been initialized but has not started.'</span>
<span class="hljs-keyword">when</span> <span class="hljs-keyword">at</span>.transaction_state = <span class="hljs-number">2</span> <span class="hljs-keyword">then</span> <span class="hljs-string">'The transaction is active'</span>
<span class="hljs-keyword">when</span> <span class="hljs-keyword">at</span>.transaction_state = <span class="hljs-number">3</span> <span class="hljs-keyword">then</span> <span class="hljs-string">'The transaction has ended. This is used for read-only transactions'</span>
<span class="hljs-keyword">when</span> <span class="hljs-keyword">at</span>.transaction_state = <span class="hljs-number">4</span> <span class="hljs-keyword">then</span> <span class="hljs-string">'The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place'</span>
<span class="hljs-keyword">when</span> <span class="hljs-keyword">at</span>.transaction_state = <span class="hljs-number">5</span> <span class="hljs-keyword">then</span> <span class="hljs-string">'The transaction is in a prepared state and waiting resolution'</span>
<span class="hljs-keyword">when</span> <span class="hljs-keyword">at</span>.transaction_state = <span class="hljs-number">6</span> <span class="hljs-keyword">then</span> <span class="hljs-string">'The transaction has been committed.'</span>
<span class="hljs-keyword">when</span> <span class="hljs-keyword">at</span>.transaction_state = <span class="hljs-number">7</span> <span class="hljs-keyword">then</span> <span class="hljs-string">'The transaction is being rolled back.'</span>
<span class="hljs-keyword">when</span> <span class="hljs-keyword">at</span>.transaction_state = <span class="hljs-number">8</span> <span class="hljs-keyword">then</span> <span class="hljs-string">'The transaction has been rolled back'</span>
<span class="hljs-keyword">end</span> <span class="hljs-keyword">as</span> transaction_staus
, trl.request_session_id
, trl.request_mode
, trl.request_type
, trl.request_status
, trl.request_owner_type
, datediff(ss,<span class="hljs-keyword">at</span>.transaction_begin_time, getdate()) <span class="hljs-keyword">as</span> request_lifetime_s
, trl.resource_type
, trl.resource_description
, trl.resource_associated_entity_id
, <span class="hljs-keyword">case</span>
<span class="hljs-keyword">when</span> trl.resource_type = <span class="hljs-string">'OBJECT'</span> <span class="hljs-keyword">then</span> object_name(convert(<span class="hljs-keyword">varchar</span>,trl.resource_associated_entity_id))
<span class="hljs-keyword">else</span>
<span class="hljs-string">'other objects not table'</span>
<span class="hljs-keyword">end</span> <span class="hljs-keyword">as</span> objectName
, logs.host_name
, logs.program_name
, logs.login_name
, req.blocking_session_id
<span class="hljs-keyword">from</span> sys.dm_tran_database_transactions dbt
<span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> sys.dm_tran_active_transactions <span class="hljs-keyword">at</span> <span class="hljs-keyword">on</span> dbt.transaction_id = <span class="hljs-keyword">at</span>.transaction_id
<span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> sys.dm_tran_session_transactions st <span class="hljs-keyword">on</span> st.transaction_id = dbt.transaction_id
<span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> sys.dm_tran_locks trl <span class="hljs-keyword">on</span> trl.request_session_id = st.session_id
<span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> sys.dm_exec_sessions logs <span class="hljs-keyword">on</span> logs.session_id = st.session_id
<span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> sys.dm_exec_requests req <span class="hljs-keyword">on</span> req.session_id = st.session_id
<span class="hljs-keyword">where</span> dbt.database_id = db_id(N<span class="hljs-string">'lenistest3'</span>)</span></code>

TransactionName这一列和我们刚才定义的事务名称一样；
Transaction_Type其实并不十分精确，因为我们没做写的操作;
Request_mode都是S， 无论是对数据库，还是针对表； request_status表示已经得到这个锁

现在我们尝试往这个表里insert一条记录：

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> dbo.fctdbsize(record_date,type_desc,name,<span class="hljs-keyword">size</span>,size_mb,size_gb,x_flag ) <span class="hljs-keyword">select</span> top <span class="hljs-number">1</span> record_date,type_desc,name,<span class="hljs-keyword">size</span>,size_mb,size_gb,x_flag <span class="hljs-keyword">from</span> dbo.fctdbsize</span></code>

没有提示完成，说明在等待，我们看下等待在哪里，用上面的SQL来查：

这里对应了request_mode – IX, request_status为WAIT 。我们从sys.dm_exec_requests 取了一个 blocking_session_id给它 ，因为这个query包含了所有的transaction,所以很容易察看到谁在产生Block.

我们将 transaction isolation level改为 read committed.

<code class=" hljs sql">use lenistest3
go
<span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">transaction</span> <span class="hljs-keyword">isolation</span> <span class="hljs-keyword">level</span> <span class="hljs-keyword">read</span> committed
<span class="hljs-keyword">begin</span> <span class="hljs-keyword">transaction</span> trans_serializable <span class="hljs-keyword">with</span> mark <span class="hljs-string">'test for serializable transaction'</span>
<span class="hljs-keyword">select</span> top <span class="hljs-number">10</span> * <span class="hljs-keyword">from</span> dbo.fctdbsize</span></code>

再执行一边 insert，会发现这次执行成功了。 锁在不同的隔离机制下，持续的时间也会不同。一旦独到这个数据，就释放锁。Serializable的隔离，使得锁停留在对象上的时间直到事务结束。

依次尝试这些transaction isolation level，看看哪些会对insert有影响:

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">SET</span> <span class="hljs-keyword">TRANSACTION</span> <span class="hljs-keyword">ISOLATION</span> <span class="hljs-keyword">LEVEL</span>
{ <span class="hljs-keyword">READ</span> UNCOMMITTED
| <span class="hljs-keyword">READ</span> COMMITTED
| REPEATABLE <span class="hljs-keyword">READ</span>
| SNAPSHOT
| SERIALIZABLE
}
[ ;</span> ]</code>

除了 Serializable,还有 repeatable read也使用了大量的行锁，repeatable read对page, table也有IS锁，这是为了保证已读取的数据在整个事务中的一致性，如果insert不小心要更改这些已被读取的数据或者页，都会等待， IS不影响X，这里的insert随机读取一条数据，table上有IS锁，但insert还是成功提交的。

上面讨论的是读在前，写在后的场景。我们接下来讨论写在前，而读在后的情况。

<code class=" hljs sql">use lenistest3
go
<span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">transaction</span> <span class="hljs-keyword">isolation</span> <span class="hljs-keyword">level</span> serializable
<span class="hljs-keyword">begin</span> <span class="hljs-keyword">transaction</span> trans_serializable <span class="hljs-keyword">with</span> mark <span class="hljs-string">'test for serializable transaction'</span>
<span class="hljs-keyword">update</span> dbo.salesman <span class="hljs-keyword">set</span> man_name = man_name + <span class="hljs-string">' test for trans'</span> <span class="hljs-keyword">where</span> man_id = <span class="hljs-number">1</span></span></code>

在表salesman上，事务加上一个 X锁，一直保留到事务结束。这里仅仅是堆表，还需要考虑到有索引的情况，过会讨论 。

然后执行一条只读事务：

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">transaction</span> <span class="hljs-keyword">isolation</span> <span class="hljs-keyword">level</span> <span class="hljs-keyword">read</span> committed
<span class="hljs-keyword">begin</span> <span class="hljs-keyword">transaction</span> read_committed
<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> salesman <span class="hljs-keyword">where</span> man_id = <span class="hljs-number">1</span></span></code>

这里可以看到IS锁被 X锁给block了。

还有个有趣的现象，我们不设置事务锁，只执行

<code class=" hljs cs"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> salesman <span class="hljs-keyword">where</span> man_id = <span class="hljs-number">1</span></code>

如果我们的监控语句不用left join是不会显示这个单条select语句，原因是在 sys.dm_tran_session_transactions里面不会记录这种ad hoc的事务。所以我们把我们的监控语句改成left join.虽然也不准，但是至少告诉你有这个ad hoc的存在。

这里我们可以用 read uncommitted来读取这条暂时未被提交的事务。Read uncommitted隔离机制，我猜想是没有加任何的锁，除了database share这个锁之外，告诉别的session还有人在用这个数据库，请勿作database一级的处理，比如删除数据库等

还可以用snapshot隔离事务，只读更改前的数据。这个隔离机制也不加锁（我猜）.

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">transaction</span> <span class="hljs-keyword">isolation</span> <span class="hljs-keyword">level</span> snapshot
<span class="hljs-keyword">begin</span> <span class="hljs-keyword">transaction</span> read_committed
<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> salesman <span class="hljs-keyword">where</span> man_id = <span class="hljs-number">1</span></span></code>

我们用监控语句也发现之有database一级的share操作。

Repeatable Read隔离机制 在这里的作用和 read committed一样的。使用了IS锁都会被X锁block住。

综上， read uncommitted, snapshot都是不加锁的，所以这两种隔离机制中，任何读都不会被write给block住。Read committed, Serializable, Repeatable Read三种隔离机制中，任何读都会被write给block住（当然是针对同一资源而言）。

那我们猜测下，读和读会不会互相block呢? 从最高隔离机制开始：

<code class=" hljs sql">use lenistest3
go
<span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">transaction</span> <span class="hljs-keyword">isolation</span> <span class="hljs-keyword">level</span> serializable
<span class="hljs-keyword">begin</span> <span class="hljs-keyword">transaction</span> trans_serializable <span class="hljs-keyword">with</span> mark <span class="hljs-string">'test for serializable transaction'</span>
<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> salesman <span class="hljs-keyword">where</span> man_id = <span class="hljs-number">1</span></span></code>

再执行第二个只读事务:

<code class=" hljs sql">use lenistest3
go
<span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">transaction</span> <span class="hljs-keyword">isolation</span> <span class="hljs-keyword">level</span> serializable
<span class="hljs-keyword">begin</span> <span class="hljs-keyword">transaction</span> trans_serializable1 <span class="hljs-keyword">with</span> mark <span class="hljs-string">'test for serializable transaction 2'</span>
<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> salesman <span class="hljs-keyword">where</span> man_id = <span class="hljs-number">1</span></span></code>

通过监控语句 ，发现两者互不干扰，只是hold S lock的时间长了点。

上面提到有index的时候，update带来的锁情况。我们将表扩大一点：

<code class=" hljs oxygene">declare @<span class="hljs-keyword">loop</span> int = <span class="hljs-number">0</span>
<span class="hljs-keyword">while</span> @<span class="hljs-keyword">loop</span> <= <span class="hljs-number">16</span>
<span class="hljs-keyword">begin</span>
<span class="hljs-keyword">begin</span> transaction
insert <span class="hljs-keyword">into</span> salesman(man_id,man_name,man_country_id)
<span class="hljs-keyword">select</span> man_id,man_name,man_country_id <span class="hljs-keyword">from</span> salesman
commit transaction
<span class="hljs-keyword">set</span> @<span class="hljs-keyword">loop</span> = @<span class="hljs-keyword">loop</span> + <span class="hljs-number">1</span>
<span class="hljs-keyword">end</span></code>

然后执行serializable更新:

<code class=" hljs sql">use lenistest3
go
<span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">transaction</span> <span class="hljs-keyword">isolation</span> <span class="hljs-keyword">level</span> serializable
<span class="hljs-keyword">begin</span> <span class="hljs-keyword">transaction</span> trans_serializable <span class="hljs-keyword">with</span> mark <span class="hljs-string">'test for serializable transaction'</span>
<span class="hljs-keyword">update</span> salesman <span class="hljs-keyword">set</span> man_name = man_name + <span class="hljs-string">' serial transa '</span> <span class="hljs-keyword">where</span> man_name = <span class="hljs-string">'leiws test for trans serial transa trans2 '</span></span></code>

这里就直接锁索引了。 RangeS-U是对index key来说的，IU， IX是对index page和表来说的。

同时我们再开一个session来读取对应的索引值：

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">transaction</span> <span class="hljs-keyword">isolation</span> <span class="hljs-keyword">level</span> serializable
<span class="hljs-keyword">begin</span> <span class="hljs-keyword">transaction</span> trans
<span class="hljs-keyword">select</span> man_name <span class="hljs-keyword">from</span> dbo.salesman <span class="hljs-keyword">where</span> man_name = <span class="hljs-string">'leiws test for trans serial transa trans2 '</span></span></code>

这里读取的session，采用的是serializable隔离机制，但是并不被另一个session的写操作给影响了。 针对同一个key值，只读session用了RangeS-S锁，可见RangeS-S与RangeS-U并不排斥。

但是奇怪的是，再也不能重现类似的场景了。又一次互相排斥了。结论还需要进一步验证。

2 不使用事务隔离机制，而是用 query option来解决lock的问题

执行下面两个语句：

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">select</span> top <span class="hljs-number">10</span> * <span class="hljs-keyword">from</span> dbo.salesman <span class="hljs-keyword">with</span>(nolock)
<span class="hljs-keyword">option</span>(fast <span class="hljs-number">10</span> )

<span class="hljs-keyword">select</span> top <span class="hljs-number">10</span> * <span class="hljs-keyword">from</span> dbo.salesman
<span class="hljs-keyword">option</span>(<span class="hljs-keyword">table</span> hint(dbo.salesman,nolock))</span></code>

第一个查询没问题，但是第二个查询就有问题了:

<code class=" hljs livecodeserver">Msg <span class="hljs-number">8722</span>, Level <span class="hljs-number">16</span>, State <span class="hljs-number">1</span>, Line <span class="hljs-number">15</span> Cannot execute query. Semantic affecting hint <span class="hljs-string">'nolock'</span> appears <span class="hljs-operator">in</span> <span class="hljs-operator">the</span> <span class="hljs-string">'TABLE HINT'</span> clause <span class="hljs-operator">of</span> object <span class="hljs-string">'dbo.salesman'</span> but <span class="hljs-operator">not</span> <span class="hljs-operator">in</span> <span class="hljs-operator">the</span> corresponding <span class="hljs-string">'WITH'</span> clause. Change <span class="hljs-operator">the</span> OPTION (TABLE HINTS...) clause so <span class="hljs-operator">the</span> semantic affecting hints match <span class="hljs-operator">the</span> WITH clause.</code>

Table hint是配合着plan guide一起用的，如果不配合plan guide一起用，就要在with和query hint里面一起使用，限制条件挺多.

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">select</span> top <span class="hljs-number">10</span> * <span class="hljs-keyword">from</span> dbo.salesman <span class="hljs-keyword">with</span>(nolock)
<span class="hljs-keyword">option</span>(<span class="hljs-keyword">table</span> hint(dbo.salesman,nolock))</span></code>

这样来用，虽然效果达到了，但是却十分麻烦.

Plan Guides: 当语句是不可更改的时候，比如第三方产品生成的语句或者前端发过来的语句但是作为DBA却不能做更改的时候，我们要更改这个语句的执行计划，该怎么做呢? Plan Guide就登场了。

创建一个plan guide：

<code class=" hljs ruby">sp_create_plan_guide [ <span class="hljs-variable">@name</span> = ] <span class="hljs-constant">N</span><span class="hljs-string">'plan_guide_name'</span>
    , [ <span class="hljs-variable">@stmt</span> = ] <span class="hljs-constant">N</span><span class="hljs-string">'statement_text'</span>
    , [ <span class="hljs-variable">@type</span> = ] <span class="hljs-constant">N</span><span class="hljs-string">'{ OBJECT | SQL | TEMPLATE }'</span>
    , [ <span class="hljs-variable">@module_or_batch</span> = ]
      { 
                    <span class="hljs-constant">N</span><span class="hljs-string">'[ schema_name. ] object_name'</span>
        | <span class="hljs-constant">N</span><span class="hljs-string">'batch_text'</span>
        | <span class="hljs-constant">NULL</span>
      }
    , [ <span class="hljs-variable">@params</span> = ] { <span class="hljs-constant">N</span><span class="hljs-string">'@parameter_name data_type [ ,...n ]'</span> | <span class="hljs-constant">NULL</span> } 
    , [ <span class="hljs-variable">@hints</span> = ] { <span class="hljs-constant">N</span><span class="hljs-string">'OPTION ( query_hint [ ,...n ] )'</span> 
                 | <span class="hljs-constant">N</span><span class="hljs-string">'XML_showplan'</span>
                 | <span class="hljs-constant">NULL</span> }</code>

删除一个 plan guide:

<code class=" hljs mathematica">sp_control_plan_guide [ @operation = ] <span class="hljs-keyword">N</span><span class="hljs-string">'<control_option>'</span>
  [ , [ @name = ] <span class="hljs-keyword">N</span><span class="hljs-string">'plan_guide_name'</span> ]
<control_option>::=
<span class="hljs-list">{ 
    DROP 
  | DROP ALL
  | DISABLE
  | DISABLE ALL
  | ENABLE 
  | ENABLE ALL
}</span></code>

监控所有的plan guides :

<code class=" hljs ruleslanguage"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> sys.plan_guides</code>

一个简单的例子：

<code class=" hljs perl"><span class="hljs-keyword">exec</span> sp_create_plan_guide
<span class="hljs-variable">@name</span> = N<span class="hljs-string">'salesman_idx_query'</span>
, <span class="hljs-variable">@stmt</span> =N<span class="hljs-string">'select top 10 * from dbo.salesman'</span>
, <span class="hljs-variable">@type</span> = N<span class="hljs-string">'SQL'</span>
, <span class="hljs-variable">@module_or_batch</span> = NULL
, <span class="hljs-variable">@params</span> = NULL
, <span class="hljs-variable">@hints</span> = N<span class="hljs-string">'option (table hint (dbo.salesman, index(idx_man_Id)))'</span>

<span class="hljs-keyword">exec</span> sp_control_plan_guide
<span class="hljs-variable">@operation</span> = N<span class="hljs-string">'drop'</span>
, <span class="hljs-variable">@name</span> = N<span class="hljs-string">'salesman_idx_query'</span></code>

再次执行

<code class=" hljs cs"><span class="hljs-keyword">select</span> top <span class="hljs-number">10</span> * <span class="hljs-keyword">from</span> dbo.salesman</code>

这里不再是做全表扫描，而是先扫描索引，再做bookmark lookup了。当然这个例子只是对plan guide的一个应用，并没有给性能带来好的提升。