Semaphore

Question

Semaphore

SQL Guy 1

SSCoach

Points: 15979
More actions
January 27, 2011 at 12:50 pm

#232895

I recently was asked on interview what is semaphore, I could not answer it. Can someone explain it or post s link to ?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

http://www.google.com/search?q=what+is+a+semaphore

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 2

Thanks for "smart" reply, GilaMonster. I also could have gone to Google and search for it before posting here, but all that links give only very general explanation of it, related to unix, general programming, etc.

Why I posted here is that I expected an explanation of semaphore specifically related to SQL SERVER. For nearly every question you can post here ...google/search=what+is+.... but why this forum exists is that something specific cannot be found with general google search. Otherwise, why to use this forum at all ?

Your reply did not provide anything

Lowell SSC Guru Points: 323518 More actions · Answer 3

in the past, i've used a rowversion as a semaphore column to designate that a row has not changed since the last time it was downloaded.

in some rare cases where the GUI is going to update a record, and needs to be the only one touching that record, I've stuckgotten the rowversion / semaphore column, so other instances select where that [semaphore] ={currentvalue}, and the update does WHERE PK = X and [semaphore] = {what was downloaded}

so the GUI selected the record, and either updates it because the criteria matched, or raises an error because teh row changed by something else (because of the behaviour of the [rowversion].

an obvious example might be important fiscal records...say Betty and Karen were in a meeting and were told to change a specific dollar value... they both might download the same record via a web or GUI, even minutes apart....but if one completes the work AFTER the other, the second update would fail, since the rowversion doesn't match what you compare to....it'd be zero rows updated, and you have to test for that, since that's not an error, but that's one way i've used it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Well, you asked what a semaphore is and the very first link that the search returns is the wikipedia article on semaphores in computing. So, do you understand what a semaphore is in general then?

Semaphores in SQL are the same as semaphores in unix, general programming, etc. Structures used to control access to shared resources in multi-threaded scenarios, typically memory. Main usage in SQL that you'll notice is in memory grants to queries. There's little documentation on them as they are internal structures, not something that users interact with.

If you have specific questions, please ask. General questions get general answers because we can't read your mind.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 5

Thanks both Lowel and GilaMonster, now it's little bit clear for me.

As I understood correctly, semaphors can be used by developers for row versioning, and is used by SQL Server as a sort of latches. I am more interested in second utilization of semaphores, as my interviewer asked me questions about internal SQL Structures. I also found a DMV sys.dm_exec_query_resource_semaphores, but there is not much explanation in BOL about it.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

They're not latches, but similar kind of idea, protection of shared resources. As I said, very very little info available as they're internal structures. None of the internals/troubleshooting books I have even mention them.

Very surprised an interviewer would ask, unless he was trying to see how you'd handle a question that you didn't know the answer to.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 7

I handled this question in a stupid way. Since this position also assumes some Oracle (but not required) and I never heard about semaphores in SQL Server, I answered that it must be something in Oracle.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

When I do interviews I always include one 'unanswerable' question. Just to see if the candidate will admit that he doesn't know or if he's going to make up some garbage explanation and try to fool me. The vast majority of people I've interviewed have done the latter.

p.s. Oracle does use semaphores, when installed on *nix

http://www.orafaq.com/node/8

If the position did need Oracle on *nix, that's far more likely what he was looking for, the semaphores in *nix

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass