SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Mutexes in SQL


Mutexes in SQL

Author
Message
Toby Harman
Toby Harman
Right there with Babe
Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)

Group: General Forum Members
Points: 717 Visits: 668
@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.
AmolNaik
AmolNaik
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1145 Visits: 1234
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
werner.broser
werner.broser
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 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
Toby Harman
Toby Harman
Right there with Babe
Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)

Group: General Forum Members
Points: 717 Visits: 668
@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!
werner.broser
werner.broser
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 Visits: 166
@Toby

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

kr/Werner
seregak76@mail.ru
seregak76@mail.ru
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 669
@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.
sgtwilko
sgtwilko
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 177
@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



Toby Harman
Toby Harman
Right there with Babe
Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)

Group: General Forum Members
Points: 717 Visits: 668
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search