February 2, 2011 at 2:01 am
Dear Database professionals,
I'm an application manager myself, so this may sound like a dumb question to you:
Is there any way to enforce the (with ROWLOCK) hint?
I'm running SQL server 2005 (9.0.4035) on a Windows 2003 server.
We have an application that reads lines on one connection, and updates them (sets the line-number) though another. This works just fine 99.9% of the time. It's the .1% that my issue is with.
In those cases, the select statement will show an 'ASYNC_NETWORK_IO' wait type, while the update statement is waiting for the select statement.
This will happen biweekly during an invoice run, while a different batch of invoices will have run minutes earlier.
What I think is happening is that SQLServer escalates the lock on the select statement so that it will not release the lock on the line that has just been read.
The application will not get the next row out of the resultset until the one that was just read has been updated (ASYNC_NETWORK_IO on select), while the update statement is waiting for the read-lock to be released (LCK_M_IX wait type, blocked by select statement)
We have previously run this application with a posgressql database, and this issue didn't occur, and the occurrence seems to go down after we have implemented the rowlock hint.
In my opinion, this validates my hypothesis on what causes the issue.
Now we come to my question (and the subject of this topic): Is there any way to enforce the rowlock hint? (and save me from either having to go against company policy and install a non-microsoft DB or tying up our application developer for months)
If you have another way to solve my problem, that would be welcome too. 😀
Kind regards,
Martin van Velzen
Application management (company withheld)
The Netherlands
February 2, 2011 at 2:47 am
In most cases, Async network IO waits are cause by applications that are slow to handle the resultset returned by sQL. Can be something like complex processing between each row, depends.
Very small portion of cases, it's poor network throughput. It's rare, because it's hard to max out modern networks.
Lock escalation is done to reduce the resources required for locking. There are ways to disable lock escalation (no, I'm not going to tell you how), but if you do that and SQL runs out of lock memory as a result (very possible if you're forcing rowlock on large numbers of rows), it could means that SQL will not process any other requests.
I would start by looking at the app that's consuming that select statement and make sure that it's not doing anything strange while receiving the data. An app should consume the entire result set, then do other processing. That way sQL can then carry on with other requests and other work and not hold locks and consume resources waiting for the application to take what it initially asked for.
Honestly, in this case I'd suggest drop the hints entirely and consider reworking the application process. It doesn't sound efficient, it doesn't sound like good design
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2011 at 4:17 am
Gail,
First of all, thanks for replying.
GilaMonster (2/2/2011)Honestly, in this case I'd suggest drop the hints entirely and consider reworking the application process. It doesn't sound efficient, it doesn't sound like good design
I'd like to do a redesign of (this part of) the application. But it is in production, and will likely take months, even if I do get it through management.
GilaMonster (2/2/2011)
Lock escalation is done to reduce the resources required for locking. There are ways to disable lock escalation (no, I'm not going to tell you how), but if you do that and SQL runs out of lock memory as a result (very possible if you're forcing rowlock on large numbers of rows), it could means that SQL will not process any other requests.I would start by looking at the app that's consuming that select statement and make sure that it's not doing anything strange while receiving the data. An app should consume the entire result set, then do other processing. That way sQL can then carry on with other requests and other work and not hold locks and consume resources waiting for the application to take what it initially asked for.
The amount of data is (for a database) not that significant IMO, at most 20k rows. What happens in the app in pseudo-code is
- select key, data from table where export-indicator = 1
- while not end of resultset
- update table set seqNr = ?? where key = selected
- write data to file
- end while
I can see two options of changing the process to avoid my problems:
- using the same connection for the select and the update. The problem is that the select would be performed after every row was updated, and IMO doing a 'top 1' select up to 20k times is not very efficient either. 🙂
- storing the entire resultset in memory before performing the update. Now while I don't think 20k rows is a lot of data for a database, I do think it's a lot of data to read into application memory, and I'd need to ensure the application server is up to it.
GilaMonster (2/2/2011)
In most cases, Async network IO waits are cause by applications that are slow to handle the resultset returned by sQL. Can be something like complex processing between each row, depends.Very small portion of cases, it's poor network throughput. It's rare, because it's hard to max out modern networks.
That's what I said isn't it? The application is very slow to process the next line of the resultset because it's waiting for the lock on the previous line to be released. Glacial would be faster than the application in this case. 🙂
To me, this looks like a deadlock, where part of the lock is not in SQL server.
The network is not an issue, it's used less than .5% on both the application and the database server
Kind regards,
Martin van Velzen
February 2, 2011 at 4:29 am
mavelzen (2/2/2011)
The amount of data is (for a database) not that significant IMO, at most 20k rows.
Not significant to a database, but SQL will escalate row locks to table after somewhere around 5000 locks. It's not an exact number, it depends on a large number of things, just a ballpark figure. Given that number I would strongly suggest not trying to disable lock escalation.
- storing the entire resultset in memory before performing the update. Now while I don't think 20k rows is a lot of data for a database, I do think it's a lot of data to read into application memory, and I'd need to ensure the application server is up to it.
Without knowing the details of what you're doing, this sounds like a good solution. How big are the rows in question? How often does this process run?
Maybe you could partition the process, select 2000, update 2000, repeat? (Again, I'm guessing since I don't know the details here)
Maybe if you could give some more details of what you're querying and what the update does, maybe we could come up with another alternative.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2011 at 6:13 am
Gail,
I'm going to attach the create table statement and a data-sample.
This is one of the tables of our interfacing environment.
PD_Id contains the primary key, a combination of the dataset ID and a sequence number
the columns pd_cj_id and pd_fj_id contain job IDs, and aren't the problem here.
The pd_ds_id column contains the id for the dataset.
PD_SeqNr is the field to be updated.
PD_FSeqNr is the sequence number that is assigned to the row when the dataset is first inserted into the database, and never changes
pd_sc_code is the field that is used in the where statement of the select
pd_message contains the actual data
1. When a dataset gets sent to the backend system, the sc_code field is filled with a certain string.
It is important that the PD_SeqNr field is updated, as this is the key for the returning status in
step 2
2. At some point a monitoring application returns either OK or error from the backend system for a
certain PD_SeqNr of a certain dataset and updates this table.
3. Once all lines have been acknowledged, the interface environment allows the end-user to view
(and edit) the data for the lines that received an error, and resend just these lines with step 1.
4. Repeat until no more lines receive an error.
Kind regards,
Martin van Velzen
February 2, 2011 at 6:31 am
Will take a look at this tomorrow (if I get a chance). I have asked if any of the other regulars here would be able to take a look.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2011 at 11:21 am
Judging by your sample data, 20k rows will need a buffer of a bit less than 9 Mbytes; that's something I would normally regard as perfectly acceptable for a client-side buffer. So I think Gail's suggestion that the app should consume the whole dataset before starting the updates is a good one. If 9MB is too big, then Gails suggestion of partitioning it into several smaller batches is a good way out.
You are using a low isolation level (or your app wouldn't work at all) so may think it desirable to keep locks until just before the update (while you have this crazy two connection operation) if there is some other application that may be updating the same data at about the same time, but that of course is what is triggering the lock escalation.
What is the calculation that delivers the new value for PD_SeqNr? Is it something that could be done in SQL? If so you could use a single UPDATE or MERGE statement to do the whole thing, with an OUTPUT clause to deliver the updated rows to the app so that it could write them to a file. This would avoid any lock conflicts between the read and update parts of the task, so it wouldn't matter if the rows delivered by the output clause were consumed slowly. But that approach might create problems if errors are expected in individual row updates.
Tom
February 3, 2011 at 2:02 am
Tom,
Thank you for looking at this for me.
Tom.Thomson (2/2/2011)
Judging by your sample data, 20k rows will need a buffer of a bit less than 9 Mbytes; that's something I would normally regard as perfectly acceptable for a client-side buffer. So I think Gail's suggestion that the app should consume the whole dataset before starting the updates is a good one. If 9MB is too big, then Gails suggestion of partitioning it into several smaller batches is a good way out.
That should be OK then. Allthough up to 20 of these processes may run at the same time, 20K lines is an exception. The average is more like 1K lines
Tom.Thomson (2/2/2011)
You are using a low isolation level (or your app wouldn't work at all) so may think it desirable to keep locks until just before the update (while you have this crazy two connection operation) if there is some other application that may be updating the same data at about the same time, but that of course is what is triggering the lock escalation.
Right.
Tom.Thomson (2/2/2011)
What is the calculation that delivers the new value for PD_SeqNr? Is it something that could be done in SQL? If so you could use a single UPDATE or MERGE statement to do the whole thing, with an OUTPUT clause to deliver the updated rows to the app so that it could write them to a file. This would avoid any lock conflicts between the read and update parts of the task, so it wouldn't matter if the rows delivered by the output clause were consumed slowly. But that approach might create problems if errors are expected in individual row updates.
The 'calculation' is simply a linenumber.
If you can point me at a resource that tells me how to do a set-based update with a different value for each line in the set, I'd be very grateful. I think this would solve my problem, as I'd no longer need to use two statements (or two connections either).
Kind regards,
Martin van Velzen
February 3, 2011 at 9:27 am
mavelzen (2/3/2011)
The 'calculation' is simply a linenumber.If you can point me at a resource that tells me how to do a set-based update with a different value for each line in the set, I'd be very grateful. I think this would solve my problem, as I'd no longer need to use two statements (or two connections either).
Kind regards,
Martin van Velzen
If the linenumber is the linenumber within the batch of updates it's very easy. Code like
BEGIN TRAN
;WITH rows as (
SELECT PD_id, ROW_NUMBER() over (ORDER BY PD_id) as rownum FROM Table)
MERGE INTO Table USING rows ON rows.PD_id = Table.PD_id
WHEN MATCHED SET PD_SeqNr = rownum
WHEN NOT MATCHED BY TARGET <raise an error - you can't get here>
; -- this semicolon is mandatory with the MERGE statement
COMMIT TRAN
If you want to return a rowset containing the updated rows to the caller,
add an OUTPUT clause immediately before the final ";"
OUTPUT PD_SeqNr, PD_id, other columns
-- have to select all the columns you want in the order you want them
-- presumably PD_SeqNr doesn't come first
I hope all that is clear and understandable (and correct).
Tom
February 3, 2011 at 9:34 am
Merge on SQL 2005?
Working on an UPDATE version, but keep getting distracted by real work
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2011 at 9:45 am
GilaMonster (2/3/2011)
Merge on SQL 2005?Working on an UPDATE version, but keep getting distracted by real work
Drat, I forgot it was sql 2005 ;-). I'll think about an update-based version
Tom
February 3, 2011 at 9:57 am
Here's an SQL 2005 version
UPDATE T SET T.PD_SeqNr = R.rnum
OUTPUT inserted.*
FROM Table T, (
SELECT PD_id, ROW_NUMBER() OVER (ORDER BY PD_id) as rnum
FROM Table where < condition defining which rows are to be updated >
) R
WHERE R.PD_id = T.PD_id
I notice that the SQL 2008 version I posted forgot the where clause in the definition of the auxiliary table, so wouldn't have worked anyway.
And UPDATE is much simpler, even if it is a T_SQL only feature that makes this work.
Tom
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply