How to use SET LOCK MODE TO WAIT

[kevinrm]

I have an error -244 that causes a VB app to stall.

-244:Could not do a physical-order read to fetch next row

I have read many docs and most thing point to using SET LOCK MODE TO WAIT. I interpret this as an SQL statement. Having never used this before how should I include this in the code.

Here is the ADO connection that i am using:

Public Function GetConnection() As ADODB.Connection

Dim sConnection As String 'variable for connection string
Dim lErrNo As Long 'variable for error no
Dim sErrDesc As String 'variable for error description

On Error GoTo ExecStatementErr

'check whether opened or not,adocon_connobj.state=1 means opened.
If adocon_connobj.State <> 1 Then

sConnection = GetConnectionString()

adocon_connobj.Open sConnection 'open the connection
Set GetConnection = adocon_connobj

Else

Set GetConnection = adocon_connobj

End If

Exit Function

ExecStatementErr:

lErrNo = Err.Number
sErrDesc = Err.Description
logit &quot;Failed to open Connection for Database operations in GetConnection().ConnectionString : &quot; & sConnection
Err.Raise lErrNo, , sErrDesc

End Function

 

[vpshriyan]

Hi,

-244:Could not do a physical-order read to fetch next row

This logical error condition results when a row is being read is denied to do so. This may be solved either by setting the LOCK MODE or by session ISOLATION granularity.

By setting the SET LOCK MODE TO WAIT, you are telling the database to wait indefinately in case of such condition. If such long wait period is not acceptable, or the operation that you are doing is not very critical or 100% result is not expected (if phanthom rows are ok), then you can set your session separation to SET ISOLATION TO DIRTY READ.

I'm not a Visual Basic programmer, so I may not be in a best position to decipher the VB code. However, both these issues are related to the backend database. Following are the example which you may consider and precede it to a existing SQL statement or figure-out how to impliment it in the Frontend application.

SET LOCK MODE TO WAIT; SELECT * FROM customer WHERE ...
SET ISOLATION TO DIRTY READ; SELECT * FROM customer WHERE ..

Regards,
Shriyan

 

[shhankar]

Hi,

This is a backend error. Generally,it is not recommended to issue the &quot;SET LOCK MODE TO WAIT&quot; statement in any critical environment.

Instead set a time frame in seconds ie.,Use
SET LOCK MODE TO WAIT 20.

Otherwise use an isolation level to fix the same.
SET ISOLATION TO DIRTY READ.

 

[kevinrm]

I made the change to add it to the following.

iNoOfRunning = CInt(oDB.GetDataValue(&quot;SET LOCK MODE TO WAIT; select count(*) from Scheduler where LastRunStatus='R' and TaskType='C'&quot)

i get an error -555...

-555 Cannot use a select or any of the database statements in a multi-query prepare.

The statement text that is presented with this PREPARE statement has multiple statements divided by semicolons, and one is a SELECT, DATABASE, CREATE DATABASE, or CLOSE DATABASE statement. These statements must always be prepared as one-statement texts. Check the statement text string, and make sure that you intended multiple statements. If you did, revise the program to execute these four statement types alone.

thank you for all your help. i appreciate your time in helping me.

kevin

 

[shhankar]

Jumbalaka!!

You cannot issue SET LOCK MODE TO WAIT and SELECT statement as a single statement. Execute it seperately.