SQL Server和Oracle中的锁和死锁（1） - 锁和死锁
1、锁的作用
ORACLE ：
锁是一种机制，当某个数据库对象正被其他进程或用户修改时，这种机制可以保护它不被修改。
为了防止对象被两个不同的用户同时修改，数据库系统创建了锁这种复杂的数据结构，来实现锁定机制，这种机制使用在oracle中称为排队的队列结构，以串行的方式执行锁。
锁的作用如下：
1) 他们坚持一致性和完整性，在事务处理期间，数据和对象保证它们的一致性和完整性。
2) 但对象不是立即可用时，它们提供一种队列结构，这种结构允许所有会话都加入到等候对象的队列中，
3) 自动处理锁机制
4) 锁的持续时间等于被提交事务的长度或处理时间。
SQL SERVER ：
锁的作用是提供事务的隔离性，保证各个事务不会互相干扰，一个事务不会读取或修改另一个事务正在使用的数据，此外，锁提供的隔离性还保证事务的一致性。
2 、锁的模式
ORACLE中的锁模式和描述如下：
2.1 锁模式描述和缩写词
缩写词模式说明RS（ss）ROW SHARE为了更新，每个事务在表中锁定了行，但允许其他事务锁定表中的其他行。RXROW EXCLUSIVE为了更新，每个事务在表中锁定了行，但不允许其他事务锁定表中的其他行。SSHARE某个事务以一个模式锁定了表，这个模式允许其他事务以SHARE模式锁定这个表，但不允许在这个表中进行任何更新SRX（ssx）SHARE ROW EXCLUSIVE在SHARE MODE中，不允许其他事务锁定这个表，且不允许DML语句。TMN/A表级别的锁TXN/A行级别的锁UULN/A用户定义的锁XXEXCLUSIVE这个表被锁定了，并且不允许其他会话锁定这个表，或者把DML语句提交到这个表
2.2 锁模式和DML语句
SQL Server中的锁模式和描述如下：
缩写词模式说明SSHARE共享锁取得资源共享锁。SQL SERVER用共享锁执行所以读取操作，共享锁阻止取得独占锁，任何事务要修改共享锁所在页或行的数据时，受到阻止，直到释放所以共享锁为止。Uupdate更新锁取得资源更新锁。更新锁锁定用户进程要修改的页。SQL SERVER找到正确位置和准备插入记录时，将更新锁升级为独占锁。XEXCLUSIVE独占锁独占锁在准备修改数据时向事务提供。资源的独占锁保证其他事务不会干扰持有独占锁的事务锁定的数据。在事务结束时释放独占锁，独占锁与其他任何类型的锁都不兼容。如果对资源持有独占锁， 任何其他进程对同一资源的读取或修改请求都要等到独占锁释放，同理，如果当前另一进程持有资源的读取锁定，这独占锁请求要等到资源可用为止。IS共享意向锁表示进程当前持有或想持有低层资源（页或行）的共享锁。IU更新意向锁表示进程当前持有或想持有低层资源（页或行）的更新锁。IX独占意向锁表示进程当前持有或想持有低层资源（页或行）的独占锁。BU批量锁批量复杂使用的批量更新锁。Range键范围锁SQL SERVER中的键范围锁隔离事务之间对数据的修改，使事务每次返回相同的结果集。即防止“幻影读”。
3、死锁
死锁就是两个进程都在等待对方持有的资源锁，要等对方释放持有的资源锁之后才能继续工作，它们互不相让，坚持到底，实际上，双方都要等到对方完成之后才能继续工作，而双方都完成不了。
Oracle死锁样本：
步骤一：
登陆ORACLE SQL *plus 之一窗口，执行：
update HR.JOBS
SET JOB_title = ’S.Finance Manager’
where job_id = ’FI_MGR’
步骤二：
登陆ORACLE SQL *plus 之二窗口，执行：
SQL Server死锁样本：
死锁使事务中止时，SQL Server向客户机返回错误号1205，由于死锁不是逻辑错误，而只是资源争夺问题，因此客户机可以更新提交整个事务，要在应用程序中处理死锁，要在错误处理器中捕获消息1205。遇到消息1205时，应用程序可以自动重新提交事务，最好不要然用户看到SQL Server返回的死锁错误消息。
我们知道可以通过SP_lock和SP_who监视进程之间的锁争用，但是，一旦出现死锁，一个事务回退，一个事务继续。此时使用sp_lock已经看不到真正死锁的资源信息(或许能够看许多X类型的锁信息)，因为所涉及资源的锁已经释放。
SQL SERVER 提供了几个跟踪标志，可以监视出现的死锁。可以用DBCC TRACEON命令打开跟踪标志，用DBCC TRACEOFF关闭跟踪标志，要然SQL SERVER把死锁跟踪标志的输出写入到错误日志中。首先要设置DBCC TRACEON(3605)，比如：
DBCC TRACEON(3605)
DBCC TRACEON(1204)
这样，一旦出现死锁，将能在错误日志中监视到相关明细信息。
4、锁争用的监测和解决
ORACLE:
ORACLE提供了有用的锁的动态性能视图V$LOCK和V$locked_OBJECT。
动态性能视图V$LOCK包含所有当前由系统和所以连接的会话保持的锁的信息。
如select * from V$lock 返回下面样本：
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- ----------------- ---------- ---------- -------- -------- ----
682BE878 682BE888 2 MR 201 0 4 0 12383 0
682BE82C 682BE83C 2 MR 11 0 4 0 12383 0
682BE7E0 682BE7F0 2 MR 10 0 4 0 12383 0
V$locked_OBJECT提供了当前被锁定的对象。可以查出该对象由什么锁模式锁定的。
当我们查询到死锁的进程的信息可以使用KILL命令终止这个产生死锁的ORACLE会话进程：
alter SYSTEM KILL SESSION ’查出的SID,查出的SERIAL#’;
其中SID即V$LOCKED_OBJECT 的SESSION_ID, serial#为V$session的serial#。
再杀操作系统进程:
KILL SPID或ORAKILL 刚才查出的SID 刚才查出的SID
SQL SERVER ：
要监视SQL SERVER 的锁活动，有如下几种常用的方法：
1) 使用SP_LOCK和SP_WHO存储过程。
2) 直接查询syslockinfo表。
3) 使用SQL SERVER Management studio的活动监视器浏览锁活动。
4)使用SQL Profiler浏览锁活动。
让我们看看SQL SERVER Management studio的活动监视器浏览锁的页面，如下图：
这个列表返回的信息和使用SP_lock返回的信息是一致的，比如
exec SP_lock
GO
这个命令的输出样本如下：
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- --------------------------------
52 5 0 0 DB S GRANT
52 5 148195578 1 PAG 1:489 IX GRANT
52 5 148195578 0 TAB IX GRANT
52 5 148195578 1 KEY (07005a186c43) X GRANT
54 1 1115151018 0 TAB IS GRANT
54 5 0 0 DB S GRANT
这个样本提供了下列的信息：
Spid 是事务的进程ID。
dbid 是持有锁的的数据库ID。
ObjID是持有锁的资源ID 。
IndId 是持有锁的表索引ID 。
Type是持有资源的锁类型。
Resource 是持有锁的资源内部名称，这个信息来自 syslockinfo 系统表。
Mode是事务请求的锁模式，锁类型请参考本文的锁模式一节
STATUS 是请求的当前状态。取值为 GRANT/WAIT/GNVRT .
我们发现上面的样本信息中出现了很多X类型的锁，如果该X锁引起了阻塞或死锁等现象，我们可以使用KILL命令解决解决锁争用，Sql server通过使用KILL 命令终止锁进程。
比如：
KILL 52
5 、避免死锁
ORACLE ：
1) 避免应用不运行长事务。
2) 经常提交以避免长时间锁定行。
3) 避免使用LOCK命令锁定表。
4) 在非高峰期间执行DDL操作。
5) 在非高峰期间执行长时间运行的查询或事务。
6) 确保开发人员使用限制最少的锁模式比不过仔细设计防止锁争用的事务，
7) 监控阻塞其他锁的锁并调查为什么这些锁正被保持。
8) 确定为什么阻塞的锁的被保持了很长时间并尽量阻止它们。
9) 监视死锁发生的频率并解决它们。
10) 当死锁发生通过回滚事务rollback或者终止会话来解决它。
SQL SERVER ：
SQL SERVER能够自动探测和处理死锁，但应用程序应尽可能的避免，遵循如下原则：
1) 从表中访问数据的顺序要一致，避免循环死锁。
2) 减少使用holdlock或使用可重复读与可序列化锁隔离级的查询，从而避免转换死锁。
3) 恰当选择事务隔离级别。选择低事务隔离级可以减少死锁。
