Thread: One transaction by connection - commit subdetails without release master transaction?
One transaction by connection - commit subdetails without release master transaction?
From
Durumdara
Date:
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
Re: One transaction by connection - commit subdetails without release master transaction?
From
Durumdara
Date:
Dear Anybody! I replace the long question to some shorter: As I see the PGSQL supports one transaction per connection. Is this information ok? Formerly I believed that only Zeos/PGDAC supports one trs/conn, but now it seems to be based on the server, and not the components... (The pgadmin is also showing one tx state/statement per connection) So if I want to make two transactions in client am I must duplify the connections in this period? Thanks for your help: dd 2012/2/17 Durumdara <durumdara@gmail.com>: > Dear PGSQL Masters! > > > 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 second connection = another transaction)? > > > Thanks for your help: dd >
Re: Re: One transaction by connection - commit subdetails without release master transaction?
From
Ben Chobot
Date:
On Feb 29, 2012, at 4:11 AM, Durumdara wrote: > Dear Anybody! > > I replace the long question to some shorter: > > As I see the PGSQL supports one transaction per connection. Is this > information ok? Yes, in postgres a connection can support only one transaction. If you wish to reduce the number of connections, look into a connection pooler, such as pgBouncer.
Re: Re: One transaction by connection - commit subdetails without release master transaction?
From
Alban Hertroys
Date:
On 29 Feb 2012, at 20:44, Ben Chobot wrote: >> As I see the PGSQL supports one transaction per connection. Is this >> information ok? > > Yes, in postgres a connection can support only one transaction. A small correction: a connection can support only one transaction _simultaneously_. You can serialise transactions on oneconnection just fine. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling.
Re: Re: One transaction by connection - commit subdetails without release master transaction?
From
Ben Chobot
Date:
On Feb 29, 2012, at 3:00 PM, Alban Hertroys wrote: > On 29 Feb 2012, at 20:44, Ben Chobot wrote: > >>> As I see the PGSQL supports one transaction per connection. Is this >>> information ok? >> >> Yes, in postgres a connection can support only one transaction. > > > A small correction: a connection can support only one transaction _simultaneously_. You can serialise transactions on oneconnection just fine. Heh, woops, yes, that's a good correction to make. :)