锁总结

SQL Server死锁总结

http://www.cnblogs.com/happyhippy/archive/2008/11/14/1333922.html

// 略去的无关的code
SqlConnection conn  =   new  SqlConnection(connectionString);
conn.Open();
SqlTransaction tran  =  conn.BeginTransaction();
string  sql1  =   " Update Lock1 SET C1=C1+1 " ;
string  sql2  =   " SELECT * FROM Lock1 " ;
ExecuteNonQuery(tran, sql1);  // 使用事务:事务中Lock了Table
ExecuteNonQuery( null , sql2);  // 新开一个connection来读取Table

public   static   void  ExecuteNonQuery(SqlTransaction tran,  string  sql)
{
    SqlCommand cmd = new SqlCommand(sql);
    if (tran != null)
    {
        cmd.Connection = tran.Connection;
        cmd.Transaction = tran;
        cmd.ExecuteNonQuery();
    }
    else
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();
            cmd.Connection = conn;
            cmd.ExecuteNonQuery();
        }
    }
}

注：出错的原因是事务没有提交，并不会发生死锁

==================================

一个典型的数据库操作事务死锁分析

http://www.cnblogs.com/kaima/archive/2008/04/24/1167550.html

using(事务)
{
    try{
        for()//一个循环
        {
            if(查询【表A】有该【记录】==false)//这个查询没有用当前事务的数据库连接，而是新开一个数据库连接查询数据库
            {
                将【记录】插入【表A】;
                插入【表B】;
            }
        }
        事务.提交();
    }catch{
        事务.回滚();
    }
}

注，事务跨度过大，增加了发生死锁的机会

==============================

==============================================================

尽管死锁不能完全避免，但遵守特定的编码惯例可以将发生死锁的机会降至最低。将死锁减至最少可以增加事务的吞吐量并减少系统开销，因为只有很少的事务：

• 回滚，撤消事务执行的所有工作。
• 由于死锁时回滚而由应用程序重新提交。

下列方法有助于将死锁减至最少：

• 按同一顺序访问对象。
• 避免事务中的用户交互。
• 保持事务简短并处于一个批处理中。
• 使用较低的隔离级别。
• 使用基于行版本控制的隔离级别。
  
  
  • 将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON，使得已提交读事务使用行版本控制。
  • 使用快照隔离。
• 使用绑定连接。

按同一顺序访问对象

如果所有并发事务按同一顺序访问对象，则发生死锁的可能性会降低。例如，如果两个并发事务先获取 Supplier 表上的锁，然后获取 Part 表上的锁，则在其中一个事务完成之前，另一个事务将在 Supplier 表上被阻塞。当第一个事务提交或回滚之后，第二个事务将继续执行，这样就不会发生死锁。将存储过程用于所有数据修改可以使对象的访问顺序标准化。

避免事务中的用户交互

避免编写包含用户交互的事务，因为没有用户干预的批处理的运行速度远快于用户必须手动响应查询时的速度（例如回复输入应用程序请求的参数的提示）。例如，如果事务正在等待用户输入，而用户去吃午餐或甚至回家过周末了，则用户就耽误了事务的完成。这将降低系统的吞吐量，因为事务持有的任何锁只有在事务提交或回滚后才会释放。即使不出现死锁的情况，在占用资源的事务完成之前，访问同一资源的其他事务也会被阻塞。

保持事务简短并处于一个批处理中

在同一数据库中并发执行多个需要长时间运行的事务时通常会发生死锁。事务的运行时间越长，它持有排他锁或更新锁的时间也就越长，从而会阻塞其他活动并可能导致死锁。

保持事务处于一个批处理中可以最小化事务中的网络通信往返量，减少完成事务和释放锁可能遭遇的延迟。

使用较低的隔离级别

确定事务是否能在较低的隔离级别上运行。实现已提交读允许事务读取另一个事务已读取（未修改）的数据，而不必等待第一个事务完成。使用较低的隔离级别（例如已提交读）比使用较高的隔离级别（例如可序列化）持有共享锁的时间更短。这样就减少了锁争用。

使用基于行版本控制的隔离级别

如果将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON，则在已提交读隔离级别下运行的事务在读操作期间将使用行版本控制而不是共享锁。

注意：
某些应用程序依赖于已提交读隔离的锁定和阻塞行为。对于这些应用程序，要启用此选项必须进行一些更改。

 

快照隔离也使用行版本控制，该级别在读操作期间不使用共享锁。必须将 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON，事务才能在快照隔离下运行。

实现这些隔离级别可使得在读写操作之间发生死锁的可能性降至最低。

使用绑定连接

使用绑定连接，同一应用程序打开的两个或多个连接可以相互合作。可以像主连接获取的锁那样持有次级连接获取的任何锁，反之亦然。这样它们就不会互相阻塞。

 

sqlServer 2005

ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.chs/udb9/html/ed7ac51e-a526-41cc-8133-ad8b7ebeaf4a.htm

 

============================

嵌套事务

 

显式事务可以嵌套。这主要是为了支持存储过程中的一些事务，这些事务可以从已在事务中的进程调用，也可以从没有活动事务的进程中调用。

下列示例显示了嵌套事务的用途。TransProc 过程强制执行其事务，而不管执行事务的进程的事务模式。如果在事务活动时调用 TransProc，很可能会忽略 TransProc 中的嵌套事务，而根据对外部事务采取的最终操作提交或回滚其 INSERT 语句。如果由不含未完成事务的进程执行 TransProc，则在该过程结束时，COMMIT TRANSACTION 将有效地提交 INSERT 语句。

 

嵌套事务破坏了事务的原子性，一个大的单元内有一个独立的小的单元，内部的事务的提交与rollback要根据后面来，所以内部的提交不是真正意义的提交

 

嵌套事务的问题

http://www.cnblogs.com/montaque/archive/2005/03/12/117410.html

 

嵌套事务有几个特征, 帮助中这么说;

Microsoft® SQL Server™ 忽略提交内部事务。根据最外部事务结束时采取的操作，将提交或者回滚事务。如果提交外部事务，则内层嵌套的事务也会提交。如果回滚外部事务，则不论此前是否单独提交过内层事务，所有内层事务都将回滚。

对 COMMIT TRANSACTION 或 COMMIT WORK 的每个调用都应用于最后执行的 BEGIN TRANSACTION。如果嵌套 BEGIN TRANSACTION 语句，那么 COMMIT 语句只应用于最后一个嵌套的事务，也就是在最内层的事务。即使嵌套事务内部的 COMMIT TRANSACTION transaction_name 语句引用外部事务的事务名，该提交也只应用于最内层的事务。

ROLLBACK TRANSACTION 语句的 transaction_name 参数引用一组命名的嵌套事务的内层事务是非法的，transaction_name 只能引用最外部事务的事务名。如果在一组嵌套事务的任意级别执行使用外部事务名称的 ROLLBACK TRANSACTION transaction_name 语句，那么所有的嵌套事务都将回滚。如果在一组嵌套事务的任意级别执行没有 transaction_name 参数的 ROLLBACK WORK 或 ROLLBACK TRANSACTION 语句，那么它将回滚所有嵌套事务，包括最外部事务。

@@TRANCOUNT 函数记录当前事务的嵌套级。每个 BEGIN TRANSACTION 语句使 @@TRANCOUNT 加 1。每个 COMMIT TRANSACTION 或 COMMIT WORK 语句使 @@TRANCOUNT 减 1。没有事务名的 ROLLBACK WORK 或 ROLLBACK TRANSACTION 语句将回滚所有嵌套事务，并使 @@TRANCOUNT 减小到 0。使用一组嵌套事务中最外部事务的事务名称的 ROLLBACK TRANSACTION 将回滚所有嵌套事务，并使 @@TRANCOUNT 减到 0。在无法确定是否已经在事务中时，可以用 SELECT @@TRANCOUNT 语句确定 @@TRANCOUNT 是 1 还是更大。如果 @@TRANCOUNT 是 0，则表明不在事务中。

 

======================

SQL 死锁两个例子及分析，

http://idai.blogbus.com/logs/52798593.html

use master --必须在master数据库中创建
go

if exists (select * from dbo.sysobjects where id = object_id(N [dbo].[p_lockinfo] ) and OBJECTPROPERTY(id, N IsProcedure ) = 1)
drop procedure [dbo].[p_lockinfo]
GO

/*--处理死锁

查看当前进程,或死锁进程,并能自动杀掉死进程

因为是针对死的,所以如果有死锁进程,只能查看死锁进程
当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程

感谢: caiyunxia,jiangopen 两位提供的参考信息

--邹建 2004.4--*/

/*--调用示例

exec p_lockinfo
--*/
create proc p_lockinfo
@kill_lock_spid bit=1,  --是否杀掉死锁的进程,1 杀掉, 0 仅显示
@show_spid_if_nolock bit=1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示
as
declare @count int,@s nvarchar(1000),@i int
select id=identity(int,1,1),标志,
进程ID=spid,线程ID=kpid,块进程ID=blocked,数据库ID=dbid,
数据库名=db_name(dbid),用户ID=uid,用户名=loginame,累计CPU时间=cpu,
登陆时间=login_time,打开事务数=open_tran, 进程状态=status,
工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess,
域名=nt_domain,网卡地址=net_address
into #t from(
select 标志= 死锁的进程 ,
  spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,
  status,hostname,program_name,hostprocess,nt_domain,net_address,
  s1=a.spid,s2=0
from master..sysprocesses a join (
  select blocked from master..sysprocesses group by blocked
  )b on a.spid=b.blocked where a.blocked=0
union all
select  |_牺牲品_> ,
  spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,
  status,hostname,program_name,hostprocess,nt_domain,net_address,
  s1=blocked,s2=1
from master..sysprocesses a where blocked<>0
)a order by s1,s2

select @count=@@rowcount,@i=1

if @count=0 and @show_spid_if_nolock=1
begin
insert #t
select 标志= 正常的进程 ,
  spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time,
  open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address
from master..sysprocesses
set @count=@@rowcount
end

if @count>0
begin
create table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(255))
if @kill_lock_spid=1
begin
  declare @spid varchar(10),@标志 varchar(10)
  while @i<=@count
  begin
  select @spid=进程ID,@标志=标志 from #t where id=@i
  insert #t1 exec( dbcc inputbuffer( +@spid+ ) )
  if @标志= 死锁的进程  exec( kill  +@spid)
  set @i=@i+1
  end
end
else
  while @i<=@count
  begin
  select @s= dbcc inputbuffer( +cast(进程ID as varchar)+ )  from #t where id=@i
  insert #t1 exec(@s)
  set @i=@i+1
  end
select a.*,进程的SQL语句=b.EventInfo
from #t a join #t1 b on a.id=b.id
end
go

 

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

 

 事务隔离

事务准备接受不一致数据的级别称为隔离级别。隔离级别是一个事务必须与其它事务进行隔离的程度。较低的隔离级别可以增加并发，但代价是降低数据的正确性。相反，较高的隔离级别可以确保数据的正确性，但可能对并发产生负面影响。应用程序要求的隔离级别确定了 SQL Server 使用的锁定行为。

SQL-92 定义了下列四种隔离级别，SQL Server 支持所有这些隔离级别：

• 未提交读（事务隔离的最低级别，仅可保证不读取物理损坏的数据）。
• 提交读（SQL Server 默认级别）。
• 可重复读。
• 可串行读（事务隔离的最高级别，事务之间完全隔离）。

如果事务在可串行读隔离级别上运行，则可以保证任何并发重叠事务均是串行的。

下面四种隔离级别允许不同类型的行为。

隔离级别	脏读	不可重复读取	幻像
未提交读	是	是	是
提交读	否	是	是
可重复读	否	否	是
可串行读	否	否	否

 

 http://oss.org.cn/ossdocs/postgresql/postgresql-doc-8.0-zh_CN/transaction-iso.html

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

 

 

线程-

  SET LOCK_TIMEOUT 2000 --自已不可能成为死锁源，具有排错的功能，自已成为死锁的牺牲品，
Begin Tran
  Update Lock2 Set C1=C1+1;
  WaitFor Delay '00:00:10';
  SELECT * FROM Lock1
Rollback Tran;

 

--线程二

Begin Tran
  Update Lock1 Set C1=C1+1;
  WaitFor Delay '00:00:30'; --进入循环等待
  SELECT * FROM Lock2
Rollback Tran;

 

 

共享锁share(s)，排它锁exclud(x)，ix意向排它锁, is，意向的解决效率问题（多粒度，树级），还有占位，优化级等，

sql锁，资源列表的以上四种锁，使用exec sp_lock，select * from sys.dm_tran_locks可以查看

 

总结死锁的解决方案：

防：

跨度小，时间占用短，简单，否同时时占用多个资源，容易发死没（广度）

粒度小，资料占用少，死锁后损失也小（深度）

隔离级别小，允许不一致，允许别人插一脚，但提高了并发量，拿不一致来换并发量，default无法保证完全的一致，不能保证串行化（执行结果就如同一个接一个的执行一样，没有

并发），但可能提高并发性(s)

程序执行的频率，并发(提高资源的利用率，使公共的执行时间更短)度高，发生死锁的机率大

 

s锁（select）读锁

x(update, insert, delete)写锁

 

操作系统中的信号量，资料的占用情况列表

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

读书过后，留下那么一点点，不会忘记的，就是素质

什么都不记得了，自已总结，推理，得出来的东西与正确答案，这就是素质