Re: Lost updates vs resumable connections/transactions

Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:

> As said, open transactions with DB locks during user interaction are a known
> bad idea for every sort of application. That together with the scaling problems
> is IMHO reason enough not to implement something that is designed to avoid
> proper application side advisory locks.

I think it's a higher level divergence of opinion here. What he's talking
about is more like the Java/J2EE approach of building lots of infrastructure
to make everything work magically. Our instincts are to keep things simple and
avoid big hammers for features that would be hard to manage.

His way lies things like session caches and the rest of the J2EE type world.
Things work the way the textbooks say they should, full ACID semantics are
maintained for sessions across multiple processes even multiple servers, and a
good implementation would let you set policies for when sessions expire and
transactions roll back.

Our way lies things like PHP/Perl with stateless individual requests. Every
request is limited to what it can do with limited state and maintaining all
the rep invariants of the database.

Keeping things simple limits you a lot but it makes things so much easier to
manage. It's kind of like a Database version of the same principle described
here:

http://www.schlossnagle.org/~george/blog/archives/269_Why_PHP_Scales__A_Cranky_Snarky_Answer.html

> Get used to put reasonable amounts of your business logic into stored
> procedures on the database side and you will find that dealing with advisory
> locks is not as painfull as it looks like. Doing it all with PHP coding alone,
> where a single business process is scattered over a input form flow dictated
> number of source files is neither as easy, nor as maintainable.

Eh. You can do that just as easily in the application. You write functions
that issue all the database queries you want to call. Often you can get all of
this intelligence into just a few objects with well defined interfaces. If
you're lucky you can get nearly all of it into a single object that represents
an arbitrary database record, which subclasses for each table that add only a
little intelligence.

I find trying to stuff things into stored procedures actually makes it
*harder* to abstract all this stuff away and harder to maintain. Languages
like PL/SQL or plpgsql are so limited and awkward that it's hard to express
anything more than straightforward procedural functions. They end up being
simple wrappers around the same SQL calls you would have made anyways.

Either way, the one thing you're trying to avoid is having SQL strewn about
every file. You want to push all the SQL down below an abstraction barrier so
that everything dictated by the front-end UI is well separated from the stuff
dictated by the database. The more abstract the interface between the two
layers the better. But how abstract it can be depends heavily on the
application.

--
greg