转自:http://blog.csdn.net/abrahamcheng/article/details/6216428
浅谈Sql 中的锁
1.锁的概念
Sql server 使用锁来确保事务的独立性,锁可以为某个事务锁定资源,防止事务间就访问统一资源的发生冲突,如多个事务同时请求更新某条记,当某个事务开始时就lock住它要更新的记录,直到该事务结束 (unblock) 释放该资源, 这样其他事务在请求更新该记录时就会被block, 直到占用该资源的事务结束才有可能unblock.
2.锁的模式和兼容性
锁一般可以分为排他锁和共享锁,当需要更新,删除时使用排他锁,当仅仅要读某个资源时使用的是共享锁。因此排他锁又称之为写锁,共享锁又称之为读锁。锁之间的兼容性是指某个资源被加了某种锁A后能否再加其他锁B,如果可以则成为锁A对锁B是兼容的。
兼容性 | 写锁 | 读锁 |
写锁 | No | No |
读锁 | No | Yes |
由上表可见仅共享锁是兼容共享锁的。
3.可以被锁的资源类型(锁的粒度)
Sql Server 可以锁定不同的资源类型,或者说锁的粒度是不同的。 这些资源的类型可以有:RID or key (row), page, object (for example, table), database。 但row是属于某个page的,page 又是属于某个block(存储块)的 ……, 因此当要锁(exclusive lock)定一格row时,先要用意向锁(intent exclusive lock)锁定该row的上一层粒度page. 同样读取某个记录时在加共享锁之前也要先给对的的page加共享意向锁(intent share lock).
Requested Mode | Granted Exclusive (X) | Granted Shared (S) | Granted Intent Exclusive (IX) | Granted Intent Shared (IS) |
Grant Request for Exclusive? | No | No | No | No |
Grant Request for Shared? | No | Yes | No | Yes |
No | No | Yes | Yes | |
Grant Request for Intent Shared? | No | Yes | Yes | Yes |
这些锁的粒度一般由Sql server 动态决定,锁是要消耗资源的,一个简单的Sql 语句可能会用到成千上万的锁。它们也会随着Sql语句的执行结束而释放它们所站的资源。
4.和锁有关比较有帮助的Sql:
查看所有锁的信息:
SELECT -- use * to explore other available attributes
request_session_id AS spid,
resource_type AS restype,
resource_database_id AS dbid,
DB_NAME(resource_database_id) AS dbname,
resource_description AS res,
resource_associated_entity_id AS resid,
request_mode AS mode,
request_status AS status
FROM sys.dm_tran_locks;
查看对应的session:
SELECT -- use * to explore
session_id AS spid,
connect_time,
last_read,
last_write,
most_recent_sql_handle
FROM sys.dm_exec_connections
WHERE session_id = @spid
查看session 运行的sql语句:
SELECT session_id, text
FROM sys.dm_exec_connections
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST
WHERE session_id = @spid
查看session的运行帐户和相关信息:
SELECT -- use * to explore
session_id AS spid,
login_time,
host_name,
program_name,
login_name,
nt_user_name,
last_request_start_time,
last_request_end_time
FROM sys.dm_exec_sessions
WHERE session_id IN(52, 53);
查看是否有block的session:
SELECT -- use * to explore
session_id AS spid,
command,
sql_handle,
database_id,
wait_type,
wait_time,
wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;
设置锁的超时时间:
SET LOCK_TIMEOUT 5000;
SET LOCK_TIMEOUT -1; // 该句锁的超时时间设置成默认时间
终止一个session : KILL @spid;