Lock - SQL Server

eduardomendes · Post by **eduardomendes** » Mon 05 Apr 2010 12:44

when I try to Edit a table in SQL Server, the following error occurs: "Connection is busy with results for another command".

UniTable Configuration:
LockMode = lmPessimistic
RefreshOptions = [roBeforeEdit]
FetchAll = False

If I use FetchAll = True the error does not occur.

In PostgreSQL, it's not happen.

What's happening?

Dimon · Post by **Dimon** » Tue 06 Apr 2010 07:57

To solve the problem set the MultipleActiveResultSets specific option to True, like this:

Code: Select all

UniConnection.SpecificOptions.Values['MultipleActiveResultSets'] := 'True';

In this case current session is not blocked when using FetchAll = False, and it is not necessary for OLE DB to create additional sessions for any query executing. You can find more detailed information about this property in the SDAC help.

eduardomendes · Post by **eduardomendes** » Tue 06 Apr 2010 12:16

Hi Dimon,

Aafter set the MultipleActiveResultSets specific option to True, the error changed.

The following error occurs: "New transaction is not allowed because there are other threads running in the session."

I'm useing SQL Server 2008.

Dimon · Post by **Dimon** » Wed 07 Apr 2010 08:37

The problem is that under MARS, starting transactions, setting savepoints, rolling back to savepoints and committing transactions isn't allowed when there is even one request which is actively running under a transaction. SDAC tries to set a savepoint before locking and therefore an error is arised.
To solve the problem you should set LockMode to lmNone or set FetchAll to True.