Re: Reliable and fast money transaction design

On Wed, Aug 29, 2007 at 08:37:26AM -0500, Ron Johnson wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> On 08/29/07 07:27, cluster wrote:
> > OK, thanks. But what with the second question in which the UPDATE is
> > based on a SELECT max(...) statement on another table? How can I ensure
> > that no other process inserts a row between my SELECT max() and UPDATE -
> > making my SELECT max() invalid?
> > 
> > A table lock could be an option but I am only interested in blocking for
> > row insertions for this particular account_id. Insertions for other
> > account_ids will not make the SELECT max() invalid and should therefore
> > be allowed.
> 
> Well, concurrency and transactional consistency *allows* other
> processes to update the table after you start your transaction.  You
> just won't *see* their updates while you're inside of a transaction.

Just make sure and read up about transaction isolation... in the default
of READ COMMITTED mode, you can sometimes see changes made by other
transactions.
-- 
Decibel!, aka Jim Nasby                        decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)