SQL Server锁知识

SQL Server 锁 LOCK

 

数据库引擎存储过程
SET TRANSACTION ISOLATION LEVEL

在多用户的情况下不免要进行并发控制。微软提供了锁机制。
这里锁分为两个部分，一个是锁的范围（行锁、页面锁、表锁），另一个是锁的粒度（共享锁、持有锁等）
服务器带宽，服务器费用，开发人员费用，运维费用，别的没有了吧

NOLOCK、ROWLOCK、UPDLOCK

1、NOLOCK 不加锁
可以查询到记录 （不管是否被锁住，都查询出数据）所以可能会发生读出脏数据的情况，把没有提交事务的数据也显示出来。

select * from test with(nolock)

　　

2、HOLDLOCK 保持锁

begin tran

select * from test with(holdlock) where id = 1

rollback

　　

3、ROWLOCK 行锁
使用 select * from dbo.A with(RowLock) WHRE a=1 这样的语句，系统是什么时候释放行锁呢？？
RowLock 在不使用组合的情况下是没有任何意义的，With(RowLock,UpdLock) 这样的组合才成立，查询出来的数据使用 RowLock 来锁定，当数据被Update的时候，或者回滚之后，锁将被释放。
4、UPDLOCK 更新锁，修改锁
优点： 允许读取数据（不阻塞其它事务）并在以后更新数据，同时确保自从上次读取数据后数据没有被更改。

begin tran
　　select * from WebManageUsers with (updlock) where LoginName = 'zzl'
　　waitfor delay '00:00:10'
　　update WebManageUsers set RealName = 'zzlreal11' where LoginName = 'zzl'
commit tran
go

begin tran
　　select * from SYS_DICT with (updlock) where FULLNAME = 'Z'
　　waitfor delay '00:00:10'
　　update SYS_DICT set FULLNAME = 'ZZ'
commit tran
go

begin transaction --开始一个事务
　　select Qty from myTable with (updlock) where Id in (1,2,3)
　　update myTable SET Qty = Qty - A.Qty from myTable as A inner join @_Table as B on A.ID = B.ID
commit transaction --提交事务
go

--案例

create procedure [dbo].[proc_sequence_select]
　　@count int,
　　@code varchar(20)
AS
begin
　　set nocount on
　　begin try
　　　　begin tran
　　　　　　if @count > 0 and @code is not null
　　　　　　　　begin
　　　　　　　　　　declare @sequence_value bigint
　　　　　　　  　　declare @start_value bigint
　　　　　　　　　　select @sequence_value = sequence_value from sys_sequence with (updlock) where sequence_code = @code
　　　　　　　　　　set @start_value = @sequence_value + 1
　　　　　　　　　　set @sequence_value = @sequence_value + @count
　　　　　　　　　　update sys_sequence set sequence_code=@sequence_value where sequence_code = @code
　　　　　　　　　　select @start_value start_value,@sequence_value stop_value
　　　　　　　　end
　　　　　　else
　　　　　　　　begin
　　　　　　　　　　raiserror ( '错误！', 16, 1)
　　　　　　　　end
　　　　commit tran
　　end try
　　begin catch
　　　　rollback tran
　　　　--错误消息
　　　　declare @msg nvarchar(max);
　　　　set @msg = error_message();
　　　　--insert into sys_error_message values( '错误类型',@msg, '错误数据', getdate());
　　　　raiserror ( @msg, 16, 1);
　　end catch;
end

　　

5、READPAST
被锁住的记录不能查询出来。

select * from test ip with(readpast)
select * from test with(readpast)

　　

6、TABLOCK 表锁

7、PAGLOCK 页锁

8、TABLOCKX 排它表锁
排它 (X) 锁可以防止并发事务对资源进行访问。其它事务不能“读取”或“修改”排它 (X) 锁锁定的数据。 

9、对应用程序资源设置锁
sp_getapplock

--锁定应用程序资源

begin tran;

    declare @result int;
    exec @result = sp_getapplock @Resource = 'Form1',@LockMode = 'Shared';

commit tran;

　　

10、为应用程序资源释放锁
sp_releaseapplock

--为应用程序资源解锁

exec sp_releaseapplock @DbPrincipal = 'dbo', @Resource = 'Form1';

　　

11、锁一个表的某一行

 

--可选参数【committed】【uncommitted】【read】【serializable】

set transaction isolation level read uncommitted

select * from test rowlock where id = 1

12、查询锁

select

request_session_id spid

,OBJECT_NAME(resource_associated_entity_id) tableName

from sys.dm_tran_locks where resource_type='OBJECT'

　　

13、解锁

declare @spid int

set @spid = 57 --锁表进程

declare @sql varchar(1000)

set @sql='kill '+cast(@spid as varchar)

exec(@sql)