表A有字段AccountID(帐户ID)和BalanceMoney(余额)有很多的业务表的关联数据会更新该余额,如余额减少在操作余额时,首先会检测余额是否充足,若充足则会更数据,否则返回错误并提示余额不足,如现有余额为100元
declare @v money
begin tranSELECT @v=BalanceMoney FROM A with(rowlock) WHERE AccountID=1if(@v>100)
begin
update A set BalanceMoney=Isnull(BalanceMoney,0)-80 where AccountID=1
endwaitfor delay '00:00:10' --等待10秒 假设执行10秒才会完成更新
commit tran
采用行锁并不能阻止其他会话读取,只会防更新因此,无法确保下面的数据是正确的,导致更新时造成出错,如出现负数,因为未更新前,所以会话都能读取检测值为100,只是未更新而以 SELECT @v=BalanceMoney FROM A with(rowlock) WHERE AccountID=1
如何确保更新前检测所得的数据是准确的或锁定其他会话读取?
declare @v money
begin tranSELECT @v=BalanceMoney FROM A with(rowlock) WHERE AccountID=1if(@v>100)
begin
update A set BalanceMoney=Isnull(BalanceMoney,0)-80 where AccountID=1
endwaitfor delay '00:00:10' --等待10秒 假设执行10秒才会完成更新
commit tran
采用行锁并不能阻止其他会话读取,只会防更新因此,无法确保下面的数据是正确的,导致更新时造成出错,如出现负数,因为未更新前,所以会话都能读取检测值为100,只是未更新而以 SELECT @v=BalanceMoney FROM A with(rowlock) WHERE AccountID=1
如何确保更新前检测所得的数据是准确的或锁定其他会话读取?
AccountID这里加索引update A set BalanceMoney更新时也要加锁,一直保持
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 将在整个表上置排它锁直至该命令或事务结束。这将防止其他进程读取或修改表中的数据。
if OBJECT_ID('tab') is not null
drop table tab;
go
create table tab (id int identity, c char(1));
go
insert into tab(c) values('a');
go-- 两个会话中的事务同时对 id=1 行的数据进行更改,但是会话 1 中的事务先一步检查数据是否满足更新的要求。
-- 操作:先运行会话 1 中的事务,紧跟着运行会话 2 中的事务。-- 会话 1
declare @c char(1);
begin tran
select @c=c from tab with(updlock) where id=1;
waitfor delay '00:00:05';
if @c='a' update tab set c='1' where id=1;
commit tran
go-- 会话 2
declare @c char(1);
begin tran
select @c=c from tab with(updlock) where id=1;
if @c='a' update tab set c='2' where id=1;
commit tran
go-- 运行的结果,会话 1 更新成功。
select * from tab;
/*
1 1
*/