DB sync on application level

[Cornelinux K]

Hello,

I have an application that uses an SQL database with also many write access.

Now I am thinking of high availability. One solution was to set up a mysql master master replication.

But I was thinking, there might be some good aspects when doing the synchronization on the application level. You could use more than two partners, the setup *should* be simpler (a design goal ;-)

Are there any best practices or examples how this can be "easier" achieved with SQLAlchemy.

I.e. checking a lock on an entry, locking the entry on all sync parners, before updating this entry, unlocking on sync partners.

Recovery after a failure of a node...

Thanks a lot and kind regards

Cornelius

[Mike Bayer]

On 9/26/15 6:35 AM, Cornelinux K wrote:

Hello,

I have an application that uses an SQL database with also many write access.

Now I am thinking of high availability. One solution was to set up a mysql master master replication.

that's a good solution.

But I was thinking, there might be some good aspects when doing the synchronization on the application level.

I'm not familiar with any.   You can't do real HA synchronization at the application level because you are unable to control the transaction log ordering at that level.  You commit your transaction to database A, great, now your homegrown synchronization thing tries to send the same thing to database B, and deadlock!  write conflict!  other entirely garden-variety concurrency issues!  now your HA is Un-A. 

You could use more than two partners, the setup *should* be simpler (a design goal ;-)

totally simple setup, sure.   But the effort attempting to get any kind of result near something ordinary like Galera, vast, and even then it won't even work that well.

Are there any best practices or examples how this can be "easier" achieved with SQLAlchemy.

not really, you'd do something like intercept the execution events, record the statements being received, and then when the transaction is committed, sync all those operations out to the other system.   E.g. you'd track http://docs.sqlalchemy.org/en/rel_1_0/core/events.html#sqlalchemy.events.ConnectionEvents.after_execute and http://docs.sqlalchemy.org/en/rel_1_0/core/events.html#sqlalchemy.events.ConnectionEvents.commit, and probably a bunch of other events.

I.e. checking a lock on an entry, locking the entry on all sync parners, before updating this entry, unlocking on sync partners.

I'd install Galera.

Recovery after a failure of a node...

I'd put HAProxy in front of Galera.

Thanks a lot and kind regards

Cornelius

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Cornelinux K]

Hi Michael,

thanks for your response.

At least it shows me, that obviously this seems to be no common idea - mostly known as a bad idea.

I do not want to sync anything on a database level, but on a logical level. It is not important, that entries have the same index. 

In theory it would not even be important, that two nodes would ran the same database type.

Let's boil it down to a simple example of two nodes. Each node being an authentication system increasing the counter in the database entry on successful or failed authentication (for what it's worth - HOTP).

The authentication object exists on both nodes. The authentication request hits the first node.

The first node would have to check, if this object is locked.

If not, the first node would have to lock the object on the other nodes.

Then it performes the authentication an modifies the counters in the database object.

Then it transmits the changes to the other nodes and

...tells them to release the lock.

Sounds straight to me. (Maybe I am a bit naive.

The locking mechanism would also happen on the application level. But I was wondering if there are existing mechanism, tools or concepts to get started with.

Kind regards

Cornelius

[Mike Bayer]

On 9/27/15 5:17 PM, Cornelinux K wrote:

Hi Michael,

thanks for your response.

At least it shows me, that obviously this seems to be no common idea - mostly known as a bad idea.

I do not want to sync anything on a database level, but on a logical level. It is not important, that entries have the same index. 

In theory it would not even be important, that two nodes would ran the same database type.

Let's boil it down to a simple example of two nodes. Each node being an authentication system increasing the counter in the database entry on successful or failed authentication (for what it's worth - HOTP).

The authentication object exists on both nodes. The authentication request hits the first node.

The first node would have to check, if this object is locked.

If not, the first node would have to lock the object on the other nodes.

Then it performes the authentication an modifies the counters in the database object.

Then it transmits the changes to the other nodes and

...tells them to release the lock.

Assuming the nature of this "lock" is a row in a table, you can work with this concept using events.   If you want to keep things at the "ORM" level I suggest building your locking system into the before_flush event:  http://docs.sqlalchemy.org/en/rel_1_0/orm/events.html?highlight=before_flush#sqlalchemy.orm.events.SessionEvents.before_flush  this is event is the "master" event where you can access everything that is to be persisted and plan everything you want to do with locking, other nodes, whatever you want.  Other events like after_flush or after_commit can be used to release locks and transmit changes to other nodes.

Sounds straight to me. (Maybe I am a bit naive.

I encourage you to dive in, as I think most developers have at some point in their careers written application-level locking schemes of some kind or another.

The locking mechanism would also happen on the application level. But I was wondering if there are existing mechanism, tools or concepts to get started with.

I didn't find anything obvious on google, you can find various academic papers on it.   Mostly it's the kind of thing that is very specific to the task at hand and a developer will just work up whatever works for that specific situation.  More generalized articles about data consistency generally focus on database-oriented consistency models since these are fixed and widely understood.

[Jonathan Vanasco]

I don't like this idea.

but...

You should familiarize yourself with two-phase commits.  sqlalchemy supports this on mysql and postgresql.  basically everyone votes to commit yay/nay in phase 1, then a commit is made (if unanimous yays) or rollback executed in phase 2.

[Cornelius Kölbel]

Hello Jonathan,

thanks a lot for your comment and the hint with the two-phase commits.

Kind regards
Cornelius

> --
> You received this message because you are subscribed to a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/1kdv2pWCcGQ/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to

> sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

--
Cornelius Kölbel
corneliu...@netknights.it
+49 151 2960 1417

NetKnights GmbH
http://www.netknights.it
Landgraf-Karl-Str. 19, 34131 Kassel, Germany
Tel: +49 561 3166797, Fax: +49 561 3166798

Amtsgericht Kassel, HRB 16405
Geschäftsführer: Cornelius Kölbel