Locking tables: Multi-table inserts 1

[skicamel]

Between a 'begin transaction' and 'commit transaction', will the database lock all tables involved until the commit transaction is executed?

I've seen various posts about locking single tables, but the proc I'm using touches a few dozen tables. If the 'transaction' doesn't lock all of the tables, what is the best route to make sure the transaction goes through completely without any changes made apart from what the transaction calls for?

 

[SemperFiDownUnda]

there are a number of things you can do to ensure that your process is as friendly as possible.

Basically you only want what is needed to be locked to be locked for the shortest amount of time possible.

While just wrappering your entire proceedure in a transaction would work it isn't very multi-user friendly.

Look here for guidelines. If yo need specific things to occur like reading one table to get some data to update other tables and you don't want the source data to be changed during that time then you can specifically lock that table.

SQL Server ultimately locks pages when updating data. SQL server will escalate the type of lock used if it deems it more efficient. 99% of the time you don't have to worry about this and as long as you adhere to some basic principals things should go fine.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_md_06_3eba.asp

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[skicamel]

That's a very useful link. Thanks, Wayne.

We acquired a little over a dozen companies some time ago. Each billing system was in a separate SQL database. I'm transferring the databases into our main billing database. The main transfer scripts I use are during off hours and haven't been an issue. I've got 4 databases left to transfer.

We began offering a new service last month that has to be in our main database. A number of our existing customers have signed up for this service. As a result, Sales/Cust Svc have had to manually transfer customers, thus losing histories & 'misleading' some financial reports. I created a transfer button in the interface that calls the 'single-customer transfer' proc. I've been wrapping the procedure in a transaction to be sure if anything goes through, it all goes through, and if there is an error, I can roll it back (There is a ton of bad data in some of these other databases.)

For a single transfer, it did appear it would lock up the database during a single tranfer for 10-20 seconds. Not very user friendly, but I'd hear no complaints as it saved employees the hassle of manually transferring, and saved those of us fixing the improperly transferred accounts a few headaches. Plus, these existing transfers are down to 5-10 a day.

The single transfer proc was written so concurrency wasn't much of an issue, but I've been playing with the code to speed it up, and can cut the time to about 1/5th of the original transfer time. But the code changes would need to have no other transactions processed during the transaction.

I expect to have all of the legacy databases transferred withing the next three weeks, so this won't be an issue shortly. If wrapping the proc in a transaction will work, I think I'll stick with that, as even the optimized code is over a thousand lines.

Thanks for your time, Wayne. Much appreciated.

Dan

 

[skicamel]

Side note: I haven't been back here in some time. Odd not seeing Terry's name up in the MVP list. He still pop in here from time to time?

 

[SemperFiDownUnda]

Glad to help.

If you are not worried about multi user friendlyness wrapping the whole thing in a transaction often doesn't slow your procedure down (unless it needs to rollback). For a short term situation it sounds like you are doing the right thing as you could spend tons of time trying to optimise it but the savings wouldn't warrent it.

Good luck

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first