October 24, 2012 at 11:01 am
We use an ERP application for transaction processing (Oracle JD Edwards) and the backend is SQL server 2008.
The application failed to commit a transaction to the database table - and now when we query on that table for that particular record, it takes forever to return any result. When we stop the query, it does return some 13 lines where we should be getting 14 lines back.
So when we change the query option SET transaction Isolation level to READ UNCOMMITTED, it immediately returns us 14 lines - without any delay.
We somehow need to commit this 14th line only to our table - we can't do it through the application anymore because the application just keeps on crashing if we want to get any line out of this transaction.
Let me know if you didn't understand the problem completely.
October 24, 2012 at 12:19 pm
it sounds like the open transaction is holding a lock that is blocking the other transaction. You need to either commit, or rollback that first transaction to release the lock(s).
An easy way to do this, is to start your second attempted transaction (which wont finish). While it's "waiting" execute the proc "sp_who2". This will return a list of all processes running on the db server. Look through and find processes where the column "BlkBy" is not equal to 0. These are processes which are "blocked by" another process. The value will be the "blocking" process. Find a process that is "blocking", but not "blocked by" anything, and that is your "lead blocker". So fo example, if process 51 is shows BlkBY process 52, and process 52 shows BLKBy nothing, 52 is your lead blocker an probably the process with the uncommitted transaction. You can then "kill 52" to kill that process.
October 24, 2012 at 12:21 pm
NJ-DBA (10/24/2012)
it sounds like the open transaction is holding a lock that is blocking the other transaction. You need to either commit, or rollback that first transaction to release the lock(s).An easy way to do this, is to start your second attempted transaction (which wont finish). While it's "waiting" execute the proc "sp_who2". This will return a list of all processes running on the db server. Look through and find processes where the column "BlkBy" is not equal to 0. These are processes which are "blocked by" another process. The value will be the "blocking" process. Find a process that is "blocking", but not "blocked by" anything, and that is your "lead blocker". So fo example, if process 51 is shows BlkBY process 52, and process 52 shows BLKBy nothing, 52 is your lead blocker an probably the process with the uncommitted transaction. You can then "kill 52" to kill that process.
hit submit to soon... you can also run "dbcc opentran" to show the oldest active open transaction.
October 24, 2012 at 12:49 pm
Thank you NJ-DBA - I found the process that's been blocking it.
NJ-DBA (10/24/2012)
NJ-DBA (10/24/2012)
it sounds like the open transaction is holding a lock that is blocking the other transaction. You need to either commit, or rollback that first transaction to release the lock(s).An easy way to do this, is to start your second attempted transaction (which wont finish). While it's "waiting" execute the proc "sp_who2". This will return a list of all processes running on the db server. Look through and find processes where the column "BlkBy" is not equal to 0. These are processes which are "blocked by" another process. The value will be the "blocking" process. Find a process that is "blocking", but not "blocked by" anything, and that is your "lead blocker". So fo example, if process 51 is shows BlkBY process 52, and process 52 shows BLKBy nothing, 52 is your lead blocker an probably the process with the uncommitted transaction. You can then "kill 52" to kill that process.
hit submit to soon... you can also run "dbcc opentran" to show the oldest active open transaction.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply