High concurrency and adLockPessimistic

[tb2007]

I have VB application on client side and SQLServer on server side.

I have one table COUNTER with 3 columns:
Id (autonumber)
Counter (long)
DeskId (long)


What is the problem? I have many users (300) and they need counter in the same time (high concurrency). So one user takes counter 1 and update counter to 2, another user takes counter 2 and update to 3, and so on. I wander is my solution good, is it possible that 2 users take the same counter?

I have function Counter like this.

Public Function Counter(ByVal Desk As Long) As long

Dim rs As ADODB.Recordset
Dim Query As String
Dim ccon As New ccon

Query = "SELECT * FROM Counter Where DeskId = " & Desk
ccon.MakeConnection

Set rs = New ADODB.Recordset

With rs
.CursorLocation = adUseServer
.Source = query
Set .ActiveConnection = ccon.cnn
.CursorType = adOpenDynamic
.LockType = adLockPessimistic
.Open
End With

rs! Counter = rs! Counter + 1
rs.Update

Counter = rs!Counter

Set rs.ActiveConnection = Nothing
ccon.Conn_Terminate
Set rs = Nothing
Set ccon = Nothing
Exit Function

 

[fredericofonseca]

if is better for this particular purpose if you create a SP that does all the select and update for you.

It will be faster and will reduce the number of locks you get.

Using a counter and the error code within the SP you can further reduce the number of times your app will receive any errors by retrying to get a new counter a few times in case you get a lock.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com