ansaurus

Question

Transaction commit executes succesfully but not getting done

Answer 1

+1 A:

You can use SQL Server Profiler to monitor the statements that occuring including begin and end of transactions. There are also some tools from Microsoft Support which are great since they run profiler and blocking scripts. I'm looking to see if I can find these will update if I do/.

If you have an open transaction you should be able to see this in the activity monitor, so you can check if there are any open transactions before you restart the server.

Edit

It sounds like this problem happens at roughly the same time every day. You will want to turn it on before the problem happens.

JoshBerke 2009-06-28 15:58:40

Thanks. But Profiler doesn't show any activity when its trace is begun after the transaction has begun.

reticent 2009-06-29 12:03:05

Exactly. I started the trace this morning, and I'm browsing it for something strange, or something about locks. Do you know anything I should check?

reticent 2009-06-29 14:02:16

Answer 2

A:

I suspect you are doing something wrong in code, do you have command timeouts set to a large enough value to do their work, or possibly an error is skipping a COMMIT?

You can inspect what transactions are open by running:

DBCC OPENTRAN

Mitch Wheat 2009-06-28 15:59:07

I didn't change command timeouts, and I'm sure the commit is executed (because I have 3 transactions getting committed at once, one in MySql, one in SqlServer and another in SqlCE; the other two transactions are committed and I can see the data).DBCC OPENTRAN shows an open transaction after the commit. I just don't know what to do now to get it committed.

reticent 2009-06-29 12:10:13

Can you cut down your code, small enough that you can post it here?

Mitch Wheat 2009-06-29 12:25:59

//All methods are called by webservice, which is called from a device project inside an SqlCE transactionpublic DataService(){ mySqlService = new mySqlService(); connection = new SqlConnection(connectionString); connection.Open(); transaction = connection.BeginTransaction();}... // Methods to transfer data - they use transaction public void Commit(){ transaction.Commit(); mySqlService.Commit(); connection.Close();} public void Rollback(){ transaction.Rollback(); mySqlService.Rollback();}

reticent 2009-06-30 12:47:29

Answer 3

+2 A:

You could run sp_lock and check to see if there are any exclusive locks held on tables you're interested in. That will tell you the SPID of the offending connection, and you can use sp_who or sp_who2 to find more information about that SPID.

Alternatively, the Activity Monitor in Management Studio will give you graphical versions of this information, and will also allow you to kill any offending processes (the kill command will allow you to do the same in a query editor).

Jeremy Smyth 2009-06-28 16:00:46

Thanks. I had to wait until it happens again - now.As Active Monitor shows, there's an open transaction associated with a process. And using sp_lock, I see there's about 7000 locks for this SPID. Is it abnormal? If so, how can I prevent it? Or is there a way I can commit this transaction? Because killing it isn't an option regarding my problem.Thanks again.

reticent 2009-06-29 11:55:44

That's quite a high number of locks! The ones you want to look out for are exclusive locks, marked as X, because they're the ones that block all access to the underlying resources. (IX, intent locks, stop others getting locks on parent objects; if this process has an X lock on a row, the page and table containing that row are IX locked). As for the greedy process, have a look at sp_who2 to find out where it's connecting from, and what the application is. That should help debug. Then you can use other techniques like SQL Profiler to fine-tune the debugging.

Jeremy Smyth 2009-06-29 12:26:19

72 of them are IX and all others are X. As sp_who2 shows, it's coming from my web service user, and it says the status is 'sleeping'. I have debugged my code last times, and I'm sure there's no exception. Is there any information I can/should check programmatically after executing every insert? Can I configure the transactions in any way to avoid these locks?

reticent 2009-06-29 12:52:30

1) Information you can check: there's a @@trancount variable you could read after each operation if you like, for debugging information. 2) You can configure a different transaction isolation level that doesn't honour locks, but trust me, you don't want to do that here!

Jeremy Smyth 2009-06-29 13:07:19

1) @@TRANCOUNT returns 0 even now. You mean if it's greater than 0, I should wait? I'm afraid it may timeout my web service.2)I didn't mean to ignore locks, I asked if there's a way to prevent them happening.

reticent 2009-06-29 13:15:20

@@trancount can only be read meaningfully from the session that created the transaction. If you read it from any other session, it shows any open transactions in that session (often zero).

Jeremy Smyth 2009-06-29 13:29:58

I'll change the web service to log that. I should wait until tomorrow to see the result. Thanks again.

reticent 2009-06-29 13:39:40

My log says trancount is always 1, probably because I'm reading it inside my transaction. I couldn't find any information about Sql sessions, in order to make sure I'm inside the correct one. (I've edited my first post)

reticent 2009-06-30 12:35:44

That would suggest you're not accidentally nesting transactions, which is a good thing to know! The next thing is to guarantee that all your transactions end in a COMMIT or ROLLBACK; your edited post suggests that if it's _one_ transaction holding everything open, it's that transaction that's timing out (or its connection closing) after a certain time. Is there a job or process that's unique to one part of your system? and not common to all the other client applications?

Jeremy Smyth 2009-06-30 12:43:12

Also, 4000 locks for _one transaction_ seems quite excessive! unless they're row locks in a single table, and SQL Server hasn't escalated them yet.

Jeremy Smyth 2009-06-30 12:44:09

I will log the begin and commit commands tomorrow. Activity Monitor shows there is one, but since every transaction is for one person and I see the data getting lost is from more than one person, I'm confused! Is this SPID (with all the locks) dedicated to one transaction? Could this have another reason?

reticent 2009-06-30 13:39:23

There's no separate process for parts of the system. Everytime the web service starts a transaction, I see an SPID coming. Actually the number of locks is close to the number of records missing, so you may be right about row locks. But I don't know what it means.

reticent 2009-06-30 13:54:00

Answer 4

A:

The timeout on your select indicates that the transaction is still open with a lock on atleast part of the table.

How are you doing transactions over web services? How / where in your code are you commiting the transaction?

Shiraz Bhaiji 2009-06-28 16:03:51

I'm using System.Data.SqlClient. I have a WebMethod called by the pocket PCs, which starts the transaction, inserts data and commits. The whole method is in a try/catch block, which rolls back the transaction in case of any exception. Did I answer your question?

reticent 2009-06-29 12:22:13

I'm sorry! I was wrong. I'm using MyGeneration.doodads (I use .Net provider for my SqlCe database). doodads has a TransactionMgr which handles the transactions. I changed the whole code to use .Net Sql provider today, to see if anything changes.

reticent 2009-06-30 05:23:16

Answer 5

A:

Doing lots of tests, I found out a deadlock is happening. But I couldn't find the reason, as I'm just inserting so many records in some independent tables. These links helped a bit, but to no luck:

http://support.microsoft.com/kb/323630

http://support.microsoft.com/kb/162361

I even broke my transactions to smaller ones, but I still got the deadlock. I finally removed the transactions and changed the code to not delete them from the source database, and didn't get the deadlocks anymore.

As a lesson, now I know if you have some (more than one) large transactions getting executed on the same database at the same time, you'll sure have problems in SQL Server, I don't know about Oracle.

reticent 2009-08-03 09:58:22

ansaurus

tags:

views:

answers:

Transaction commit executes succesfully but not getting done

related questions