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 lenistest3go<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<i style="color:transparent">本文来源gaodai$ma#com搞$$代**码)网8</i> 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>

喜欢 (0)赏

[搞代码]

分享 (0)