Re: Serializable transaction restart/re-execute

On 4/6/15 6:42 AM, Bill Moran wrote:
> CREATE OR REPLACE FUNCTION mytest() RETURNS integer AS $$
> BEGIN
>      update account set balance = balance+10 where id=1 RETURNING balance;
> END
> $$
> LANGUAGE SQL;
>
> of course, it's unlikely that you'll ever want to wrap such a
> simple query in a function, so I'm supposing that you'd want
> to do something else with the old value of balance before
> updating it, in which case:
>
> CREATE OR REPLACE FUNCTION mytest() RETURNS integer AS $$
> DECLARE
>      cc integer;
> BEGIN
>      SELECT INTO cc balance FROM account WHERE id = 1 FOR UPDATE;
>
>      RAISE NOTICE 'Balance: %', cc;
>      perform pg_sleep(3);
>
>      update account set balance = cc+10 where id=1 RETURNING balance INTO cc;
>
>      return cc;
> END
> $$
> LANGUAGE plpgsql;
>
> The FOR UPDATE ensures that no other process can modify the
> row while this one is sleeping.
>
> Now, I understand that you want to don't want to do row locking,
> but this is (again) an insistance on your part of trying to
> force PostgreSQL to do things the way GTM did instead of
> understanding the RDBMS way of doing things.

Actually, the entire point of SERIALIZABLE is to avoid the need to mess
around with FOR UPDATE and similar. It's a trade-off. If you have a
large application that has lots of DML paths the odds of getting
explicit locking correct drop rapidly to zero. That's where SERIALIZABLE
shines; you just turn it on and stop worrying about locking.

The downside of course is that you need to be ready to deal with a
serialization failure.

I *think* what Fillpe was looking for is some way to have Postgres
magically re-try a serialization failure. While theoretically possible
(at least to a degree), that's actually a really risky thing. The whole
reason you would need any of this is if you're using a pattern where you:

1 BEGIN SERIALIZABLE;
2 Get data from database
3 Do something with that data
4 Put data back in database

If you get a serialization failure, it's because someone modified the
data underneath you, which means you can't simply repeat step 4, you
have to ROLLBACK and go back to step 1. If you design your app with that
in mind it's not a big deal. If you don't... ugh. :)
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com