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