这是我看了楼上各位的回复并查相关资料后写的获取第一条记录ID的存储过程,不知道有错误没有:create procedure dbo.click_Sp_ProcessId ( @id int output ) asset nocount on declare @localError int declare @localRowCount intbegin transactionset @id = (select top 1 fId from dbo.click_Requests with (updlock) where fIsDone = 0 order by fId)
select @localError = @@error, @localRowCount = @@rowcountif @localError <> 0 goto on_errorif @localRowCount = 0 set @id = 0if @id > 0 begin update dbo.click_Requests set fIsDone = 1 where fId = @id if @@error <> 0 or @@rowcount = 0 goto on_error endif @@trancount > 0 commit transaction set nocount off return 0on_error: if @@trancount > 0 rollback transaction set nocount off return -1
设如果表中有数字列(假设处理是为数字列加1)~标签列(假设0和1~1为已处理)~ID列
那么是不是只要找出某ID和(and)标签列为0的处理~~为数字列加1~~标签列改为1~~
不满足ID的或不满足标签的都不会再处理!!为什么加事务???
至于搂主的问题,可以通过加标志来处理
认同Haiwer(海阔天空)的观点,在存储过程中不论进行什么样的操作建议都加事务,已保证数据的一致性和完整性。
至于搂主的问题,可以通过加标志来处理
加事务有两个作用
1、处理失败的时候可以回滚,恢复记录原始状态
2、给正在处理的记录加行锁,防止其他用户读取这个行
---------------------------------------------------------------------------
这第二条说的是事务的隔离级别吗
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
(
@id int output
)
asset nocount on declare @localError int
declare @localRowCount intbegin transactionset @id = (select top 1 fId from dbo.click_Requests with (updlock) where fIsDone = 0 order by fId)
select @localError = @@error, @localRowCount = @@rowcountif @localError <> 0 goto on_errorif @localRowCount = 0 set @id = 0if @id > 0
begin
update dbo.click_Requests set fIsDone = 1 where fId = @id
if @@error <> 0 or @@rowcount = 0 goto on_error
endif @@trancount > 0 commit transaction
set nocount off
return 0on_error:
if @@trancount > 0 rollback transaction
set nocount off
return -1