Problems with long stored proc with transaction

Question

Problems with long stored proc with transaction

fasttrack

SSCertifiable

Points: 6071
More actions
March 31, 2006 at 1:14 pm

#111607

Hi guys,
I have built a stored proc which inside has a loop (while begin end) performing insert and update on lots of table. I have used a loop to keep the same code and changing the table name and others parameters.
I have also added a transaction, if the process fails, the tran rollback all operations done on every table run before the failure(insert, update). Inside the loop there are some code which access to some tables in master DB (these are used to build the parametric code).
The transaction begin before the loop and commit outside the loop:
BEGIN TRAN
    WHILE
         BEGIN
               sql code to build update and insert for every table
         END
COMMIT TRAN.

The problem is: Running the stored proc without Transaction, it is completed (it takes time, but it finishes the execution).
If the transaction is Setted, the stored seems to hang up on big table (where insert and update lots of records) and goes everlasting.
Do you know some suggestions about how to run the stored procs without problem.
Some time out on running query may be possible?
Thank in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Are you getting errors in your SQL Server logs? Have you run profiler/perfmon while this is happening? I would guess that the process gets to a point to where it becomes memory constrained and is waiting for lock resources to free up. SQL Server must obtain a lock in order to perform the updates/inserts. That lock could be a row/extent/page or table lock, but each time it aquires a lock, SQL Server must set aside memory to manage that lock. Check your locks setting in sp_configure. If it is set to zero, which is default, SQL Server will only use 2% of your total memory for lock management. SQL Server will eventually allocate up to 40% for locks as required, but it will not go above this. Run Perfmon and check the SQL Server locks. It sounds like this transaction is too large and filling up the lock space. Why can't you commit after each table?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

fasttrack SSCertifiable Points: 6071 More actions · Answer 2

We have same opinion. I think this is a lock problem and memory, in fact when sql server hangs on a table; write/read on disk is null, it means that some lock may occur.

I do not commit after each table because this table are linked between them with FK, keeping integrity between them, commit must be at the end.

to check locks is not easy because sql server responde very slow and working on this issue is not easy.

Anyway this my next step to do...

Any suggestion will be very appreciated.

thank

fasttrack SSCertifiable Points: 6071 More actions · Answer 3

Making some tests I have partially solved the problem with locking hints, now the performance get better.

thank