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.

consuming result sets from dynamic queries within a cursor

Hi SQLers
I have the flowing scenario. I have a cursor that amongst other things
returns me the name of a stored procedure that needs to run and a parameter
to pass to it. Depending on whether the stored proc returns a result I then
decide what to do with the other data I get from the cursor. But I want to
suppress the result sets coming from the inner stored proc calls.
I basically want something like this in my cursor loop
insert into #tmp exec ( @.StoredProcedureName + ' ' + @.messageID )
if exists (select 1 from #tmp)
-- do something with cursor data
else
-- do something else with cursor data
-- where @.StoredProcedureName and @.messageID are data from the cursor
but I get an error saying invalid object #tmp
I cannot declare #tmp as a table variable as unfortunately the stored
proceedures called are different for different iterations of the cursor, tha
t
is @.StoredProcedureName changes and the result set from the different
procedures is different. I cannot make them the same as each of these proc
does a different job and they are called by different callers and so cannot
be changed.
and I dont want to say
exec ( @.StoredProcedureName + ' ' + @.messageID )
if (@.@.ROWCOUNT > 0)
-- do something
else
-- do something else
as the result set of the exec is then returned to the caller.
If anyone has any ideas I would be very greatful. I know some ways to solve
this in C# .NET but I was just wondering if there is a SQL solution. I expec
t
there is not but thought I would ask those that are likely to know.
Many thanks
DerekI don't understand why you would want to execute a stored proc dynamically
based on a cursor. TSQL does have IF statements for flow control but what
you are describing is a very procedural solution. Are you sure there isn't a
set-based SQL alternative to all this?
Read this article on how to do dynamic SQL (and why NOT to do it):
http://www.sommarskog.se/dynamic_sql.html
This one explains how you can process result sets from an SP:
http://www.sommarskog.se/share_data.html
David Portas
SQL Server MVP
--|||Hi David,
Thanks for your time.
Basically my cursor is finding all entries in a MessageReceipients table
that have NULL as their SentDateTime and once armed with the MessageID and
@.MessageType for these intended recipients, I need to call the appropriate
stored procedures (the actual stored proc will depend on the MessageType) to
see if these receipients can still have messages sent to them.
For example it is possible that a message receipient is in the
MessageRecipient table and that the mail system originally failed to send th
e
message but by the time we come to do this check (to resend the message) the
intended recipient my no longer want or be able to receive messages. This
fact can only be determined by calling the @.StoredProcedureName (in my
original code sample) and the reason this is dynamic is that the procedure
that needs to be checked to determin if a message can still be sent is
different for different message types.
I know I can solve this problem in my C# code and perhaps this is the place
to do it. For example I could just return all receipients that have got
potentially outstanding messages. And in the C# check the @.MessageType and
@.MessageID and then call the @.StoredProcedureName with @.MessageID to deterim
whether it is really appropriate to send the message to the receipient.
I was just hoping to not have to do this step in the C# application if I
could avoid it.
I also realise that re writing the various @.StoredProcedureName SPs would
make life easier but unfortunately they are used by other parts of the app
and so I cannot change them. So I would have to esentially duplicate there
functionality with the exception of how they return their results and that
would not be good from a maintenance perspective.
Having said that for now all I am doing in the C# application is looking at
the last result set returned from my SP as this is the result set that I
want. But this is only ok as the results sets are quite small and so I do no
t
need to worry to much about returning unwanted data to the C# application.
Hope that make things a little clearer
Many thanks
Derek
"David Portas" wrote:

> I don't understand why you would want to execute a stored proc dynamically
> based on a cursor. TSQL does have IF statements for flow control but what
> you are describing is a very procedural solution. Are you sure there isn't
a
> set-based SQL alternative to all this?
> Read this article on how to do dynamic SQL (and why NOT to do it):
> http://www.sommarskog.se/dynamic_sql.html
> This one explains how you can process result sets from an SP:
> http://www.sommarskog.se/share_data.html
> --
> David Portas
> SQL Server MVP
> --
>
>