ACID (was Re: Reliable and fast ...)

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

On 08/29/07 10:40, Joshua D. Drake wrote:
> Ron Johnson wrote:
>> On 08/29/07 09:34, Decibel! wrote:
>>> On Wed, Aug 29, 2007 at 08:37:26AM -0500, Ron Johnson wrote:
>>>>
>>>> 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.

We use SERIALIZABLE (with all it's locking "issues") to guarantee
the I in ACID. ISTM that READ COMMITTED can only deliver "ACD".

- --
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFG1ZVYS9HxQb37XmcRAlopAJ9wvAovDcqvUpsj5dqSrum+/3QUbgCeODwL
a8BJm6gi7VnR6dWgtmTLkcM=
=eg1s
-----END PGP SIGNATURE-----