Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using a TSQL semaphore in SSIS Expand / Collapse
Author
Message
Posted Sunday, January 13, 2013 10:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 7:35 AM
Points: 33, Visits: 291
Comments posted to this topic are about the item Using a TSQL semaphore in SSIS
Post #1406572
Posted Sunday, January 13, 2013 10:54 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 8:26 AM
Points: 109, Visits: 490
Any particular reason that sp_getapplock wasn't used for this implementation?

http://msdn.microsoft.com/en-us/library/ms189823.aspx
Post #1406573
Posted Monday, January 14, 2013 2:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 7:35 AM
Points: 33, Visits: 291
Yes, you are right, built-in sp_getapplock could be used in my solution instead of the user-defined semaphore table drop_fi_semaphore
, but the structure of the SSIS package would remain unchanged .
The only changed block will be Get Semaphore .
Post #1406616
Posted Monday, January 14, 2013 5:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 10:29 AM
Points: 3, Visits: 24
Thanks for the post.

A useful addition would be how to release the semaphore from within the package. A fairly common use case is to have a queue and want to hold the semaphore while you are selecting the next item from the queue.

Ill also check out spappLock

Thanks again.
Post #1406677
Posted Monday, January 14, 2013 6:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 7:35 AM
Points: 33, Visits: 291
Release the semaphore : just end the transaction that is holding the semaphore by a commit ( or rollback, is the same since no data are modified) ...
Post #1406680
Posted Monday, January 14, 2013 8:56 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 5, 2014 3:56 PM
Points: 54, Visits: 29
Two problems I see right off:
1. You may not want isolation level serializable for the whole package. That may cause unnecessary blocking in other processes.
2. if the data flow fails, the semaphore is not released.
Post #1406784
Posted Monday, January 14, 2013 12:00 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 12:21 PM
Points: 117, Visits: 572
Cade Roux (1/13/2013)
Any particular reason that sp_getapplock wasn't used for this implementation?

http://msdn.microsoft.com/en-us/library/ms189823.aspx

Thanks for pointing this out... My predecessor used a data update of all rows with a fake column value to get around this and the whole thing was a nasty can of worms. sp_getapplock is so clean!
Post #1406891
Posted Tuesday, January 15, 2013 2:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 7:35 AM
Points: 33, Visits: 291
Henry B. Stinson (1/14/2013)
Two problems I see right off:
1. You may not want isolation level serializable for the whole package. That may cause unnecessary blocking in other processes.

I suppose you are rigth , I should be enough to set isolation level serializable for the single components

2. if the data flow fails, the semaphore is not released.

If the data flow fails, the semaphore is released . The semaphore is designed in such a way, that cannot remain unreleased if the session terminates
The semaphore is acquired by putting a lock on a row :
begin tran
select * from drop_fi_semaforo with ( rowlock, UPDLOCK, holdlock) where Uno = '1'

There is nothing to commit or rollback , since data are not modified .
If the session is terminated, the lock is released, the DBMS takes care of it


Post #1407096
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse