It seems like I am going to write another none SQL stuff again. No. This is a real life scenario. I am writing an ETL for my customer to load data from a Sybase server running on Unix system. The database I am accessing is a standby log-shipped database which is restored 5 minutes. The user account the ETL is using has very limited right on Sybase Server. The Sybase DBA does not have SQL Server driver on the Unix system. This means that I can’t put log restore as part of my ETL and Sybase DBA does not have connectivity to SQL Server side. We need to coordinate those 2 processes that when one is running the other must not run.

Using sp_getapplock is a perfect solution but I failed to find any procedures in Sybase functionally equivalent to sp_getapplock in SQL Server.I have to create a binary Semaphore in Sybase so that both Sybase DBA and I can use the same mechanism to determine whether or not our processes can be started. I create a table called Semaphore in a user database in Sybase.

CREATE TABLE Semaphore(Owner VARCHAR(100) NOT NULL, Flag BIT NOT NULL)

This table only has one record. Whenever a process is started, either log-shipping or ETL application, it must check this table to ensure the running status of the other application by calling procedure AcquireSemaphore. When the Flag field is 1, someone, the value in the Owner field, is running. Other processes not owning it should either quit or wait until the flag is turned off. When the flag is 0, no one currently owns this flag. Applications can get the ownership of the semaphore. Owner field tells who is owning the flag. Only owner can switch the flag to zero.

CREATE PROCEDURE AcquireSemaphore @Owner VARCHAR(100)
AS
BEGIN
DECLARE @ReturnValue INT 
SELECT @ReturnValue = 0
BEGIN TRANSACTION
UPDATE Semaphore SET Flag = 1, Owner = @Owner WHERE Flag = 0
IF @@rowcount = 0
BEGIN
UPDATE Semaphore SET Flag = 1 WHERE Owner = @Owner
IF @@rowcount = 0
SELECT @ReturnValue = -1
END
  COMMIT TRANSACTION
  RETURN @ReturnValue
END
GO
CREATE PROCEDURE ReleaseSemaphore @Owner VARCHAR(100)
AS
  UPDATE Semaphore SET Flag = 0 WHERE Owner = @Owner
go

When a process who owns the semaphore is terminated before calling ReleaseSemaphore procedure, the semaphore status will persist and it will not be able to be acquired by other processes until the owner process starts again and releases it at the end.