Hi Folks,
(SQL Sever 7, VB6 Ent.)
Here's the scenario.  Up to 5 different execuatables will be hitting the
same table in the same database at the same time.  Specifically, the SQL
statement looks like this:

rsRequest.Open "SELECT * FROM REQUEST WHERE R_REQUEST_NUMB = (SELECT
MIN(R_REQUEST_NUMB) FROM REQUEST WHERE R_CLOB_ID = ' ')", cnct,
adOpenKeyset, adLockPessimistic
rsRequest("R_CLOB_ID") = "1"
rsrequest.update

So I get the minimum request number and then immediately assign it an ID so
no other will query it.
I am trying to find a method of reliably locking this row exclusive to the
exe that querried it.
Any one of the exe's running by themseleves will do as expected and
retrieve/update the expected record, but when 2 or more do this together,
sometimes they will pull the same record causing me errors down the line.

I'm trying to keep this as generic and x platform/provider as possible.
Any thoughts or ideas would be greatly appreciated.
Thanks...
Tom
tomod00(AT)bellatlantic.net

Tom,
I would write a stored procedure that locks the table while updating it. If
any other EXE is trying to do the same, it will recieve an error telling it
that the table is locked. The EXE could then just try again.
Thomas.

Tom
A possible solution to this is to use locking hints to force sqlserver to
use a particular type of lock:
cnct.BeginTrans
sql="SET LOCK_TIMEOUT 0 "  'Tell me if the row is already locked by another
process!
sql=sql & "SELECT * FROM REQUEST WITH (ROWLOCK,UPDLOCK) WHERE R_REQUEST_NUMB
= (SELECT MIN(R_REQUEST_NUMB) FROM REQUEST WITH (NOLOCK) WHERE R_CLOB_ID = '
')"

rsRequest.Open sql,cnct,adOpenKeyset, adLockPessimistic

rsRequest("R_CLOB_ID") = "1"
rsrequest.update
cnct.CommitTrans

In this example, first we begin a transaction on the connection and then set
the lock timeout to 0, so if the row is already locked by another process,
ADO will generate an error which we can trap. A number higher than 0 will
make sqlserver keep trying to get the lock for a given number of seconds

Next, in the sql string, i add WITH (ROWLOCK,UPDLOCK) to the part of the
select that is returning the recordset. This will force an undate lock at
row level. Any other user now trying to do this will get the error I
described earlier (sorry, cant remember the exact error number). The row is
now locked!
Update the row and then commit the transaction

BE WARNED!: Sql Server does NOT cope with row level locking at all well! If
SqlServer thinks you have locked too many rows in a table or page, it
automatically escallates the Row level locks to page or table locks. The
effect of this is that rows which you know are NOT locked suddenly, without
warning or apparent explanation become locked. This effect can spread
accross tables also under certain circumstances, so a row on a table that
you KNOW is NOT locked suddenly appears to be locked. The recommended
approach is to keep all locking within transactions (as the lock is released
when the transaction is committed or rolled back) and keep the duration of
the transaction as short a possible