July 29, 2008 at 2:06 am
I'm a newbie when it comes to transactions, locking and isolation levels. I've read many articles about them but still I'm having a hard time understanding which one to use when. I'd like to ask for your help about two transactional operations I'm doing if I'm doing right or wrong.
First operation
1. Transaction created by ADO.NET. No isolation level set, default is used.
2. A few selects from different tables. Single rows each. No table hints used. These tables will be heavily used by others. The selects used by others also doesn't have any table hints.
3. Inserts into two tables. Single rows each. No table hints used. These tables will be heavily used by others. The selects used by others also doesn't have any table hints. Inserted data will be used by others.
4. Updates inserted data. Single table, single row. Transaction committed after update.
Second operation
1. Single stored procedure with transaction code. No isolation level set, default is used.
2. A few selects from different tables. Single rows each. No table hints used. These tables will be heavily used by others. The selects used by others also doesn't have any table hints.
3. Inserts into single table. Single row. Inserted data will not be used by others. Transaction committed after insert.
My concerns are:
First one: Inserted data should not be read by others until it's updated. Is it safe like the way I'm doing or should I start using table hints/read committed transactions on every select which uses that table?
Second one: That transaction will be heavily used. Will it problem when two users insert data at the same time? Will one insert lock other out or make other one wait?
I'm clueless about these and I couldn't find anything with my searches. I appreciate any help.
July 29, 2008 at 5:34 am
More often than not, locking is handled just fine by SQL Server. Don't start adding lock hints until you run into a problem, and possibly, not even then. Usually adjusting the code and or indexing solves a lot more problems than hints do. Query hints are very dangerous things.
The isolation level default, READ_COMMITED, handles most situations well. However, if you are concerned about concurrency and you're willing to sacrifice a bit of processing power and some space in the tempdb, then going for READ_COMMITED_SNAPSHOT.
Transactions are a bit trickier. By default, and this can be changed, there is an implicit transaction on the database call, but you can issue an explicit transaction to more specifically control access. Be cautious when using client side transactions (transactions controlled through ADO or whatever db access you're using) so that you don't put user interactions or anything in the middle of the transaction. Keep your transactions as small as you possibly can, just to limit the amount of information that is locked at any one time. Using SNAPSHOT or READ_COMMITED_SNAPSHOT gives you some flexibility around this.
Other than that, if you hit specific issues, post them here where there are some really great people (Gail, Jeff, Jack, GSquared, to name only a few) that will help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply