VB.NET and SQL

  • Thread starter Thread starter Pepehammer
  • Start date Start date
P

Pepehammer

Hi there guys!

i'm using this query in VB.NET:

Dim sSQLFirstAvailable As String = _
"SELECT TOP 1 * " & _
"FROM Mi_Table "& _
"WHERE Status IN (0,3) AND WTN > '" & sLastWTN & "' " & _
"ORDER BY WTN"

This query it's used in a multi user program, BUT in the same moment 2 or
more users can pick the same record up.
How can i do to "lock" the record that a user is using, and make the next
user to pick up the next record?

Thanks
 
What database product are you using?

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.
 
If I read your question correctly I don't think Cor's pointer to optimistic
concurrency will yield the answer. The only solution that I can see working
is to funnel all requests through a single source. That means a
server-based component (of some sort) that can queue the requests... that
way when a request is being processed other requests are simply held.

That has to be combined with a query that can determine which row(s) have
already been returned. How best to implement that depends upon a few things
including how dynamic your list of rows is. The simplest way of course is
to add a column and have the "request process" mark the record as having
been returned already. The query would include a clause to only choose the
ones without that field set.

Try looking up "request queue" or something close to that in Google.

Tom
 
Hi Tom,

I can give an answer, but I hope Bill Vaughn will do that also (This is
also in the newsgroup adonet), I wait always on him for concurrency, he
tells that alway so nice.

But this was such a simple link with so much information.

The theory about optimistic concurrency is something I tell my whole
computing live.
(Same idea as Bill Vaughn)

:-)

Cor
 
Cor: Not a problem... if you read the SQL presented once again and imagine
what it might be used for I think you can see that it isn't a concurrency
issue at all.

Imagine three users needing to obtain the "next customer" from a pool of all
customers meeting some criteria. Like the customers are late with their
payment and you have to phone them (or anything like that.) You can't have
all three users have the exact same customer record appear on the screen or
all three will make the same phone call.

It isn't an "update" problem and the requests need to be queued.

But perhaps I've interpreted it wrong...
 
How do you handle sLastWTN ? Don't you need later to know which person
picked the record ? Do you need to display something before it is picked ?

I would rather update the record to keep track of who picked a particular
record. You can then query for the next unpicked record... If data are
already displayed you'll have to handle the case where someone displayed a
record but someone else picked it before.

My ideal scenario at first sight would be just a "pick next" button that
update the next unpicked record to mark it as picked before returning the
picked record (in a single transaction)...

Patrice
 
Hi Tom,

I see that probably here is pesimistic concurrency needed, but that is also
on that page.
(I checked it before I did send that link).

Cor
 
The situation isn't (in my mind) a simple question of locking. When UserA
locks the "TOP 1" row doesn't that only delay the access to other users?
They eventually get a timeout failure or obtain that very row when the lock
is released. They are requesting the "same" row and the OP needs it to
return the next one.

The key in my mind is to queue the requests so that they are done in an
orderly fashion. It may require nothing more than a stored procedure but it
seems to me that no other request for the "TOP 1" row can (in this case) be
processed until the first one is complete. The SQL code posted did nothing
to insure that subsequent requests wouldn't return the exact same row.

This can only be done by modifying the first row returned (and including a
check for that modified field in the query) or setting up a server component
that keeps track of which rows it handed out. In the second case it may not
be necessary to modify the row as the component would keep track.

The ideal solution (as with so many problems) depends upon the
circumstances...

Tom
 
Hi Tom,

I think that there are more roads that goes to Rome for this problem.

The OP was asking locking and to be honest, that did bring me on the wrong
route.

I think that your method your method is one of the solutions.

However, and not to tell that I did not make a mistake by answering you to
fast. There is another solution that can be used in my opinion with
optimistic concurrency.

The methode exist in creating an extra table wherin one by one everytime in
a list structure the acknowledge of the user is saved as a new row.

But just as an alternative and I do not know if this will fit.

Cor
 
Make no mistake, I know what you mean :-) but we seem to have lost the
OP...

We say "all roads lead to Rome" actually. It's an interesting little
problem that's why I like it. Queue theory is study unto itself and I
surely don't know the half of it. I'm also making a big guess on what the
person is doing based upon a single line of SQL.

Tom
 
All roads lead to Rome, but what differentiates the men from the boys in
programming is figuring out which road is shortest!

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.
 
Hi Kevin,

Have you been in Rome, a lot of men take the roads with the most beautiful
girls and the more you reach Rome it seems that there are every time more
roads with beautiful girls.

It are maybe not the shortest road, but in a kind of way the best.

:-)

Cor
 
Back
Top