December 15, 2003 at 12:14 am
Greetings,
I have a database that is locking when a batch of T-SQL runs. This sql batch has begin transaction and commit transaction for every 500 records. When I try to browse the database using EM, when the batch is running, I receive the following error "lock request timeout period exceeded"
I have debugged the query batch by removing the BEGIN and END TRANSACTIONS. The database does not lock.
Yes! Ok! I know that there are FAST FORWARD cursors involved but I cannot spend time re-writing. Does anyone know why the transactions lock the database. QA does the same when the batch is running.
Here is the configuration.
Datacentre 2000
SQL Server 2000 sp3
Memory 6.144 GIG (sql server)
Unisys E7000 X 8 Processors 1.6 GHZ XEO
Andy.
Andy.
December 15, 2003 at 9:56 am
Can you post the query, or at least parts of it to give us an idea of what it is doing?
December 17, 2003 at 11:20 am
I had a situation where my process was fast enough in getting from the COMMIT TRAN back to the BEGIN TRAN that queries weren't being processed.
My solution was to put a WAITFOR after my COMMIT TRANSACTION - this allowed the database to move on to other requests, instead of simply plowing through mine.
Also, I think (but am not certain) that, when a stored proc includes transactional logic, that the stored proc essentially acts as though it started w/ a BEGIN TRAN and ended with a COMMIT TRAN; I have, again, at least seen that as a behavior.
R David Francis
R David Francis
December 17, 2003 at 11:35 am
I wouldn't simply remove BEGIN and END TRANSACTIONS. BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent. If errors are encountered, all data modifications made after the BEGIN TRANSACTION can be rolled back to return the data to this known state of consistency.
Did you try to query tables the batch has updated? You can query table with hint (NOLOCK) from QA if you don't mind some dirty data be returned.
Edited by - allen_cui on 12/17/2003 11:36:49 AM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply