场景:多个客户端并发,同时调用同一个存储过程,如何处理,使之查询数据不会出现重复?
解决方法:
在查询的表上使用排它锁
存储过程示例:
CREATE PROCEDURE id_test @deal_no varchar(10) output, @getno_date varchar(50) output AS declare @id int declare @curr_id int --开始事务 BEGIN TRANSACTION select @curr_id = isnull(curr_id,0) + 1 from tb_sm_id with (TABLOCKX) --查询时加排他锁 --延迟100毫秒再更新 WAITFOR DELAY '00:00:00.100' update tb_sm_id set curr_id = @curr_id set @deal_no = 'A' + right('000000' + cast(@curr_id as varchar(10)),5) select @id = isnull(max(id),0) + 1 from tb_sm_data with (TabLOCKx)--查询时加排他锁 insert into tb_sm_data(id,deal_no) values(@id,@deal_no) declare @update_date datetime declare @ms int --查询datetime select @update_date = update_date from tb_sm_data where id = @id select @ms = datepart(ms,@update_date) --转换成带毫秒的字符串日期 set @getno_date = convert(varchar(20),@update_date,120) + '.' + right('000' +cast(@ms as varchar(3)),3) --提交事务 COMMIT TRANSACTION
两个表:
CREATE TABLE [dbo].[tb_sm_id] ( [curr_id] [int] NULL ) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tb_sm_data] ( [id] [int] NULL , [deal_no] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [update_date] [datetime] NOT NULL ) ON [PRIMARY] GO
附录 锁定提示
可以使用 SELECT、INSERT、UPDATE 和 DELETE 语句指定表级锁定提示的范围,以引导 Microsoft® SQL Server™ 2000 使用所需的锁类型。当需要对对象所获得锁类型进行更精细控制时,可以使用表级锁定提示。这些锁定提示取代了会话的当前事务隔离级别。
说明 SQL Server 查询优化器自动作出正确的决定。建议仅在必要时才使用表级锁定提示更改默认的锁定行为。禁止锁定级别反过来会影响并发。
锁定提示 | 描述 |
---|---|
HOLDLOCK | 将共享锁保留到事务完成,而不是在相应的表、行或数据页不再需要时就立即释放锁。HOLDLOCK 等同于 SERIALIZABLE。 |
NOLOCK | 不要发出共享锁,并且不要提供排它锁。当此选项生效时,可能会读取未提交的事务或一组在读取中间回滚的页面。有可能发生脏读。仅应用于 SELECT 语句。 |
PAGLOCK | 在通常使用单个表锁的地方采用页锁。 |
READCOMMITTED | 用与运行在提交读隔离级别的事务相同的锁语义执行扫描。默认情况下,SQL Server 2000 在此隔离级别上操作。 |
READPAST | 跳过锁定行。此选项导致事务跳过由其它事务锁定的行(这些行平常会显示在结果集内),而不是阻塞该事务,使其等待其它事务释放在这些行上的锁。READPAST 锁提示仅适用于运行在提交读隔离级别的事务,并且只在行级锁之后读取。仅适用于 SELECT 语句。 |
READUNCOMMITTED | 等同于 NOLOCK。 |
REPEATABLEREAD | 用与运行在可重复读隔离级别的事务相同的锁语义执行扫描。 |
ROWLOCK | 使用行级锁,而不使用粒度更粗的页级锁和表级锁。 |
SERIALIZABLE | 用与运行在可串行读隔离级别的事务相同的锁语义执行扫描。等同于 HOLDLOCK。 |
TABLOCK | 使用表锁代替粒度更细的行级锁或页级锁。在语句结束前,SQL Server 一直持有该锁。但是,如果同时指定 HOLDLOCK,那么在事务结束之前,锁将被一直持有。 |
TABLOCKX | 使用表的排它锁。该锁可以防止其它事务读取或更新表,并在语句或事务结束前一直持有。 |
UPDLOCK | 读取表时使用更新锁,而不使用共享锁,并将锁一直保留到语句或事务的结束。UPDLOCK 的优点是允许您读取数据(不阻塞其它事务)并在以后更新数据,同时确保自从上次读取数据后数据没有被更改。 |
XLOCK | 使用排它锁并一直保持到由语句处理的所有数据上的事务结束时。可以使用 PAGLOCK 或 TABLOCK 指定该锁,这种情况下排它锁适用于适当级别的粒度。 |
例如,如果将事务隔离级别设置为 SERIALIZABLE,并且在 SELECT 语句中使用表级锁定提示 NOLOCK,则键范围锁通常用于维护不采用可串行事务。
USE pubs
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
SELECT au_lname FROM authors WITH (NOLOCK)
GO
生成的锁是:
EXEC sp_lock
GO