MSSql lock problem

[sin_dragan]

Hi, I writing one program who uses a lot of sqlquery components, placed on one DataModule. Recently I started to get the next error:

Code: [Select]

EMSSQLDatabaseError
Error 20019
Attempt to initiate a new Adaptive Server operation with results pending

I didn't get this error until my project is almost complete, so now I have everything set up, I m using SqlDb. Im also using master/detail with 3 queries, maybe that is the problem, can someone help. I think sqldb uses tds library.

EDIT: I found some reference to this error:
http://lists.freepascal.org/fpc-devel/2014-March/033494.html

http://lists.freepascal.org/pipermail/fpc-devel/2014-November/034903.html

Can someone take a look at this?

[sin_dragan]

I have found some more info about this problem on this page
http://www.freetds.org/faq.html#pending

Code: [Select]



The client library is tightly coupled to the server; they are synchronized, share state information.
The server requires the client either to read all the results from a query, or to indicate that no further rows are desired i.e., to issue a cancellation.

[b]Until one of those two things happens, the server will not accept new queries on that connection. It will complain about "pending results".[/b]

How do mortal programmers cope with this strict one-query-at-a-time limitation? For one thing, they become better programmers.

    The solution often involves doing more SQL work, which often results in less interaction with the server, which is almost always faster.
    Even when that's not true, there's nothing to prevent the application from caching the whole results set in its own collection object.
    Sometimes, the best answer is to open a new connection.
That's not as bad as it sounds, and it certainly not unheard of to have 4 or more simultaneous connections supporting an application.


Until one of those two things happens, the server will not accept new queries on that connection. It will complain about "pending results".

So, how do I set a cancelation after I query for needed results, or to read all results, I already set PacketRecord on -1 on all my sqlqueries, but it doesn't solve the problem.

[taazz]

That is the biggest downside of the explicit transaction requirement of sqldb. In your case use something else since the existing mechanism makes it impossible to close a transaction with out freeing the client side data as well. Take a look on ZeosDB component if they support MSSQL and if they do not have the same requirement.

[sin_dragan]

thx taazz for your reply, I tried to put 10000 records on PacketRecord and it seems that the problem is gone (for now), I now face the issue with transactions.

I get the error messages:

Error 20018
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

I though that everything is set on autocommit, I do update one query at the time.

Is there a right way to use transactions with sqldb for MSSql, I mean on manual side.

For example: to start every with BeginTransaction and finish with Commit?

Please help

[LacaK]

thx taazz for your reply, I tried to put 10000 records on PacketRecord and it seems that the problem is gone (for now), I now face the issue with transactions.

or set PacketRecords:=-1; to bypass "pending results" error message

Error 20018
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

There is fix in FPC 3.0.1 which will execute ROLLBACK only if there is active transaction

I though that everything is set on autocommit, I do update one query at the time.

If you want autocommit, then set in TMSSQLConnection.Params: "AutoCommit=1"

[sin_dragan]

Thx lacak, AutoCommit solved my problem.

About rollback fix, can someone direct me to that ticket in mantis, I tried to find it, but I couldn't.

[LacaK]

About rollback fix, can someone direct me to that ticket in mantis, I tried to find it, but I couldn't.

There is no bug report. Patch was applied directly to SVN.