Re: Strange locking behaviour

Brian McCane <bmccane(at)mccons(dot)net> writes:
> 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 ;

What exactly does the "Insert" step do? Why are you bothering to UPDATE
stocks.nextupdate twice in this transaction? It's not like any other
transaction will be able to see the now + 1 day setting. Do you really
mean to update *all* rows of stocks here?

> 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 ;

Again, what exactly does the "Insert" step do? Does it change
stocks.nextupdate? (One would hope so, else there's a tight loop here.)
If it does, won't it block at that point waiting for the other
transaction's updates to be committed?

regards, tom lane