March 14, 2011 at 3:05 pm
We have an asp.net/c# web application running at a client.
Just recently we rolled out some changes, one of which was a mod to a sql procedure that pulls the clients Orders from a sql table and returns to an aspx page.
Normally no problems until I added a Left Outer Join. Now they get this often :
Could not continue scan with NOLOCK due to data movement
We had to roll back my changes by the way until I find a better solution.
Stored proc code :
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Select
buy_sell=
CASE when ord.buy_sell=1 then 'Buy'
when ord.buy_sell=2 then 'Sell'
END,
ord.amount,
ord.amt_unit,
<more columns here..>
s.ticket as tick_strat,
s.dependent as dep,
s.sort_string as sort,
into #temp
FROM filter_list flst, users u, order_fx ord
LEFT OUTER JOIN order_strategy s
ON s.ticket = ord.ticket
WHERE
status not in ('EXECUTED','FILLED')
AND u.client = @client
AND ord.client = @client
order_strategy table is getting a lot of activity on it. For example a sql job regularly deletes the records and rebuilds the table (not the actual table structure, just the rows).
This must be causing the problem along with the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED .
Any ideas.
Thank you !!!
Bob
March 14, 2011 at 3:32 pm
This is expected. It happens under these circumstances:
From: http://support.microsoft.com/kb/815008
This behavior may occur when a row in a table is deleted between the time SQL Server reads the location of the row from an index and the time SQL Server fetches the row.
Short version, long story, stop deleting things out while other things are using them. How often do you reset the lookup table? Another option is to force it to go to the clustered index so you don't have this glitch between the NC-Index seek and the keylookup on the one or two queries you're having problems with. I don't recommend this solution permanently but it'll get you up and running while you DO fix it...
... which involves redesigning how you're using the lookup table and/or changing your consistency and isolation levels.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 15, 2011 at 8:57 am
Hi Craig - Thanks for your response.
The bad news is that my order_strategy table gets rebuilt by a Sql job every couple of minutes.
The good news is that I can remove the order_strategy join from my stored proc query quite easily, and attack my problem from a different angle.
On the client's QC boxes, it appeared that the outer join - LEFT OUTER JOIN order_strategy s - was NOT causing any problems; and that by using Read Uncommitted Sql Server wouldn't be concerned with the Deleted records on order_strategy table. Boy was I wrong !
At the same time, it's odd how the problem truly revealed itself only AFTER the changes went in production - at which time all the users logged on to perform their work. In the QC environment there were only a few users testing.
In the end I would rather NOT go with the quick-fix, as you also suggested.
If there was a way to keep my order_strategy table without deleting it every few minutes, perhaps that would be a better solution...
Regards,
Bob
March 15, 2011 at 11:31 am
I personally am not sure why you're completely re-building order_strategy every few minutes, but if you'd like to expand on the logic there, I'm sure one of us will be able to help you come up with some alternate solutions.
Another option would be to fast load what you need into a #tmp table before trying to use it in your existing procs, but again, my personal opinion is that's a workaround while you get it fixed.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 16, 2011 at 10:54 am
Yes, good point Craig. I am not crazy about this idea of rebuiling order_strategy each time either. I just found that if I don't clear out those records (and there's usually less than 40 or 50), I may have some stragglers hanging around in there and it throws the display off a bit.
i.e. order_strategy forces certain type of parent/child orders to "stick" together on the main order board. The parent is the "00000" record, and the sort_string allows child orders to be grouped with the parent (regardless of its ticket sort).
Ticket Dependent Sort_String
844986000000 C 0.0999 WEB844985844986
844987844986C 0.0999 WEB844985844986844987844987
844988844986C 0.0999 WEB844985844986844987844988
And yes, smarter logic would probably solve this.
I am in fact using a #temp table, but I'm also using a TRUNCATE prior to my final insert. I think the Truncate is causing some issues where my other procedure has a READ UNCOMMITTED setting.
This is the final three lines of my proc:
truncate table order_strategy
insert into order_strategy select * from #tmp_ord_strat
drop table #tmp_ord_strat
I also tried :
delete from order_strategy where ticket not in (select b.ticket from #tmp_ord_strat b)
but it's commented out with a note that it may be causing problems.
thanks for your time again...
Bob
March 16, 2011 at 12:03 pm
Bob,
You may want to check out using a synonym in your data retrieval queries and have 2 tables for order strategy. I've used it successfully on a couple of occasions for this sort of thing, but not in a real rapid-fire environment.
You can't ALTER a synonym, you have to drop and re-create it, which could have a concurrency problem if some data retrieval occurs on another SPID between the DROP and CREATE statements. What I did to prevent this is create a little semaphore table that procedures would read before issuing the read query, and if I was about to switch the synonym (where I would set the semaphore to true before doing the switch), the procedure would wait a very short time before using the synonym in a query, which would give me time to DROP and CREATE it on the other table.
The sequence was:
1. Build the alternate table.
2. Set the semaphore to true.
3. Drop and re-create the synonym.
4. Clear the semaphore
Todd Fifield
March 16, 2011 at 12:08 pm
Is the OrderStrategy table session-specific?
Kind of like a temp table, but multiple processes access it from multiple connections?
If so, there are easy solutions for that, which don't involve truncate or complex delete processes, and which allow for MUCH better concurrency and performance with MUCH fewer errors and glitches.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 16, 2011 at 12:13 pm
tfifield (3/16/2011)
Bob,You may want to check out using a synonym in your data retrieval queries and have 2 tables for order strategy. I've used it successfully on a couple of occasions for this sort of thing, but not in a real rapid-fire environment.
You can't ALTER a synonym, you have to drop and re-create it, which could have a concurrency problem if some data retrieval occurs on another SPID between the DROP and CREATE statements. What I did to prevent this is create a little semaphore table that procedures would read before issuing the read query, and if I was about to switch the synonym (where I would set the semaphore to true before doing the switch), the procedure would wait a very short time before using the synonym in a query, which would give me time to DROP and CREATE it on the other table.
The sequence was:
1. Build the alternate table.
2. Set the semaphore to true.
3. Drop and re-create the synonym.
4. Clear the semaphore
Todd Fifield
What you're describing is essentially a "mutex" (mutual exclusion). If you're going to use one of those, you can skip the whole synonyms thing entirely and use it control access to the table.
There's almost certainly a simpler process than trying to build thread safety in T-SQL. It can be done, but it's a serious concurrency-killer, and that can have major bad effects on user-experience.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2011 at 9:52 am
Am I missing something here?
The fundamental problem is running at READ UNCOMMITTED. No READ UNCOMMITTED, no problem.
If you're getting this error, you a reading uncommitted transactions.
Also, a thought:
FROM filter_list flst, users u, order_fx ord
LEFT OUTER JOIN order_strategy s
ON s.ticket = ord.ticket
You are mixing old and new syntax. That isn't a good idea. In this case, it looks like (unless you have eliminated some of the WHERE clause when you posted), you are performing a cross join on filter_list, users, and order_fx, then OUTER JOINing that to order_strategy. This seems likely to be producing many more rows in your result set than you intend.
March 17, 2011 at 12:44 pm
Thank you for your response.
I have been considering changing the READ UNCOMMITTED TO READ COMMITTED (which I understand to be the Sql Server default).
I only put the UNCOMMITTED in this proc below with the assumption that it would never get locked up in the event some other process was locking/updating one or more records. i.e. I'm not concerned if a dirty read occurs.
I can confirm that I am NOT getting additional records that I do not expect. I can call this proc manually via a sql qry window and the record set look fine.
Here's the full WHERE clause in my proc :
SELECT
DISTINCT
buy_sell=
CASE when ord.buy_sell=1 then 'Buy'
when ord.buy_sell=2 then 'Sell'
END,
ord.amount,
ord.amt_unit,
ord_stat = dbo.Calc_Ord_Color2('sort', ...,
dbo.get_mkt_rate(ord.cur_1,ord.cur_2,ord.order_type, ord.buy_cur, @fxo_bidask) ),
<more columns here..>
into #temp
FROM order_fx ord, filter_list flst, users u
WHERE
(ord.status = @status or
(@status = 'Accepted' and -- these are active orders
(ord.status = 'Modified' or
ord.status like '%- Request' or
ord.status like '%- Pending' or
ord.status like '%- Rejected'))
OR
(@status = 'Pending' and
(ord.status like '%- Pending%' or
ord.status like '%- Request') and
ord.status <> 'Modify - Request')
)
AND (flst.lcCurrency ='ALL' OR (flst.lcCurrency = 'BYCURR'
AND (ord.buy_cur=flst.Bycurr OR ord.sel_cur=flst.ByCurr) )
OR (flst.lcCurrency='BYPAIR' and ord.cur_2 = flst.ByPair2 and ord.cur_1 = flst.Bypair1))
AND (flst.lctrader = 'ALL' OR (flst.lctrader = 'BYTRAD' AND ord.user_id = flst.ByTrad) OR ord.user_id='WEB' )
AND flst.filter_id = @filter_id
AND (ord.user_id = u.user_id OR ord.user_id='WEB')
AND u.client = @client
AND ord.client = @client
March 17, 2011 at 12:50 pm
:blink:
That is some complex join logic you've got going there.
Let's get back to the original design flaw, a table that's getting dumped and rebuilt every few minutes. Under what circumstances does this table rebuild itself?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 17, 2011 at 1:11 pm
GSquared (3/16/2011)
What you're describing is essentially a "mutex" (mutual exclusion). If you're going to use one of those, you can skip the whole synonyms thing entirely and use it control access to the table.
There's almost certainly a simpler process than trying to build thread safety in T-SQL. It can be done, but it's a serious concurrency-killer, and that can have major bad effects on user-experience.
Gus,
As I said I never tried this in a really rapid-fire type environment. I probably wouldn't use it if there was a possibility of another process finding the mutex cleared just before I set it and issuing the read statement just after dropping the synonym but before I re-created it. (This is the only place it could fail I think)
I am familiar with mutexes. I used to write device drivers in PDP-11 assembly language.
Todd Fifield
March 17, 2011 at 2:01 pm
bob mazzo (3/17/2011)
Thank you for your response.I have been considering changing the READ UNCOMMITTED TO READ COMMITTED (which I understand to be the Sql Server default).
I only put the UNCOMMITTED in this proc below with the assumption that it would never get locked up in the event some other process was locking/updating one or more records. i.e. I'm not concerned if a dirty read occurs.
The problem is that you ARE getting a dirty read, and that's what is causing the error. You are correct, READ COMMITTED is the default isolation level, and this error does not occur at that level.
If you must read and you are having trouble with concurrency (ie you get excessive blocking) you might want to consider READPAST - READPAST will, as it says, read past locked rows, however if other transactions are holding large numbers of locks this may not work for you.
March 17, 2011 at 2:06 pm
Hi Craig,
It's a sql job that schedules the order_strategy table rebuild.
i.e. order_strategy forces certain type of parent/child orders to "stick" together on the main order board. The parent is the "00000" record, and the sort_string allows child orders to be grouped with the parent (regardless of its ticket sort).
Ticket Dependent Sort_String
844986 000000 C 0.0999 WEB844985844986
844987 844986 C 0.0999 WEB844985844986844987844987
844988 844986 C 0.0999 WEB844985844986844987844988
I'm looking at immediately changing the TRUNCATE to DELETE FROM Order_Strategy, and also wrapping the Delete around a transaction. Then changing up my main stored proc to have a Read Committed (i.e. the stored proc that reads from Orders and sends them back to an aspx page).
And I also have this small CASE embedded in the main SQL statement which looks at Order_strategy fields (see the S alias where I appaned the '(d)' for a dependent order):
TICKET=
CASE
WHEN ord.tick_lnk2 !=0 THEN LTRIM(STR(ord.ticket))+ '(o)'
WHEN ord.sub_type = 'LOOP' THEN LTRIM(STR(ord.ticket))+ '(l)'
WHEN ord.tick_lnk1 !=0 and ord.sub_type !='LOOP' THEN LTRIM(STR(ord.ticket)) + '(d)'
WHEN ord.tick_lnk1 =0 and s.ticket is not NULL THEN LTRIM(STR(ord.ticket)) + '(d)'
ELSE LTRIM(STR(ord.ticket))
END,
thanks again.
Bob
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply