【Sqlserver】各种数据库的锁表和解锁操作

<span style="color: #008080">--</span><span style="color: #008080">1.查看被锁表 </span>
<span style="color: #0000ff">SELECT</span> request_session_id <span style="color: #0000ff">as</span> spid,<span style="color: #ff00ff">OBJECT_NAME</span>(resource_associated_entity_id) <span style="color: #0000ff">as</span> tableName <span style="color: #0000ff">FROM</span> sys.dm_tran_locks <span style="color: #0000ff">WHERE</span> resource_type<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">OBJECT</span><span style="color: #ff0000">‘</span> 
<span style="color: #008080">--</span><span style="color: #008080">spid   锁表进程 ；tableName   被锁表名 </span><span style="color: #008080">
--</span><span style="color: #008080">2.解锁 </span>
<span style="color: #0000ff">declare</span> <span style="color: #008000">@spid</span>  <span style="color: #0000ff">int</span>  <span style="color: #0000ff">Set</span> <span style="color: #008000">@spid</span>  <span style="color: #808080">=</span> 被锁表的进程号 <span style="color: #0000ff">declare</span> <span style="color: #008000">@sql</span> <span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">1000</span>) <span style="color: #0000ff">set</span> <span style="color: #008000">@sql</span><span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">kill </span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span><span style="color: #ff00ff">cast</span>(<span style="color: #008000">@spid</span>  <span style="color: #0000ff">as</span> <span style="color: #0000ff">varchar</span>) <span style="color: #0000ff">exec</span>(<span style="color: #008000">@sql</span>)

Oracle 查询被锁住的表和解锁表

<span style="color: #008080">--</span><span style="color: #008080">1.锁表查询的代码有以下的形式： </span>
<span style="color: #0000ff">SELECT</span> <span style="color: #ff00ff">count</span>(<span style="color: #808080">*</span>) <span style="color: #0000ff">FROM</span> v$locked_object; <span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span><span style="color: #000000"> v$locked_object; 
</span><span style="color: #008080">--</span><span style="color: #008080">2.查看哪个表被锁 </span>
<span style="color: #0000ff">SELECT</span> b.owner,b.<span style="color: #ff00ff">object_name</span>,a.session_id,a.locked_mode <span style="color: #0000ff">FROM</span> v$locked_object a,dba_objects b <span style="color: #0000ff">WHERE</span> b.<span style="color: #ff00ff">object_id</span> <span style="color: #808080">=</span> a.<span style="color: #ff00ff">object_id</span><span style="color: #000000">; 
</span><span style="color: #008080">--</span><span style="color: #008080">3.查看是哪个session引起的 </span>
<span style="color: #0000ff">SELECT</span> b.username,b.sid,b.serial#,logon_time <span style="color: #0000ff">FROM</span> v$locked_object a,v$session b <span style="color: #0000ff">WHERE</span> a.session_id <span style="color: #808080">=</span> b.sid <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span><span style="color: #000000"> b.logon_time;  
</span><span style="color: #008080">--</span><span style="color: #008080">4.杀掉对应进程 </span>
<span style="color: #0000ff">alter</span> system <span style="color: #0000ff">kill</span> session<span style="color: #ff0000">‘</span><span style="color: #ff0000">1025,41</span><span style="color: #ff0000">‘</span><span style="color: #000000">; 
</span><span style="color: #008080">--</span><span style="color: #008080">其中1025为sid,41为serial#.</span>

【Sqlserver】各种数据库的锁表和解锁操作

标签：进程   arc   from   blank   _id   cts   ble   解锁   lan