What column from master.dbo.sysprocesses is unique for the session?

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 cl开发者_运维知识库ear 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

---

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

---

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

Try spid + kpid.