SQL server data locking approach by datetime

Question

see this post https://stackoverflow.com/a/11334964

create table PendingQueue (
  id int not null,
  DueTime datetime not null,
  Payload varbinary(max),
  cnstraint pk_pending_id nonclustered primary key(id));

create clustered index cdxPendingQueue on PendingQueue (DueTime);
go

create procedure usp_enqueuePending
  @dueTime datetime,
  @payload varbinary(max)
as
  set nocount on;
  insert into PendingQueue (DueTime, Payload)
    values (@dueTime, @payload);
go

create procedure usp_dequeuePending
  @batchsize int = 100,
  @retryseconds int = 600
as
  set nocount on;
  declare @now datetime;
  set @now = getutcdate();
  with cte as (
    select top(@batchsize) 
      id,
      DueTime,
      Payload
    from PendingQueue with (rowlock, readpast)
    where DueTime < @now
    order by DueTime)
  update cte
    set DueTime = dateadd(seconds, @retryseconds, DueTime)
    output deleted.Payload, deleted.id;
go

this concept is not clear to me set DueTime = dateadd(seconds, @retryseconds, DueTime)
why they add 600 second with DueTime ?

please tell me what they are trying to do which prevent two session to access same data. please explain the above code. thanks

Edit

Still i have some confusion. you said Define @retryseconds as 600 seconsds means that on failure, or on crash, it would be retries automatically in 10 minutes. This was defined manually, you could change this value according to your own requirement.

when we update data then we can not mention if update fail then again try to update same data after 10 minute.

this sql set DueTime = dateadd(seconds, @retryseconds, DueTime) try to update DueTime field. then why are you saying it will update DueTime field after 10 minute. this point is not clear to me. please explain for for what you said here. Define @retryseconds as 600 seconsds means that on failure, or on crash, it would be retries automatically in 10 minutes. This was defined manually, you could change this value according to your own requirement.

Thanks

Answer 1

Hi @Sudip Bhatt ,

Thanks for your update.

Define @retryseconds as 600 seconsds means that on failure, or on crash, it would be retries automatically in 10 minutes. This was defined manually, you could change this value according to your own requirement.

I meant that the answerer defined the @retryseconds as 600 seconds which was 10 minutes. If you use this query or script in your own environment, you could define it yourself, like 800 seconds, 1000 seconds and so on.

set DueTime = dateadd(seconds, @retryseconds, DueTime)

DATEADD is a function which adds a specified number value (as a signed integer) to a specified datepart of an input date value, and then returns that modified value.

So above statement means adding 10 minutes to DueTime. For example, the DueTime is '2020-01-10 12:30:00', then after executing 'set DueTime = dateadd(seconds, @retryseconds, DueTime)', the DueTime will be updated as '2020-01-10 12:40:00'.

If you still have any concern or confuse, please let me know. Thanks.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Sudip Bhatt ,

• READPAST

Specifies that the Database Engine not read rows that are locked by other transactions. When READPAST is specified, row-level locks are skipped but page-level locks are not skipped. That is, the Database Engine skips past the rows instead of blocking the current transaction until the locks are released.

For example, assume table T1 contains a single integer column with the values of 1, 2, 3, 4, 5. If transaction A changes the value of 3 to 8 but has not yet committed, a SELECT * FROM T1 (READPAST) yields values 1, 2, 4, 5.

• ROWLOCK

Specifies that row locks are taken when page or table locks are ordinarily taken.

Using rowlock, readpast will lock current top 100 (@batchsize) rows and will skip the rows which are blocked by other session.

Define @retryseconds as 600 seconsds means that on failure, or on crash, it would be retries automatically in 10 minutes. This was defined manually, you could change this value according to your own requirement.

set DueTime = dateadd(seconds, @retryseconds, DueTime)

Then above query means that on failure, or on crash, the duetime of all the top 100 rows selected will be automatically added 10 more minutes.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.