Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

John Huang’s Blog

John Huang, Microsoft Certified Master in SQL Server, SQL Server MVP, is an independent SQL Server consultant in Vancouver BC, Canada. He started using SQL Server for his projects in year 1994. He has architected and implemented many SQL and BI applications serving different industrial areas. He loves talking about SQL Server and discussing SQL Server technologies with others. He blogs at http://www.sqlnotes.info.

Create Semaphore in Sybase

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.

Share/Bookmark

Comments

Leave a comment on the original post [www.sqlnotes.info, opens in a new window]

Loading comments...