浅谈Sql 中的锁

x625802392

浅谈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
Grant Request for Intent Exclusive?	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,
blocking_session_id,
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;