Help Isolating Data Access

[Chris Gallucci]

I need help with what I think may be a design issue.

I need to access a record for processing. I need to do it in such a fashion that several concurrent processes do not access the same
record.

I've set up a stored procedure that first queries the table for a record to process than marks the record so that it is now
unavailable.

The process that executes this stored procedure runs on multiple threads on multiple machines.

My problem is that I have occasions where two or more threads access the same record at the same time which causes unwanted
redundant processing. IOW, I need to be able to guarantee that a record will never be processed by more than a single thread of
execution.

Does anyone have a solution or suggestion? Is there a design pattern for this?

FWIW, the parent process is a Windows Service written in C#.NET (v1.1) running against a SQL Server 2000 database.

ChrisG

[William Ryan eMVP]

Chris:

Do you mean the record on the DB side or locally? If you mean on the db,
I'm not sure I understand. Let's say that I have record with a field called
CustNumber and process one with user Bill is accessing it. I pull the
record down into a dataset. During the select I mark the record as Used to
that the same query run now won't return the record b/c the where clause
specifies "where UsageFlag <> '1'" or whatever you do for indicating used.
Is this the scenario? Or do you mean you have 3 or four processes running
locally? If it's locally than you can sync lock the datatable (or the row)
and be done with it (well, that may be an overstatement, I don't want to
overgeneralize when we're talking about threading).

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
"Chris Gallucci" <ch***@gallucci.com> wrote in message
news:eZ**************@TK2MSFTNGP12.phx.gbl...

I need help with what I think may be a design issue.

I need to access a record for processing. I need to do it in such a fashion that several concurrent processes do not access the same record.

I've set up a stored procedure that first queries the table for a record to process than marks the record so that it is now unavailable.

The process that executes this stored procedure runs on multiple threads on multiple machines.
My problem is that I have occasions where two or more threads access the same record at the same time which causes unwanted redundant processing. IOW, I need to be able to guarantee that a record will never be processed by more than a single thread of execution.

Does anyone have a solution or suggestion? Is there a design pattern for this?
FWIW, the parent process is a Windows Service written in C#.NET (v1.1) running against a SQL Server 2000 database.
ChrisG

[Chris Gallucci]

"William Ryan eMVP" <do********@comcast.nospam.net> wrote in message news:OY**************@TK2MSFTNGP12.phx.gbl...
| Chris:
|
| Do you mean the record on the DB side or locally?
On the DB side.

| If you mean on the db,
| I'm not sure I understand. Let's say that I have record with a field called
| CustNumber and process one with user Bill is accessing it. I pull the
| record down into a dataset. During the select I mark the record as Used to
| that the same query run now won't return the record b/c the where clause
| specifies "where UsageFlag <> '1'" or whatever you do for indicating used.
| Is this the scenario?
Yes. And I do as you suggest in a stored procedure call in an attempt to isolate the selection/marking.

Or do you mean you have 3 or four processes running
| locally? If it's locally than you can sync lock the datatable (or the row)
| and be done with it (well, that may be an overstatement, I don't want to
| overgeneralize when we're talking about threading).
I have several threads that call the aforementioned SP. At times it happens that 2 more threads appear to call the SP at the same
time in which case they grab the same record.

| --
| W.G. Ryan MVP Windows - Embedded

ChrisG