[转] with (nolock) 解释

本文来自：http://blog.sina.com.cn/s/blog_5fafba5e010113kr.html

select * from t1 WITH(NOLOCK) 
select * from t1 WITH(READPAST) 

 这两个都可以绕开死锁进行查询，对于允许脏读的情况特别适用。

区别是：

NOLOCK     可能把没有提交事务的数据也显示出来
READPAST 会把被锁住的行不显示出来 

但在 Oracle 中，没有类似用法

select 1 from dual for update

select 1 from dual for update nowait

Oracle 中，for update 会对行加上锁，再 for update 时会等待，而 for update nowait 时会报错：

ORA-00054 resource busy and NOWAIT specified
ORA-30006: resource busy; acquire with WAIT timeout expired

select 1 from dual FOR UPDATE SKIP LOCKED

 跳过锁，不报错不超时不等待

select 1 from dual for update wait 2

  锁 2 秒后释放锁

Oracle 部分原文：https://www.cnblogs.com/quanweiru/archive/2012/11/09/2762223.html

 

顺便放两个查询 MSSQL 的语句：

select 'KILL '+CAST(a.spid AS NVARCHAR(100)) AS KillCmd,REPLACE(hostname,' ','') as hostname ,replace(program_name,' ','') as program_name
,REPLACE(loginame, ' ', '') AS loginame, db_name(a.dbid) AS DBname,spid,blocked,waittime/1000 as waittime
,a.status,Replace(b.text,'''','''') as sqlmessage,cpu
from sys.sysprocesses as a with(nolock)
cross apply sys.dm_exec_sql_text(sql_handle) as b
where a.status<>'sleeping' AND a.spid<>@@SPID


SELECT TOP 30
total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
last_execution_time AS [最后一次执行时间],min_worker_time /1000 AS [最小执行时间(ms)],
max_worker_time /1000 AS [最大执行时间(ms)],
SUBSTRING(qt.text,qs.statement_start_offset/2+1,
(CASE WHEN qs.statement_end_offset = -1
THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
AS [使用CPU的语法], qt.text [完整语法],
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1 and last_execution_time>dateadd(hh,-5,getdate())
ORDER BY (qs.total_worker_time/qs.execution_count/1000) DESC