Mutexes in SQL

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Toby Harman » Mutexes in SQL

18 posts, Page 2 of 2

««1 2

Mutexes in SQL

Rate Topic

Display Mode

Topic Options

Author

Message

Toby Harman

Posted Thursday, August 26, 2010 4:46 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:38 PM
Points: 322, Visits: 477

@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

AmolNaik

Posted Monday, August 30, 2010 1:46 PM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 09, 2013 1:20 PM
Points: 936, Visits: 1,184

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

werner.broser

Posted Wednesday, September 01, 2010 5:44 AM

SSC-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

Toby Harman

Posted Wednesday, September 01, 2010 6:02 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:38 PM
Points: 322, Visits: 477

@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

werner.broser

Posted Thursday, September 02, 2010 1:59 AM

SSC-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

seregak76

Posted Tuesday, March 08, 2011 1:39 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:14 AM
Points: 15, Visits: 526

@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

sgtwilko

Posted Tuesday, March 08, 2011 12:52 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 13, 2013 5:38 AM
Points: 27, Visits: 153

@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

Toby Harman

Posted Tuesday, March 08, 2011 4:44 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:38 PM
Points: 322, Visits: 477

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 »

18 posts, Page 2 of 2

««1 2

Permissions