July 24, 2008 at 12:37 pm
So - I am doing a transaction across 3 tables, which have a parent child relationship. So I want to wrap the whole thing in a transaction and commit at the end. I'm doing this from code, because the data is all object based and I wanted to keep the structure....but I assume I would get this in the DB as well..
I'm opening a transaction - and stepping through it in the code in debug mode.... what I find is that while its in the middle of the transaction, I can't select anything from the table that touches that row..
For e.g. I'm trying to insert a row into the customers table, where the firstname is 'John'.... when it is in the middle of the transaction, I can't do a select on the table that resembles anything to 'John' - as in, I can't do something like
select * from Customers
where firstname like 'John%'
I understand that on a day to day basis, this won't be a problem, because no code is doing what I would do during debug (stop in the middle) - and most inserts should happen very fast... but I wanted to know what the best practice in this scenario is....
July 24, 2008 at 1:16 pm
That is the behavior you should get. When you are inserting or updating a record, you should be unable to select the same record. In SQL Server, you also have lock escalation. That means that if you lock a substantial portion of a page, the entire page may be locked and if you lock a substantial portion of a table, the entire table may be locked. Escalation is for the sake of performance - locking a substantial portion usually means you will need to lock the remainder of the records, so the get locked pre-emtively to reduce the change of deadlocks and reduce the resources necessary to take individual row locks.
Managing blocking can be done in many ways, but the first and most important is to keep the amount of time anything is locked to a minimum. So, as you have said, don't wait for a dialog while a record is locked.
In addition, in high-transaction systems, structuring your table to put things together that will get locked by the same process and things that will have concurrent reads and writes from different processes away from each other. If you have 10 account executives accessing their accounts at the same time, having a clustered index on the table that somewhat resembles the way they have their work split up will reduce the chances of one AE updating a record on the same page the second AE is trying to read.
July 25, 2008 at 8:23 am
From what you described, you're not necessarily seeing a table lock. Have you tried taking a look at sys.dm_tran_locks? That will give you a lot of information about what is occuring during your udpates. You should also hit the books and read up on locking.
"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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply