Prev: Multimedia and internet keys of the keyboard not working under VO apps
Next: Problem date comparision 2010
From: Bernhard Mayer on 12 Jan 2010 04:26
Dear fellow VO'ers!

At our largest customer we are getting deadlock errors which we could not
solve up to now.

Error: Transaction (Process ID 70) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
Subsystem: Vo2Ado
Error Subcode: 16389

Mostly the call stack of the error is located in AdoServer:Init; I don't
really understand why the message says something about transactions - every
call stack we got up to now is nowhere near something as a transaction, we
use AdoServer only for reading purposes.

Sample:

cStmt := "Select FieldA, FieldB,..... from ORDERS where ..."
oServer := AdoServer{cStmt, oConn, adOpenForwardOnly, adLockReadOnly,
adCmdText}

The information we got from google did not really help us so far; we tried
to use BEGIN SEQUENCE + Error blocks to catch the error but so far we did
not succeed. Another problem is that the deadlock error seems to terminate
the database connection and the whole app crashes.

Could anyone help me with this problems?

using SQL Server 2005 32 bit Standard Edition, SP 2
VO 2.832, VO2Ado 2.8 prof., the AdoConnection cursor type is adUseClient

TIA,
Bernhard

From: Lars Broberg on 12 Jan 2010 04:55
Bernhard,

Are you REALLY sure that it's the SELECT that deadlocks?
A deadlock is when two connections "locks" each other. The first has a
lock on table A and needs a lock on table B where the second already has
a lock on table B and now needs one on table A. The two connections will
never complete in this scenario and one of them will be closed by SQL
Server (the deadlock victim). I really can't see how a simple SELECT
against one table can cause a deadlock. It could mean an update had to
wait if it was a VERY huge SELECT taking a lot of time, but it shouldn't
deadlock.
----
Lars Broberg
Elbe-Data AB
lars at elbe-data dot se


Bernhard Mayer wrote:
> Dear fellow VO'ers!
>
> At our largest customer we are getting deadlock errors which we could
> not solve up to now.
>
> Error: Transaction (Process ID 70) was deadlocked on lock resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction.
> Subsystem: Vo2Ado
> Error Subcode: 16389
>
> Mostly the call stack of the error is located in AdoServer:Init; I don't
> really understand why the message says something about transactions -
> every call stack we got up to now is nowhere near something as a
> transaction, we use AdoServer only for reading purposes.
>
> Sample:
>
> cStmt := "Select FieldA, FieldB,..... from ORDERS where ..."
> oServer := AdoServer{cStmt, oConn, adOpenForwardOnly, adLockReadOnly,
> adCmdText}
>
> The information we got from google did not really help us so far; we
> tried to use BEGIN SEQUENCE + Error blocks to catch the error but so far
> we did not succeed. Another problem is that the deadlock error seems to
> terminate the database connection and the whole app crashes.
>
> Could anyone help me with this problems?
>
> using SQL Server 2005 32 bit Standard Edition, SP 2
> VO 2.832, VO2Ado 2.8 prof., the AdoConnection cursor type is adUseClient
>
> TIA,
> Bernhard
From: Bernhard Mayer on 12 Jan 2010 06:27
Hi Lars!

> Are you REALLY sure that it's the SELECT that deadlocks?

So far only in AdoServer Select statements the deadlock error emerged. I
don't understand why this happens.

BR,
Bernhard

"Lars Broberg" <this.is.not.a.valid(a)dress.se> schrieb im Newsbeitrag
news:PGX2n.23$Gn5.3594(a)newsb.telia.net...
> Bernhard,
>
> A deadlock is when two connections "locks" each other. The first has a
> lock on table A and needs a lock on table B where the second already has a
> lock on table B and now needs one on table A. The two connections will
> never complete in this scenario and one of them will be closed by SQL
> Server (the deadlock victim). I really can't see how a simple SELECT
> against one table can cause a deadlock. It could mean an update had to
> wait if it was a VERY huge SELECT taking a lot of time, but it shouldn't
> deadlock.
> ----
> Lars Broberg
> Elbe-Data AB
> lars at elbe-data dot se
>
>
> Bernhard Mayer wrote:
>> Dear fellow VO'ers!
>>
>> At our largest customer we are getting deadlock errors which we could not
>> solve up to now.
>>
>> Error: Transaction (Process ID 70) was deadlocked on lock resources with
>> another process and has been chosen as the deadlock victim. Rerun the
>> transaction.
>> Subsystem: Vo2Ado
>> Error Subcode: 16389
>>
>> Mostly the call stack of the error is located in AdoServer:Init; I don't
>> really understand why the message says something about transactions -
>> every call stack we got up to now is nowhere near something as a
>> transaction, we use AdoServer only for reading purposes.
>>
>> Sample:
>>
>> cStmt := "Select FieldA, FieldB,..... from ORDERS where ..."
>> oServer := AdoServer{cStmt, oConn, adOpenForwardOnly, adLockReadOnly,
>> adCmdText}
>>
>> The information we got from google did not really help us so far; we
>> tried to use BEGIN SEQUENCE + Error blocks to catch the error but so far
>> we did not succeed. Another problem is that the deadlock error seems to
>> terminate the database connection and the whole app crashes.
>>
>> Could anyone help me with this problems?
>>
>> using SQL Server 2005 32 bit Standard Edition, SP 2
>> VO 2.832, VO2Ado 2.8 prof., the AdoConnection cursor type is adUseClient
>>
>> TIA,
>> Bernhard

From: Meinhard Schnoor-Matriciani on 12 Jan 2010 06:34
Hi Bernhard,

MS SQL has at least one bad attitude compared to other SQL server product,
that are the default values for some properties concerning the commit
behaviour. While other products do not use transactions for simple selects,
MSSQL does as long you don't change this. This has two impacts, first of all
the behaviour you describe might be related to this and second the size of
the tempdb might get very big, depending on the soze of your result sets. In
our application we use two seperate connections, one for select data, the
other one for manipulating data. On the read connection we explicitely do
something like

oReadConn:SetConnectOption( SQL_AUTOCOMMIT ,SQL_AUTOCOMMIT_ON )

which automatically closes the transaction for a select. The other thing you
should keep in mind is the isolation level, we use

oMessageSource:Isolationoption := SQL_TXN_READ_UNCOMMITTED

most of the time. This might not be an option for you because this heavily
depends on the application. I don't know much about VO2ADO, but due to the
fact that this is a server side thing I assume that there are methods to set
these options in a similar way.

Hope this gives you some hints which help to solve your problem. When
looking for additional information look for 'cursor stability' as well.

Regards
Meinhard

"Bernhard Mayer" <Idonotexisthere(a)nowhere.com> schrieb im Newsbeitrag
news:4b4c415d$0$1550$91cee783(a)newsreader04.highway.telekom.at...
> Dear fellow VO'ers!
>
> At our largest customer we are getting deadlock errors which we could not
> solve up to now.
>
> Error: Transaction (Process ID 70) was deadlocked on lock resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction.
> Subsystem: Vo2Ado
> Error Subcode: 16389
>
> Mostly the call stack of the error is located in AdoServer:Init; I don't
> really understand why the message says something about transactions -
> every call stack we got up to now is nowhere near something as a
> transaction, we use AdoServer only for reading purposes.
>
> Sample:
>
> cStmt := "Select FieldA, FieldB,..... from ORDERS where ..."
> oServer := AdoServer{cStmt, oConn, adOpenForwardOnly, adLockReadOnly,
> adCmdText}
>
> The information we got from google did not really help us so far; we tried
> to use BEGIN SEQUENCE + Error blocks to catch the error but so far we did
> not succeed. Another problem is that the deadlock error seems to terminate
> the database connection and the whole app crashes.
>
> Could anyone help me with this problems?
>
> using SQL Server 2005 32 bit Standard Edition, SP 2
> VO 2.832, VO2Ado 2.8 prof., the AdoConnection cursor type is adUseClient
>
> TIA,
> Bernhard

From: Bernhard Mayer on 12 Jan 2010 09:47
Hi Meinhard!

VERY interesting news; we experimented with the
AdoConnection:Isolationlevels but so far we didn't get where we would like
to.

So far I could not find the appropriate VO2Ado functions but maybe Robert
can help me.

Nevertheless thanks a lot; at least I know why the deadlock is happening.

BR,
Bernhard

"Meinhard Schnoor-Matriciani" <meinhard(a)appfact.de> schrieb im Newsbeitrag
news:4b4c6062(a)news.arcor-ip.de...
> Hi Bernhard,
>
> MS SQL has at least one bad attitude compared to other SQL server product,
> that are the default values for some properties concerning the commit
> behaviour. While other products do not use transactions for simple
> selects, MSSQL does as long you don't change this. This has two impacts,
> first of all the behaviour you describe might be related to this and
> second the size of the tempdb might get very big, depending on the soze of
> your result sets. In our application we use two seperate connections, one
> for select data, the other one for manipulating data. On the read
> connection we explicitely do something like
>
> oReadConn:SetConnectOption( SQL_AUTOCOMMIT ,SQL_AUTOCOMMIT_ON )
>
> which automatically closes the transaction for a select. The other thing
> you should keep in mind is the isolation level, we use
>
> oMessageSource:Isolationoption := SQL_TXN_READ_UNCOMMITTED
>
> most of the time. This might not be an option for you because this heavily
> depends on the application. I don't know much about VO2ADO, but due to the
> fact that this is a server side thing I assume that there are methods to
> set these options in a similar way.
>
> Hope this gives you some hints which help to solve your problem. When
> looking for additional information look for 'cursor stability' as well.
>
> Regards
> Meinhard
>
> "Bernhard Mayer" <Idonotexisthere(a)nowhere.com> schrieb im Newsbeitrag
> news:4b4c415d$0$1550$91cee783(a)newsreader04.highway.telekom.at...
>> Dear fellow VO'ers!
>>
>> At our largest customer we are getting deadlock errors which we could not
>> solve up to now.
>>
>> Error: Transaction (Process ID 70) was deadlocked on lock resources with
>> another process and has been chosen as the deadlock victim. Rerun the
>> transaction.
>> Subsystem: Vo2Ado
>> Error Subcode: 16389
>>
>> Mostly the call stack of the error is located in AdoServer:Init; I don't
>> really understand why the message says something about transactions -
>> every call stack we got up to now is nowhere near something as a
>> transaction, we use AdoServer only for reading purposes.
>>
>> Sample:
>>
>> cStmt := "Select FieldA, FieldB,..... from ORDERS where ..."
>> oServer := AdoServer{cStmt, oConn, adOpenForwardOnly, adLockReadOnly,
>> adCmdText}
>>
>> The information we got from google did not really help us so far; we
>> tried to use BEGIN SEQUENCE + Error blocks to catch the error but so far
>> we did not succeed. Another problem is that the deadlock error seems to
>> terminate the database connection and the whole app crashes.
>>
>> Could anyone help me with this problems?
>>
>> using SQL Server 2005 32 bit Standard Edition, SP 2
>> VO 2.832, VO2Ado 2.8 prof., the AdoConnection cursor type is adUseClient
>>
>> TIA,
>> Bernhard
>

 |  Next  |  Last
Pages: 1 2
Prev: Multimedia and internet keys of the keyboard not working under VO apps
Next: Problem date comparision 2010