Thread: Transactional sql

"Chad Thompson" <chad@weblinkservices.com> writes:

> Is anyone aware of a good website that would give examples of
> transactional sql statements.

I would recommend reading Bruce Momjian's excellent book on
PostgreSQL.  It is available on the web at the following location:

http://www.ca.postgresql.org/docs/aw_pgsql_book/index.html

In particular there is a section on Transactions and Locks that should
clear up what the fuss is all about.  The book has been published by
Addison Weseley so besides being available on the Internet the book is
also available from most fine booksellers (hint hint).

The canonical example is as follows.  Let's pretend that you run a
bank and you wish to transfer money from my account to yours.  Without
transactions that would be written as:

UPDATE account SET moolah = moolah - 100 where id = 'jason';
UPDATE account SET moolah = moolah + 100 where id = 'chad';

The problem with this is that it is possible that something could go
wrong with one of these statements.  For example, if someone unplugged
the computer right after the first statement then I would lose $100
without you getting paid.  Bankers don't like that sort of thing.
They want to make sure that either both statements succeed or that
both fail because together these two statements make up one
*transaction*.  Using transactions in PostgreSQL is simple.  Here's
the same statements wrapped in a transaction.

BEGIN;
UPDATE account SET moolah = moolah - 100 where id = 'jason';
UPDATE account SET moolah = moolah + 100 where id = 'chad';
COMMIT;

Now PostgreSQL will guarantee that either both of the statements will
succeed, or that they will fail together.  You can even change your
mind on an open transaction by issuing an ABORT command.  This will
cause PostgreSQL to "roll back" all of the stuff that it did in the
transaction.

Now, some "databases" will tell you that this sort of stuff can be
done at the application level, and that is true, to an extent.
However, it is not a trivial excercise, and doing it right requires
knowing a whole lot more about hardware and low level programming than
you or I are likely to ever want to learn.  That's the beauty of using
PostgreSQL to store your data.  All you need to do is group your
transactions together with BEGIN and COMMIT statements (make sure you
read the bit about deadlocks), and you can let the PostgreSQL
developers worry about the hard bits.  If you are really interested
you should read the HACKERS list for information about WAL and MVCC
and a host of other acronyms, but that isn't really necessary.

> I would also be interested in examples of batching sql statements
> together (i.e. query on the results of a query)

This is fairly straightforward as well.  It is possible in PostgreSQL
to create tables from your queries (See the documentation for SELECT
INTO) these tables can even be designated as "temporary tables" that
disappear when your connection goes away.

> Thanks
>
> Chad

Hope this was helpful,
Jason