January 25, 2006 at 2:41 pm
Hello, I have a problem with locking when I run my stored procedure within a transaction. Here is my script:
DECLARE @ERROR INT
BEGIN TRANSACTION import_all_files
EXECUTE @ERROR = dbo.ImportAllFiles
IF @ERROR <> 0
BEGIN
ROLLBACK TRANSACTION import_all_files
END
ELSE COMMIT TRANSACTION import_all_files
(Note that running without a transaction works fine)
This import sprock in turn calls a BCP sprock that utilizes extended stored procedures which run a bulk copy from file to table. The resulting SQLDMO_1 process then locks on my original EXECUTE process. Has anyone seen transaction problems with DMO?
January 26, 2006 at 4:56 am
I may be reaching here but, when you start an explicit transaction with BEGIN TRANSACTION the server will place all following operations in the transaction until you execute COMMIT TRANSACTION (IE it will not start any other transactions automatically).
This differs from the Autocommit mode where each statement would be committed on completion.
As such, when using an explicit transaction, I think it will be holding on to the Locks on the tables used until the transaction is Committed (or rolled back) and thus increase the chances of a locking problem (the more you try to do in one transaction, the greater the chances). Whereas, in autocommit mode, it would drop the lock after each statement.
I am unsure of a good solution for this problem, except to try and use multiple transactions, at a more granular level.
(Marvin)
January 26, 2006 at 10:33 am
I've wrapped many sprock executions within transactions and never had a problem until the transaction contained the "sp_OA-" extended stored procedures.
January 27, 2006 at 1:04 am
Suggest you move your transactions down to a more granular level to try to spot exactly where it is getting a problem.
Assumption would be that some other process is using the same tables for the OLE objects, possibly calling the same sp_OA... stored procs.
(Marvin)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply