April 21, 2005 at 10:42 am
Hi all,
We are having a problem with a missing COMMIT of a transaction in our application. The client/server app uses a ODBC connection for a SQL Server 2000 db.
Under heavy CPU load (or with tons of ODBC traffic) the COMMIT after the DELETE statement does not seem to get executed succesfully and the transaction stays open, which leads to other problems. For some reason unknown to me, our app sends the DELETE statement to ODBC wrapped in a T-SQL "BEGIN TRANSACTION DELETE... ...COMMIT". The INSERTS are sent as simple "INSERT INTO...".
Specifically, the problem appears when the application does a delete (between 1-200 rows) and afterwards inserts (also between 1-200 rows) for a single table. I took a brief look with the Profiler and found that the COMMIT is missing:
Normal:
SQL:BatchStarting BEGIN TRANSACTION DELETE From x WHERE UserID = 21; COMMIT;
SQL:StmtStarting BEGIN TRANSACTION
SQL:StmtCompleted BEGIN TRANSACTION
SQL:StmtStarting DELETE From x WHERE UserID = 21;
SQL:StmtCompleted DELETE From x WHERE UserID = 21;
SQL:StmtStarting COMMIT;
SQL:StmtCompleted COMMIT;
SQL:BatchCompleted BEGIN TRANSACTION DELETE From x WHERE UserID = 21; COMMIT;
Problem:
SQL:BatchStarting BEGIN TRANSACTION DELETE From x WHERE UserID = 21; COMMIT;
SQL:StmtStarting BEGIN TRANSACTION
SQL:StmtCompleted BEGIN TRANSACTION
SQL:StmtStarting DELETE From x WHERE UserID = 21;
SQL:BatchCompleted BEGIN TRANSACTION DELETE From x WHERE UserID = 21; COMMIT;
Does someone have similar experiences or maybe an idea how this problem could be solved? I reckon we could at least drop the extra BEGIN TRANSACTION and see if it helps...?
Thanks,
-Olli
April 21, 2005 at 1:12 pm
What application programming language are you using?
- That platform will give you the answer you need!
Just out of curiosity, Does that target table has a delete trigger ?
* Noel
April 22, 2005 at 2:17 am
App runs on C++ and the database connections use old Visual Studio 6 objects. The target table does not have a delete trigger or triggers of any kind, db data changes are done by the server sending SQL. Btw, what makes you so sure that it's a application-related problem?
-Olli
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply