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 ««12

Mutexes in SQL Expand / Collapse
Author
Message
Posted Thursday, August 26, 2010 4:46 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 7:46 PM
Points: 381, Visits: 535
@alen

The problem I am trying to resolve isn't so much that Job B must wait for Job A, and is dependant on the results of Job A. That would be a simple precedence.

We have two jobs that should not be run at the same time. Running Job B while Job A is running can lead to strange behaviour and incorrect results, so we want them to be aware of each other. Neither is particularly time critical, so we can afford to delay one and allow the other to finish.
Post #976019
Posted Monday, August 30, 2010 1:46 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 21, 2013 11:43 PM
Points: 945, Visits: 1,234
alen teplitsky

I agree with you. We have a centralized SQL Server that runs only SQL Jobs, it runs all our Import/Export/ODS ETL jobs. We leverage SSIS Packages and then these get called in sequence as a single Job or different jobs . Although the packages will point to different servers through the Connection Managers but they ultimately get called from a single centralized SQL Server.

Thanks,


Amol Naik
Post #977557
Posted Wednesday, September 01, 2010 5:44 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, April 05, 2012 3:10 AM
Points: 101, Visits: 166
As this mechanismn is transaction or session relevant,
you cannot use it over two processes.

Just a hint (was mssing in the story): You can check these locks with

select APPLOCK_MODE('dbo','MyMutex','Session')
select APPLOCK_TEST('dbo','MyMutex','Exclusive','Session') (using your parameters of course)

kr/Werner
Post #978667
Posted Wednesday, September 01, 2010 6:02 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 7:46 PM
Points: 381, Visits: 535
@Werner

Not sure what you mean by

As this mechanismn is transaction or session relevant,
you cannot use it over two processes.


You can create a Lock / Semaphore / Mutex with one process that is visible to another. That was the point of the article!

I hope I've mis-understood your post!
Post #979231
Posted Thursday, September 02, 2010 1:59 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, April 05, 2012 3:10 AM
Points: 101, Visits: 166
@Toby

you are totally right, it is visible in different processes.

kr/Werner
Post #979335
Posted Tuesday, March 08, 2011 1:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 2:34 PM
Points: 15, Visits: 590
@sgtwilko

I've implemented updlock as you suggest in your comment and I encountered duplicates that occur very rarely, and not every time when I run tests with many concurrent clients. I can not explain duplicates and believed it was a sure way to protect the concurrent reads of i_nextUID. But I gave up and tested sp_getapplock, which seems to work flawlessly. There will also be better concurrency if you have a WHERE clause.

SELECT @i_uid = i_nextUID FROM t_table
WHERE rowID=@rowID

Updlock escalates to table lock in my tests and only one client may update t_table at a time and everything is going relatively slowly. Instead I put @rowID as suffixes in the @Resource variable of sp_getapplock and simulate rowlock that does not escalate.
Post #1074636
Posted Tuesday, March 08, 2011 12:52 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 4:58 AM
Points: 27, Visits: 158
@seregak76

Hiya,

I would be very interested to have a little more information such as, SQL server version and SP, number of concurrent connections (also how many uids would be requested simultaneously) and how clients connect and execute the code (e.g., Clasic asp running a Stored proc via ADODB.command object, .net running via adhoc record set, etc).

If you still have it, I would also be interested to see the code that failed (PM if you don't want/can not publish it)

Please don't misunderstand my intentions; I'm not saying you've implemented it badly, nor am I contradicting your statement, but I am concerned for the applications that we have using this code as we've been using two versions of this code for many years (one version very similar to the example that generates the next unique number for the entire DB and the other that generates the next unique number for a user). I would like to know how it can fail so that we can adapt.

It may just be a matter of scale, or perhaps in my testing I never managed to get an escalation to table lock. I will have to go back and rebuild the testing code and see if I can locate the point of failure.

SgtWilko



Post #1075071
Posted Tuesday, March 08, 2011 4:44 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 7:46 PM
Points: 381, Visits: 535
Assuming SQL 2005+

It is theoretically possible that you could into trouble if you use the READ UNCOMMITTED transaction isolation level. You could potentially get dirty reads in the SELECT. That isn't a fault in the implementation of the locks, but rather that you have explicitly set options that do not honour locks properly.

If you are interested in having a unique numeric ID on a record, I would suggest that defining the column with IDENTITY and then using either an OUTPUT on your INSERT statement or SCOPE_IDENTITY to see the values.

As my original article outlined (at least I hope it did!) this technique is more about ensuring that task A and task B are aware of each other and don't interfere with each other. I'm sure there are examples of it in most businesses.
Post #1075232
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse