Readonly recordset is causing record locks - How to fix? 1

[tsinvr]

I have a program that is reading one database and based on the results will update a seperate database. My reads are supposed to be readonly but it is being reported to me that this is causing recordlocks on the server that is blocking other processes.

here is a snipit of code:
With oSLXRS
.ActiveConnection = oSLXConn
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.CacheSize = 100
.Open sSQL, Options:=adCmdText
End With

Am I getting locks because my cursorlocation is adUseServer?

Thanks.

 

[Creepers]

Your lock is because of the LockType = adLockReadOnly. If adding/updating records, I would suggest:
CursorType = adOpenKeySet
LockType = adLockPessimistic

 

[hmckillop]

If for example you are accessing a SQL server, (adLockReadOnly) will take shared locks out on any data, basically meaning that anyone wishing to access that data can, but anyone wanting to update that data will not be able to.
This is how it should be as, you dont want anyone to change the data prior to you having finished reading all the fields.
The alternative is to specify at a database level you dont require any locking ie use something like a database hint NOLOCK - e.g. if you SQL string was "SELECT * FROM TableA" instead use "SELECT * FROM TableA (NOLOCK)"

Also try switching to adUseClient as it should disconnect locks after each row is accessed


I would guess that most DBs lock in a similiar method.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[tsinvr]

Thanks hmckillop that was exactly what I needed. I put in the (NOLOCK) on my select statement and the problem has gone away.

 

[hmckillop]

be wary though, as NOLOCK does not issue shared locks and does not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible.

"I'm living so far beyond my income that we may almost be said to be living apart