Table locking causing problems

I have to fix a possible locking problem with an existing app.

The overall process consists of getting a base 36 number from a table to use
as a
product id, then increment it by one and save the new number back to the
table.

The table has only one column and one row, containing the next available
product
id. The select statement is trivial:

select CurProId from ProdIdTable

The record set is declared local to the procedure:

dim rsGetProdNum as ADODB.Recordset
dim NextProdId as String

The app is using ADO 2.7 and the code to open the record set is:

Set rsGetProdNum = New ADODB.Recordset
rsGetProdNum.CursorLocation = adUseServer
rsGetProdNum.Open strSQL, dbADOConx, adOpenDynamic, adLockPessimistic

There is a validation for the results (sort of):

With rsGetProdNum
If .EOF = False Then
NewProdNum = Trim(!CurProId)
Else
MsgBox "INVALID PRODUCT NUMBER CALL COMPUTER DEPT!!!", vbOKOnly
Exit Function
End If
End With

The logic then proceeds to calculate the next product id and then updates
and
closes the table:

(logic to calculate next prod id snipped, resulting id in NextProdId
variable)

rsGetProdNum!CurProId = NextProdId

'Update and unlock table
rsGetProdNum.Update
rsGetProdNum.Close

The problem is that some users are getting results where rsGetProdNum.EOF is
True, that is, it does not return anything. This condition is not possible
because it
is garanteed that the Prod Id table will *always* contain valid data. This
might be
happening because of some locking issue.

What is the best way to approach this scenario? Thanks Saga.

It's impossible to remember (or understand) all the intricacies of the
various parameters for opening an ADO recordset. For example, it's possible
that by replacing adOpenDynamic with adOpenKeyset will make your code work
or not but I cannot tell you anything on this.

However, in your case, the solution would be to not compute the new ID value
on the client side using ADO. This type of calculation should always be done
directly on the server inside a transaction using a stored procedure (SP).
Search Google for "updlock holdlock incrementing" and you should find many
examples. This is not only a question of performance and reliability but
also a question of not blocking the other users if - for any reason - the
client application freezes in mid-process.

You must also take into account the fact that even if the SQL-Server will
always correctly return a new ID, it's possible that the client will freeze
right after - for whatever reason - and make this new ID orphan; ie.,
without any associated record. In order to eliminate this possibility, the
SP should not only return the new ID but also take in charge the process of
creating the new empty record. Later, you can check the database for empty
records or you can chose to set a flag inside each record when creating it
saying that this record is new but still unused. This way, you will ensure
that there is no hole in your sequence of numbers and that there is always
an associated record - possibly unused - for each number; something that is
mandatory for financial records.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


"Saga" wrote in message

>I have to fix a possible locking problem with an existing app.
>
> The overall process consists of getting a base 36 number from a table to
> use as a
> product id, then increment it by one and save the new number back to the
> table.
>
> The table has only one column and one row, containing the next available
> product
> id. The select statement is trivial:
>
> select CurProId from ProdIdTable
>
> The record set is declared local to the procedure:
>
> dim rsGetProdNum as ADODB.Recordset
> dim NextProdId as String
>
> The app is using ADO 2.7 and the code to open the record set is:
>
> Set rsGetProdNum = New ADODB.Recordset
> rsGetProdNum.CursorLocation = adUseServer
> rsGetProdNum.Open strSQL, dbADOConx, adOpenDynamic, adLockPessimistic
>
> There is a validation for the results (sort of):
>
> With rsGetProdNum
> If .EOF = False Then
> NewProdNum = Trim(!CurProId)
> Else
> MsgBox "INVALID PRODUCT NUMBER CALL COMPUTER DEPT!!!", vbOKOnly
> Exit Function
> End If
> End With
>
> The logic then proceeds to calculate the next product id and then updates
> and
> closes the table:
>
> (logic to calculate next prod id snipped, resulting id in NextProdId
> variable)
>
> rsGetProdNum!CurProId = NextProdId
>
> 'Update and unlock table
> rsGetProdNum.Update
> rsGetProdNum.Close
>
> The problem is that some users are getting results where rsGetProdNum.EOF
> is
> True, that is, it does not return anything. This condition is not possible
> because it
> is garanteed that the Prod Id table will *always* contain valid data. This
> might be
> happening because of some locking issue.
>
> What is the best way to approach this scenario? Thanks Saga.
>
>
>