1

I'm creating SQL mechanism (in MSSQL 2005) which allows only one user at time set the field in certain table. If this field is set, no one else can clear it (set to NULL). If not, this field is set to something unique for the user's session. As a good candidate master.dbo.sysprocesses.sid looks for me, but I'm not sure with it.

I need to ensure when the user's client application (which will use this mechanism) crashes, the field will be cleared so another users will be able to use it. I'm thinking about some clearing job, which will check if the user's session which set this field is active yet and if not, then clear it (of course only when the field is not NULL).

Is there a unique column in master.dbo.sysprocesses for each user session without reusing the same value later on ?

Thanks a lot

2 Answers2

1

spid identifies any connection/session. No more, no less. No other session will have that spid.

And you can use @@SPID for that instead rather than sysprocesses.

Edit: sid is not unique because a user can have several sessions ongoing.

I reckon we need more info to offer a suggestion.

I am concerned that your app will crash often enough to need this solution to clear such exclusive locking...

Edit2: login_time would be usable too based on comments

gbn
  • 422,506
  • 82
  • 585
  • 676
  • Thanks for the answer, but I can't use it for my purpose. The SPID which had some session (already terminated) can get another one later on, so my clearing job cannot recognize if it's me or the session before. –  May 04 '11 at 11:16
  • The problem is that it's not my application, I'm just writing extension plugins for it. And with the crash I mean that the hosted application closes itself when connection problem occurs (and this sometimes happens since some of the hosts uses wi-fi). And I need to ensure these locks for exclusive record modifications. So for that DB job I need some unique value for each session without reusing. –  May 04 '11 at 11:48
  • I got it, I can use `login_time`, it seems this is the only unique unrepeatable session identifier in that table. If you modify your answer I'll accept it, I won't accept my own answers :) –  May 04 '11 at 12:07
0

A spid is not unique. Sometimes I see 10 or more equal spids on my server (it happens because of parallelism).

Try spid + kpid.

Ben
  • 51,770
  • 36
  • 127
  • 149
user2008528
  • 1