Re: Reliable and fast money transaction design

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ron Johnson wrote:
> On 08/29/07 09:34, Decibel! wrote:
>> 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.
>
> Argh!!! The RDBMS that I typically use defaults to SERIALIZABLE.

SERIALIZABLE is really slow :). You should look into SERIALIZABLE only
for those transactions that need it. There is also SELECT FOR UPDATE.

Joshua D. Drake

>

- ---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFG1ZOLATb/zqfZUUQRAl5UAKCf8cli24MMOjxsKlel5nEFXllGsgCeIfDn
eg5BSlRpUlTGgGA7tBbx3EM=
=ynMx
-----END PGP SIGNATURE-----