controlling database access

Hi All
I have a scenario where I have several processes (web-farm) that try to
process data in a certain table. I'd like to control access such that only 1
row from the table can be processed at a time regardless of how many
external processes try to access it. I guess I need to lock access so the
right sort of lock is required. Is it possible to lock a row/table based on
a read or does it have to be written to?
Currently using Serializable but it seems overkill.
Also is there a good recommendation for SQL server book that relates to
common SQL server tasks problems/solutions - A sort of patterns book?
Thanks
ShaunShaun Wilde wrote:
> Hi All
> I have a scenario where I have several processes (web-farm) that try
> to process data in a certain table. I'd like to control access such
> that only 1 row from the table can be processed at a time regardless
> of how many external processes try to access it. I guess I need to
> lock access so the right sort of lock is required. Is it possible to
> lock a row/table based on a read or does it have to be written to?
> Currently using Serializable but it seems overkill.
> Also is there a good recommendation for SQL server book that relates
> to common SQL server tasks problems/solutions - A sort of patterns
> book?
> Thanks
> Shaun
You could use an Application Lock. See sp_getapplock and
sp_releaseapplock in BOL.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Did you mean to say only one row to be processed at a time OR a row can be
processed by one and only one process?
Regarding books, this might interest you:
SQL Server 2000 Fast Answers for DBAs and Developers:
http://vyaskn.tripod.com/sql_server...ast_answers.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Shaun Wilde" <shaun_wilde@.nospam.nospam> wrote in message
news:OFGDayhZFHA.3152@.TK2MSFTNGP14.phx.gbl...
> Hi All
> I have a scenario where I have several processes (web-farm) that try to
> process data in a certain table. I'd like to control access such that only
1
> row from the table can be processed at a time regardless of how many
> external processes try to access it. I guess I need to lock access so the
> right sort of lock is required. Is it possible to lock a row/table based
on
> a read or does it have to be written to?
> Currently using Serializable but it seems overkill.
> Also is there a good recommendation for SQL server book that relates to
> common SQL server tasks problems/solutions - A sort of patterns book?
> Thanks
> Shaun
>|||Hi Vyas
I mean that only one row can be processed at a time by only one process.
Process A - works on row 1
Process B - works on row 2
Process C sees there is nothing to do and waits (polling)
Shaun
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:u$#Y64hZFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Did you mean to say only one row to be processed at a time OR a row can be
> processed by one and only one process?
> Regarding books, this might interest you:
> SQL Server 2000 Fast Answers for DBAs and Developers:
> http://vyaskn.tripod.com/sql_server...ast_answers.htm
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Shaun Wilde" <shaun_wilde@.nospam.nospam> wrote in message
> news:OFGDayhZFHA.3152@.TK2MSFTNGP14.phx.gbl...
only
> 1
the
> on
>|||Hi Shaun,
It seems you want the row be modified / updated only by one process while
other is not able to do anything? I am afraid we do not have exactly same
funcation as you needed.
You may check the topic "Isolation Levels" in BOL for more reference,
different isolation level will provide different data access privilege.
However you will have to customize your project manually. For example
1. Add a new column named IsAccess and defaultly set to zero
2. When one process want to use the row, it will have to use a transaction
to update IsAccess column
3. If the column is updated to 1, which means some process is working on
this row and others will have to wait.
Something like realize a lock system yourself, but more complex. You may
refer lock mechanism in operating systems.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.