using ADO with SQL database - locking issues

[Anonymous]

I have VBA code running in AutoCAD 2015.  I have a connection string that points to a database on a SQL Server.  When I define the recordset do I really need to set the LockType and CursorType?  I am having some database locking problems reported by end users.  Multiple users are using the VBA code and I am wondering if setting the LockType and CursorType is really necessary and maybe that is the culprit.  SQL should handle that automatically for me right?  The LockType and CursorType exist in the code because the database was formerly MS Access and it was never removed when we migrated to a SQL database.  Can anyone help?

EXAMPLE CODE SNIPPET WITH CURSORTYPE AND LOCKTYPE DEFINED

con.Open ConnectionString

Set rec = New ADODB.Recordset
With rec
    .ActiveConnection = con
    .CursorType = adOpenStatic
    .LockType = adLockPessimistic
End With

rec.Open "Select * From [MetalStuff].[wallstuff].[WALLPIECES]"
Do While Not rec.EOF
    If IsNull(rec("releaseNum")) Then
        rec("releaseNum") = ""
        rec.Update
    End If
    rec.MoveNext
Loop
rec.Close

con.Close

[jy_guillevic]

Hi mllietzau,

with this code, i would try this

adOpenForwardOnly    0    Default. Uses a forward-only cursor. Identical to a static cursor, except that you can only scroll forward through records. This improves performance when you need to make only one pass through a Recordset.

with that

adLockOptimistic - Indicates optimistic locking, record by record. The provider uses optimistic locking, locking records only when you call the Update method.

let me know

[Anonymous]

We are still uncertain why we are occasionally getting locking errors.  We even created a new VBA project using your settings and had my coworker, a fellow programmer, writing 5,000 records while I was reading the same table and we didn't receive any locking errors.  In fact, everything we tested using your locking settings or the settings we originally had tested out perfectly fine.  We could never get it to fail no matter what we tried!

My coworker thinks the locking errors occurred here: He was debugging code and he had a breakpoint sitting inside of a while loop which was iterating through records of an open database connection.  A drafter interrupted him and the code was just sitting inside the while loop waiting for him to continue when he walked away to deal with the drafters problem.  Then drafters started reporting locking issues.

Unfortunately my coworker is debugging using the same database the end users are using in production.  He needs to use the same database though because it's easier to fix bugs when you have the actual data to produce the exact problem that was reported.  The only solution I can come up with is to have my coworker debug on our TEST SQL server and have the end users use our PRODUCTION SQL server.  When my coworker needs to debug an issue he will somehow need to update the TEST SQL server with the real production data and then start his diagnosis.

[norman.yuan]

Another thing to consider, which should be the first thing to consider, IMO, is to have appropriate "WHERE..." clause in the "SELECT..." query to open ONLY the records that are really needed.

In your case, you only want to set "releaderNum" column if it is NULL. so, instead of each time you get all the records (thousands, or millions?, not to mention you did pessimistic locking on them) to your app, you only get those with "releaseNum" not set, maybe only a few, which would greatly reduce the chance of concurrent writing access to the same records:

SELECT * FROM....WHERE releaseNum IS NULL

[jy_guillevic]

Norman is right,

UPDATE [MetalStuff].[wallstuff].[WALLPIECES] SET releaseNum = '' WHERE releaseNum is null; job done

try your code while updating both of you not reading.