One transaction by connection - commit subdetails without release master transaction?

From: Durumdara <durumdara(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: One transaction by connection - commit subdetails without release master transaction?
Date: 2012-02-17 08:13:29
Message-ID: CAEcMXhmrKUNxQ9Z7+zCiWM3EB+K+4W-Jkz0kWp_xiTY0UjxJ7g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Dear PGSQL Masters!

I got a new project: porting a DBASE based application to PGSQL. Some
theoretic question I have before I coding/typing.

OS: Win, IDE: Delphi.

Preface:
Delphi uses TDataSet-s (every of them have records/rows).
When use Post method to save the actual record. In DBASE or flat table
systems the Post is instantly write the record into the file (isolation
level = commit on post). "Edit" or "Append" methods are usable for modify
the record.
These methods put "lock" on actual record to protect from other
modifications. After "Post" or "Cancel" these locks released.

The construction of the old application is this (and it is repeating with
many times, in many forms = "dialogs"):

1.) The edit form is opening a master item (table row) - with master
dataset, dbedits, grids, etc. For example: an order.

2.) The edit form is opening details (show them). For example: dispo
addresses, bill items, suborders, etc.

3.) Master dataset set locked with Table."Edit".

4.) You can edit the master...

5.) ... or you can edit any of subdetails, or basic data (for example
types, kinds, workers, etc) in other forms, and this instantly posted (post
= commit). So master kept in "Edit" state after I added a new address (but
the new address record saved in DB).

6.) After you finished the form closing, and Master Data posted. Then the
lock released.

Because this "lock" alive, never anyone change master or details at once
(conflicts resolved with this), only basic data (colors, types, kinds,
workers, etc.)

If trying to visualize this under PGSQL, or other RDBMS (except Firebird
with IBX/IBO), I everytime blocked on these things:

a.) Lock, protection on the "master" record
b.) Only one living transaction by connection

Firebird with IBX/IBO is allows you to make more living transactions.

In this case I can start a new transaction in every new subdetail form,
this not affected on main transaction (master).

begin; update master set id = id where id = :id

begin; insert into detail1 () values(); commit;
begin; update detail2 ...; commit;

master; commit

This update SQL locking the master and we can post/commit on any
subdetails, the subdata are flushed into db, no matter that later the
master rolled back or not.

But ZEOS, or PGDAC supports only one transaction by connection (and may
other dbs (ADO) too).

So if I want to use "lock for update", or "lock record" on master, then the
lock will vanishing when any of subdetails commited or rolled back (fail).

How can I protect the master? Can I create "subtransactions" that can
commitable without commit the master?

Other problem that I cannot change the mode of transaction, because it is
held on connection (AutoCommit = ???).
The bills, or heavily linked data must saved with "No AutoCommit" mode
(protected by transaction, all or none = subitems with master at once).
But these subdetails are commitable by rows, that is no matter.

I hope I describe well the situation.

Forms can open other Forms, and subforms can save subdata without releasing
master lock, or drop "master's transaction" (ok, in DBASE clones there is
no transaction, but may you understand it).

Because users used this method for this time, I must provide same under
PGSQL.

What do you thinking about this? Can I do this without doubling the
connections by users (if I duplicate the connections, the subforms can use
the seconds connection = another transaction)?

Thanks for your help: dd

Responses

Browse pgsql-general by date

  From Date Subject
Next Message sodik 2012-02-17 09:43:26 Re: Check if backup is in progress
Previous Message Amila Jayasooriya 2012-02-17 07:19:15 cast type bytea to double precision