Record Locking Mechanism - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Record Locking Mechanism

Author

Topic

Shilpa22
Starting Member

37 Posts

Posted - 2010-07-29 : 08:59:00

Hello All,
I have a procedure which return a result set.(ex: AccountIds :a,b,c,d,e,f,g). In .net code behind I am assigning the each accountID to a Request.My requirement is once an AccountId is assigned to a Request, unless it finishes the transsaction that account id shud not used for other requests. SO I have to Lock that particual Acccount Id for that request till its released.
How can I acheive this? Please help me out

Thanks in Advance,
Silpa

Thanks in Advance
Shilpa

Shilpa22
Starting Member

37 Posts

Posted - 2010-07-30 : 01:55:59

any suggestions please???

Thanks in Advance
Shilpa

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-07-30 : 02:17:32

Put a additional column is_locked bit

1.Set the column when you start processing
2.Do the process
3.re-set the column

If other request came, just check weather the column is_locked and proceed.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Shilpa22
Starting Member

37 Posts

Posted - 2010-07-30 : 02:48:08

Thanks for your reply.
The problem I am facing is. Actually there are 4 Queues which assings account Ids to the Request. If all the Queues attempt to assign a account ID(concurrent Requests), at that moment same Account Id is being assigned.
I thought abt u r suggestion earlier..Even in this case also it cud result the same. Because on the first pick, that acctId may get assigned al the requests even we set flag.
What do u think?

Thanks in Advance
Shilpa

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-07-30 : 02:52:52

You must design a queue such a way it must allow a single request at a time.
Queue doesn't allow Concurrent request.It must be one by one.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Shilpa22
Starting Member

37 Posts

Posted - 2010-07-30 : 02:56:25

I am very new to this Technology. Can you please give me an example or links which can guide me.

Thanks in anticipation

Thanks in Advance
Shilpa

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-07-30 : 03:03:29

quote:
Originally posted by Shilpa22

I am very new to this Technology. Can you please give me an example or links which can guide me.

Thanks in anticipation

Thanks in Advance
Shilpa

I don't know about your business, Just i have a hawk-eye

i suggest this,it may helps you

http://www.15seconds.com/issue/020903.htm

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources