I am running 7.2 on FreeBSD-current. I have an web page that I have been
playing with for tracking stocks online. It has a table which contains
all of my calculations. Once a day I pull down a report from MarketWatch
of all stocks that meet a criterion I have defined. Once every 5 minutes
I download new stock data, and compute buy/sell recommendations. Anyway,
that is all working. Now for my problem:
I download my list from MarketWatch and then go and pull historical
data for all the new stocks in the list. The program that gets the
historical data does:
BEGIN WORK ;
SELECT stockid FROM stocks WHERE ticker = 'M$FT' FOR UPDATE ;
UPDATE stocks SET nextupdate = NOW + INTERVAL '1 DAY' ;
....Insert 7 years of stock data....
UPDATE stocks SET nextupdate = NOW() ;
COMMIT WORK ;
The program that gets the 5 minute updates does:
SELECT stockid FROM stocks WHERE nextupdate <= NOW() ;
-- and for each stockid it finds above
BEGIN WORK ;
....Insert new data...
COMMIT WORK ;
Looks simple enough, right?
Anyway, while the historical data INSERTs are occurring, no other
application can insert data into any of my tables, so my 5 minute update
sits there and diddles around forever, and I don't get recommendations.
My understanding from reading the docs is that 'FOR UPDATE' gets a 'ROW
SHARE' lock, and updates and inserts get a 'ROW EXCLUSIVE' lock. I cannot
understand why all my apps, except the historical data app, are showing
'waiting' in a 'ps' command. Is the problem that after I do the fist
"UPDATE" command it promotes the lock to 'ROW EXCLUSIVE', and then the 5
minute update cannot even look at the value of 'nextupdate' until after
the inserts have completed? I set 'nextupdate' to tomorrow specifically
to prevent the 5 minute update from doing anything until the historical
data is available.
Any ideas?
- brian
Wm. Brian McCane | Life is full of doors that won't open
Search http://recall.maxbaud.net/ | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"