CODEWITHSUNDEEP
sqlsql-servert-sql
Paweł Halicki
Paweł Halicki

Reputation: 90

TSQL partial lock of table (but not for other applications)

We have a application that allows users to do something on database (it's not important), and then send notifications with use of 3rd party software (it can only read data from DB with select statement).
Notifications are handled by two SQL stored procedures, 1st adds notifications to Notifications table (many notifications can be added prior to sending them), and 2nd sends them using this code:

EXEC master.dbo.xp_cmdshell 'c:\send.cmd';  
WAITFOR DELAY '00:00:05';  
DELETE FROM Notifications;

The problem is with 2nd procedure - sometimes it happens that 2 users trigger sending messages at the same time, resulting in sending whole content of Notifications table to recipients twice.

I wanted to use something like locking Notifications table, but the table should be readable by 3rd party software executed in first line of code. Or saying it in other words - I would like to allow only one instance of SendMsg procedure to be executed at a time (leaving Notifications table read-only for other procedures).
Any ideas how can i achieve this?

Upvotes: 0

Views: 322

Answers (3)

Ben
Ben

Reputation: 35613

EDIT: See David T Macknet's answer which is much better for MSSQL 2005 upwards. This technique may still be useful on other DBMS or earlier versions of SQL server though.

You can do this using a second table to hold the fact of whether the table is locked.

create table Notifications_Lock(
ix int primary key,
fLocked bit,
constraint Notifications_Lock_SingleRow check (ix = 1)
)

insert Notifications_Lock values( 1, 0, null)

go

create proc NotificationsLockTry
as
begin
    update Notifications_Lock set fLocked = 1 
    from Notifications_Lock with (TABLOCKX)
    where fLocked = 0
    return @@rowcount
end
go
create proc NotificationsLockTimeout( @waitSeconds int)
as
begin
    set @waitSeconds = isnull(@waitSeconds, 0)

    declare @dtWaitTill datetime
    set @dtWaitTill = dateadd(second, @waitSeconds, getutcdate())
    declare @fLocked int

    update Notifications_Lock set fLocked = 1 
    from Notifications_Lock with (TABLOCKX)
    where fLocked = 0
    set @fLocked = @@rowcount

    if @fLocked > 0  return @fLocked

    while @fLocked = 0 And @dtWaitTill > getutcdate()
    begin
        waitfor delay '00:00:01'
        update Notifications_Lock set fLocked = 1 
        from Notifications_Lock with (TABLOCKX)
        where fLocked = 0
        set @fLocked = @@rowcount
        if @fLocked > 0 return @fLocked
    end
    return @fLocked
end
go
create proc NotificationsUnlock
as
begin 
    update Notifications_Lock set fLocked = 0, dtLocked = null
    from Notifications_Lock with (TABLOCKX)
    where fLocked = 1
    return @@rowcount
end

Example usage:

declare @fLocked int
-- Wait up to 5 minutes for a lock
exec @fLocked = NotificationsLockTry 300
if @fLocked = 0 
begin
    raiserror('Unable to lock notifications table', 11,11)
    return
end
-- Locked OK
-- INSERT NOTIFICATIONS HERE
-- CALL Send.cmd HERE
exec NotificationsUnlock

return

Be aware that if using this method, if the job quits or is interrupted, you may have to manually unlock the Notifications table.

You can also add a timeout by having the date locked added to the table, then you can check this periodically and if it is too far in the past, just unlock it.

Upvotes: 0

David T. Macknet
David T. Macknet

Reputation: 3162

use sp_getapplock and sp_releaseapplock to give yourself a mutex. See Application Locks (or Mutexes) in SQL Server 2005 for more detail. There's no harm in having a second process wait to get a lock, provided your external command is fast enough. You'd also be able to remove your WAITFOR DELAY, as everything would be governed by the app lock.

Upvotes: 2

Ben
Ben

Reputation: 35613

You need to add an additional field to the notifications table, indicating which batch of notifications it is.

create table notifications(
   batchID uniqueidentifier,
   text nvarchar(max)

)

So AddNotification will take an additional GUID parameter which is the batch. Then your sending proc should do this:

declare @cmd nvarchar(max)
set @cmd = 'c:\send.cmd' + ' ' + cast(@batchID as varchar(36))

EXEC master.dbo.xp_cmdshell @cmd;  
--- WAITFOR DELAY '00:00:05';  
-- What are you waiting for? xp_cmdshell waits already.
-- You can also induce CMD.exe to wait for windows programs using the START /WAIT option
DELETE FROM Notifications where batchID = @batchID;

Obviously send.cmd needs to be modified to take the same GUID as a parameter.

If you cannot add a parameter to the stored procedures, using the SPID (@@spid) may work, provided you can be sure the same underlying connection (from the connection pool) is used for all the stored procedures. You will still have to add a parameter to send.cmd though.

Upvotes: 0

Related Questions