[轉]數據庫訪問並發問題

在數據庫訪問時。如果處理並發訪問的問題
或者當一個操作員對一個對像作讀操作時。另一個操作員對此對像作寫操作的時候
如何避免死鎖發生

/**********  加鎖   ***************
設table1(A,B,C)
A    B    C
a1   b1   c1
a2   b2   c2
a3   b3   c3

1）排它鎖
新建兩個連接
在第一個連接中執行以下語句

begin tran
   update table1
   set A='aa'
   where B='b2'
   waitfor delay '00:00:30'  --等待30秒
commit tran

在第二個連接中執行以下語句

begin tran
   select * from table1
   where B='b2'
commit tran

若同時執行上述兩個語句，則select查詢必須等待update執行完畢才能執行即要等待30秒

2）共享鎖
在第一個連接中執行以下語句

begin tran
   select * from table1 holdlock -holdlock人為加鎖
   where B='b2'
   waitfor delay '00:00:30'  --等待30秒
commit tran

在第二個連接中執行以下語句

begin tran
   select A,C from table1
   where B='b2'
   update table1
   set A='aa'
   where B='b2'
commit tran

若同時執行上述兩個語句，則第二個連接中的select查詢可以執行
而update必須等待第一個連接中的共享鎖結束後才能執行 即要等待30秒

3）死鎖
增設table2(D,E)
D    E
d1   e1
d2   e2
在第一個連接中執行以下語句

begin tran
   update table1
   set A='aa'
   where B='b2'
   waitfor  delay '00:00:30'
   update table2
   set D='d5'
   where E='e1'
commit tran

在第二個連接中執行以下語句

begin tran
   update table2
   set D='d5'
   where E='e1'
   waitfor  delay '00:00:10'
   update table1
   set A='aa'
   where B='b2'
commit tran

同時執行，系統會檢測出死鎖，並中止進程
--------------------------------------------------------------
SET IMPLICIT_TRANSACTIONS  ON --用戶每次必須顯式提交或回滾。否則當用戶斷開連接時,
                              --事務及其所包含的所有數據更改將回滾

SET IMPLICIT_TRANSACTIONS  OFF --自動提交模式。在自動提交模式下，如果各個語句成功
                               --完成則提交。
1:如上

2: 如何鎖一個表的某一行


A 連接中執行

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

begin tran

select * from tablename with (rowlock) where id=3

waitfor delay '00:00:05'

commit tran

B連接中如果執行

update tablename set colname='10' where id=3 --則要等待5秒

update tablename set colname='10' where id<>3 --可立即執行

2 鎖定數據庫的一個表

SELECT * FROM table WITH (HOLDLOCK)

注意: 鎖定數據庫的一個表的區別

SELECT * FROM table WITH (HOLDLOCK)

其他事務可以讀取表，但不能更新刪除

SELECT * FROM table WITH (TABLOCKX)

其他事務不能讀取表,更新和刪除


SELECT 語句中「加鎖選項」的功能說明
  SQL Server提供了強大而完備的鎖機制來幫助實現數據庫系統的並發性和高性能。用戶既能使用SQL Server的缺省設置也可以在select 語句中使用「加鎖選項」來實現預期的效果。 本文介紹了SELECT語句中的各項「加鎖選項」以及相應的功能說明。
  功能說明：　
  NOLOCK（不加鎖）
  此選項被選中時，SQL Server 在讀取或修改數據時不加任何鎖。 在這種情況下，用戶有可能讀取到未完成事務（Uncommited Transaction）或回滾(Roll Back)中的數據, 即所謂的「髒數據」。

  HOLDLOCK（保持鎖）
  此選項被選中時，SQL Server 會將此共享鎖保持至整個事務結束，而不會在途中釋放。

  UPDLOCK（修改鎖）
  此選項被選中時，SQL Server 在讀取數據時使用修改鎖來代替共享鎖，並將此鎖保持至整個事務或命令結束。使用此選項能夠保證多個進程能同時讀取數據但只有該進程能修改數據。

  TABLOCK（表鎖）
  此選項被選中時，SQL Server 將在整個表上置共享鎖直至該命令結束。 這個選項保證其他進程只能讀取而不能修改數據。

  PAGLOCK（頁鎖）
  此選項為默認選項， 當被選中時，SQL Server 使用共享頁鎖。

  TABLOCKX（排它表鎖）
  此選項被選中時，SQL Server 將在整個表上置排它鎖直至該命令或事務結束。這將防止其他進程讀取或修改表中的數據。

---

--或者自己加鎖(控制更靈活)

--鎖定記錄,只允許單用戶修改的例子:

--創建測試環境
--創建測試表--部門表
create table 部門(departmentid int,name varchar(10))

--記錄鎖定表
create table lock(departmentid int,dt datetime)

go
--因為函數中不可以用getdate,所以用個視圖,得到當前時間
create view v_getdate as select dt=getdate()
go
--創建自定義函數,判斷記錄是否鎖定
create function f_chk(@departmentid int)
returns bit
as
begin
declare @re bit,@dt datetime
select @dt=dt from v_getdate
if exists(select 1 from lock where departmentid=@departmentid
and datediff(ss,dt,@dt)<5)
set @re=1
else
set @re=0
return(@re)
end
go

--數據處理測試
if dbo.f_chk(3)=1
print '記錄被鎖定'
else
begin
--也可以是查詢
begin tran
insert into lock values(3,getdate())
update 部門 set name='A' where departmentid=3
delete from lock where departmentid=3
commit tran
end

--刪除測試環境
drop table 部門
drop view v_getdate
drop function f_chk

如果是死鎖可以查一下：1：sp_who 或 sp_who22: Select * from sysprocesses where blocked <> 03: 企業管理器－＞服務器－＞管理工具－＞活動－＞當前活動 然後把他kill掉。。。   進程信息中，如果發現旁邊有一個鎖狀的圖標，就表明這個進程是死鎖，kill掉4：SQL事件探查器，監控一下,看主要是那些處理引起的死鎖.然後做相應的處理.用事件探查器new一個trace，監視一下造成你sqlserver停頓的情況。。。最好的辦法還是檢查一下引起鎖的原因，一般是由你的代碼引起的。