Distributed Transactions

59 views
Skip to first unread message

Thadeus Burgess

unread,
Oct 29, 2009, 5:39:09 PM10/29/09
to web...@googlegroups.com
Massimo,

Would you mind giving a more in depth example on distributed transactions with postgres? I know web2py supports them, however there is not much information on it. How would you accomplish a join across databases?

Is there an api to abstract this?

dba = DAL('postgres...')
dbb = DAL('postgrse...')

dba.define_table('customers'....)
dba.define_table('employees'....)
dbb.define_table('products'....)
dbb.define_table('sales'.....)

db = DISTRIBUTED_DAL(dba, dbb)

rows = db(db.customers.id == request.vars.cust_id)(db.sales.id == request.vars.sale_id).select()

for row in rows:
   print row.customer.name, row.customer.address, row.sale.time, row.sale.product.name

-Thadeus


mdipierro

unread,
Oct 29, 2009, 5:46:42 PM10/29/09
to web2py-users
There is a single function. In your case you would call

dba.distributed_transaction_commit(dba,dbb)

or

try:
dba.distributed_transaction_commit(dba,dbb)
except:
session.flash="distributed transaction failed and rolled back"

I have not tried this in some time but when I tried worked fine.
postgresql only.

Thadeus Burgess

unread,
Oct 29, 2009, 5:54:16 PM10/29/09
to web...@googlegroups.com
So you would have to insert records for each object individually, then call distributed_transaction_commit.


dba.customers.insert(....)
dbb.sales.insert(....)

dba.distributed_transaction_commit(dba, dbb)



How would you perform a join?

dba(dba.customers.id == dbb.sales.id_customer).select()

for row in rows:
   row.customers.name, row.sales.total
   row.update_record(sales.tax=1.08)

dba.distributed_transaction_commit(dba,dbb)

-Thadeus

mdipierro

unread,
Oct 29, 2009, 6:14:15 PM10/29/09
to web2py-users
On Oct 29, 4:54 pm, Thadeus Burgess <thade...@thadeusb.com> wrote:
> So you would have to insert records for each object individually, then call
> distributed_transaction_commit.
>
> dba.customers.insert(....)
> dbb.sales.insert(....)
>
> dba.distributed_transaction_commit(dba, dbb)

yes

> How would you perform a join?

You cannot cross database joins.

Thadeus Burgess

unread,
Oct 29, 2009, 8:23:31 PM10/29/09
to web...@googlegroups.com
Is this a limitation of using a distributed database system, or just not having support in the DAL for this yet.


-Thadeus
$

mdipierro

unread,
Oct 29, 2009, 9:18:16 PM10/29/09
to web2py-users
I am not aware of any any system that allows joins across databases. I
do not think it is a well defined concept since if information reside
in distinct places there is no way to guarantee referential integrity.

In web2py you can have tables in distinct with records that reference
each other using integer keys and you can use the a record pulled from
one to reference a record pulled from another and you can use
distributed transactions tot make sure things do not get messed up.

Massimo



On Oct 29, 7:23 pm, Thadeus Burgess <thade...@thadeusb.com> wrote:
> Is this a limitation of using a distributed database system, or just not
> having support in the DAL for this yet.
>
> -Thadeus
> $
>

Thadeus Burgess

unread,
Oct 29, 2009, 10:08:30 PM10/29/09
to web...@googlegroups.com
So if two tables need to be joined often, keep them on the same database.

Cool, thanks! :)

-Thadeus

mdipierro

unread,
Oct 29, 2009, 11:26:59 PM10/29/09
to web2py-users
Since we are discussing this. I have added distributed transaction
support for mysql and firebird (postgresql was already there).
Example:

db1=DAL('postgres://...')
db2=DAL('mysql://...')
db3=DAL('firebird://...')

SQLDB.distributed_transaction_begin(db1,db2,db3)
db1.table.insert(....)
db2.table.insert(....)
db3.table.insert(....)
SQLDB.distributed_transaction_commit(db1,db2,db3)

I would appreciate help with testing this.


On Oct 29, 9:08 pm, Thadeus Burgess <thade...@thadeusb.com> wrote:
>  So if two tables need to be joined often, keep them on the same database.
>
> Cool, thanks! :)
>
> -Thadeus
>
Reply all
Reply to author
Forward
0 new messages