Wednesday, January 23, 2013 10:54 PM
Our data warehouse ETL process executes approximately 100 different stored procedures once per day. We want to log some of the detail from these stored procedures, so we've created another stored procedure that will be called several times from within those 100 stored procedures, often to be called at the same time by multiple procedures. This auditing procedure will find the one proper AuditLog table row and make one of several types of data changes to it. In order to do so, each execution of the auditing procedure will need a date value, the calling procedure name, and the procedure's current completion status in order to find that one single row. (For example, "today," "dbo.MergeRetailers," and "successful.")
The boss is very concerned that we are introducing the possibility of concurrency issues (and ultimately a delayed or failed ETL process) when this one stored procedure is going to be called several hundred times per day. He believes there is a possibility that ETL procedure A could be calling the audit procedure to update Row X and be locked out because procedure B is in the process of calling that same audit procedure to update Row Z of the same table. With my non-existent knowledge of locking, blocking, and concurrency, I can't rule out this possibility.
I have Kalen Delaney's new book on SQL Server Concurrency, but I need to come up with an answer for my boss before I can read all 200 pages. Is it possible that an update to a single row of a table can cause another process that wants to update a different row of the same table to be locked out? What's the likelihood given that no concurrent AuditLog executions will ever be updating the same row?
And by the way, what's the effect of a lock? Would procedure B just need to wait for procedure A to complete (even though they're updating separate rows)? I would expect update executions against a locked row to be delayed until the update transaction completes, but my expectations are often wrong.
Thursday, January 24, 2013 12:02 AM
If a single AuditLog table row is accessed using the clustered primary key it will use a row-level lock. The lock will be held for the duration of the transaction, or released after the update if there is no transaction.
So different processes should be able to update different rows concurrently.
But why updates? A more common pattern is to only INSERT into a table used for logging.
Thursday, January 24, 2013 12:25 AM
Several hundred times a day isn't much on SQL Server, several hundred times a second might be!
Of course you may still have concurrency issues, but simple operations like you describe should be OK. In general, the first one in should block the second, which will wait and then execute normally.
Also want to second what David said, you should log by appending new rows rather than update old, yeah I know people like to update the last-run date or put starting and stopping times in the same rows, but neither is really good design.
- Marked As Answer by SQL Server dude Thursday, January 24, 2013 12:27 AM
Thursday, January 24, 2013 12:43 AM
Thank you both, David and Josh. I shouldn't have much problem confirming that in Books Online with a bit of reading.
Point well-taken on updating log rows. I agree completely, as I've pointed out here many times that we're going to need to search for the audit table primary key every single time the audit procedure is called. For some reason, we decided to go this other route. I don't think a single row can contain all the logging of a single procedure execution because many of our procedures perform multiple updates, multiple inserts, etc. We don't have a column for "Update1RowCount," "Update2RowCount," etc. Appending new rows is obviously the right way, especially when considering our concern for concurrency problems.
Slightly off-topic, I find myself putting up more arguments as the years go by. "That's not the right way to do it, we're inviting a list of potential problems!" Of course, my advice isn't always followed, and often I wonder if maybe my delivery was lacking or perhaps too inflexible. This was one of those times when I decided to myself, "they'll eventually come around, so I'll just try working with what they've concocted. If and when they realize I was right, I'll accept that gracefully, too."
Again, thank you both for your help.