deadlock

deadlock

Post by uuff » Sun, 01 Apr 2001 09:27:38



we have a web application.

in the backend, two processes write data (from different sources) to the
same database at the same time. i use COM+ to control transaction for
each message received. The components call stored procedures to insert
message into different tables. within each sp, i use (READCOMMITTED) for
each SELECT statement.

in the frontend, when user uses IE to browse our site, there are
deadlocks happened. Browsing and writing data happen at same time. in
the frontend, we only read data (XML) from database and i use SELECT...
FOR XML AUTO(OR EXPLICIT) to fetch data and also use (READCOMMITTED) for
each SELECT statement.

before we have more deadlocks. i split sps into more small sps in the
backend in order to reduce the duration each sp take. it's getting much
better, but the deadlock still happens.

i read book online and try to follow the instructions to minimize
deadlocks, such as access object in the same order, use a low isolation
level...

in our error log, i get the following message:
source: RunSPGetXML (this is my function to run query)
message: -2147217887 - Transaction (process ID 59) was deadlocked on
{locl} resources with another process and has been chosen as the
deadlock victim. Rerun the transaction

I hope somebody can help to solve this problem

jj

 
 
 

deadlock

Post by Mark » Wed, 04 Apr 2001 10:45:02


If your using ado, we had similiar problems and changed our record locking
on our recordsets from pessimistic to optimistic locking which cleared up
90% of our problems.


Quote:> we have a web application.

> in the backend, two processes write data (from different sources) to the
> same database at the same time. i use COM+ to control transaction for
> each message received. The components call stored procedures to insert
> message into different tables. within each sp, i use (READCOMMITTED) for
> each SELECT statement.

> in the frontend, when user uses IE to browse our site, there are
> deadlocks happened. Browsing and writing data happen at same time. in
> the frontend, we only read data (XML) from database and i use SELECT...
> FOR XML AUTO(OR EXPLICIT) to fetch data and also use (READCOMMITTED) for
> each SELECT statement.

> before we have more deadlocks. i split sps into more small sps in the
> backend in order to reduce the duration each sp take. it's getting much
> better, but the deadlock still happens.

> i read book online and try to follow the instructions to minimize
> deadlocks, such as access object in the same order, use a low isolation
> level...

> in our error log, i get the following message:
> source: RunSPGetXML (this is my function to run query)
> message: -2147217887 - Transaction (process ID 59) was deadlocked on
> {locl} resources with another process and has been chosen as the
> deadlock victim. Rerun the transaction

> I hope somebody can help to solve this problem

> jj


 
 
 

deadlock

Post by uuff » Thu, 05 Apr 2001 01:11:10


thank you for your response.
i check my code. when querying from database, the only LockType I use is
readonly. And also when gettin XML from database, I use Command object
instead of Recordset, so I can not set LockType (for Command object).
Here is one of my generic function to get xml:
-----------------------------------------------------------------------
Public Function RunSPGetXML(ByVal strConnectString As String, _
                           ByVal strSPName As String, _
                           ParamArray SPParameters() As Variant) _
                              As String
On Error GoTo ErrorHandler

Dim ADOCommand As New ADODB.Command
Dim objStream As New ADODB.Stream

    objStream.Open

    With ADOCommand
        .CommandType = adCmdStoredProc
        .CommandText = strSPName
        .ActiveConnection = strConnectString
        .Properties("Output Stream") = objStream
    End With

    Dim vArray As Variant
    vArray = SPParameters(0)

    If IsArray(vArray(0)) Then
        Set ADOCommand = DefineParameters(ADOCommand, SPParameters(0))
    ElseIf IsArray(vArray) Then
        Set ADOCommand = DefineParameters(ADOCommand, SPParameters)
    End If

    ADOCommand.Execute , , adExecuteStream

    Set ADOCommand.ActiveConnection = Nothing          
    objStream.Position = 0
    RunSPGetXML = objStream.ReadText(-1)

    ...
End Function
-----------------------------------------------------------------------

I notice one of ExecuteOptionEnum property is adAsyncFetchNonBlocking.
Can anybody me whether I can solve my problem if using
adAsyncFetchNonBlocking.

thanks again

jj



> If your using ado, we had similiar problems and changed our record locking
> on our recordsets from pessimistic to optimistic locking which cleared up
> 90% of our problems.



> > we have a web application.

> > in the backend, two processes write data (from different sources) to the
> > same database at the same time. i use COM+ to control transaction for
> > each message received. The components call stored procedures to insert
> > message into different tables. within each sp, i use (READCOMMITTED) for
> > each SELECT statement.

> > in the frontend, when user uses IE to browse our site, there are
> > deadlocks happened. Browsing and writing data happen at same time. in
> > the frontend, we only read data (XML) from database and i use SELECT...
> > FOR XML AUTO(OR EXPLICIT) to fetch data and also use (READCOMMITTED) for
> > each SELECT statement.

> > before we have more deadlocks. i split sps into more small sps in the
> > backend in order to reduce the duration each sp take. it's getting much
> > better, but the deadlock still happens.

> > i read book online and try to follow the instructions to minimize
> > deadlocks, such as access object in the same order, use a low isolation
> > level...

> > in our error log, i get the following message:
> > source: RunSPGetXML (this is my function to run query)
> > message: -2147217887 - Transaction (process ID 59) was deadlocked on
> > {locl} resources with another process and has been chosen as the
> > deadlock victim. Rerun the transaction

> > I hope somebody can help to solve this problem

> > jj

 
 
 

1. Deadlocked by Deadlocks

I'm new to SQL and even newer to replication so this is really flustrating -
I have set up a merge publication and have successfully set up two push
subscriptions, the only problem is that the replication keeps getting
stopped by Deadlocks (Error 1205).  How do I stop these and can anyone tell
me what these are getting caused by?????

2. Listing tables in a filegroup

3. Deadlock situtation (deadlock victim) - help needed!!

4. Calculating Table Size

5. Your server command (process id 14) was deadlocked with another process and has been chosen as deadlock victim. Re-run your command

6. Postgresql Version

7. deadlocking

8. Restoring Analysis Services database from file

9. How do I avoid this one-table deadlock?

10. SQLServer 6.5 + ODBC Deadlock Problem

11. deadlock: csv output using isql via xp_cmdshell in trigger

12. deadlock and timeout