Envers and database replication (master-master)

[nikovan]

Hi All!

As far as I understand @RevisionNumber field must be formed from a strictly increasing sequence. That's a mandatory requirement for having the AuditReader logic to work correctly. This requirement normally doesn't cause any problem for most situations, but it does however makes AuditReader practically unusable when you're building a distributed system which is using a master-master database replication.

My question is - is there a way for getting envers to compare revision timestamps rather than revision numbers when querying the AuditReader?

The problem lies with generating a globally unique numbers between separate database instances. It's easy to generate a unique number, but almost impossible to generate it in a strictly increasing sequence across multiple geographic locations.

Just to illustrate - this is how the merged RevisionInfo could look like after the changes have been replicated between databases:

Revision from database A: rev=500, timstamp=1/1/2011

Revision from database B: rev=50, timestamp=2/2/2011

Because the logic is based on max(rev) then, for example, auditReader.getRevisionNumberForDate(3/3/2011) will return 500 which is incorrect, but if you use max(timestamp) then you will get the correct result - 50.

Can anyone comment on a best option for resolving that, please?

Many thanks,

Ivan

[hernanbolido]

Hi!

I think that the restriction is that each revision number must be greater than the latest one (1,2,5,10 is ok). This doesn´t implies that the secuence must be strictly increasing (1,2,3,4...).

We are using timestamp based revision number. I remember we had a discussion here: http://community.jboss.org/message/534513#534513.

In fact, there is a potential problem using only one database and clustered application servers. In this scenario there are n session factories and there could be revision numbers colissions.

Well, I hope it will be helpful.

Regards. Hernán.

[nikovan]

Thanks Hernán!

Using timestamp is probably a reasonable idea - I'll give it a go. There's still a potential problem of simultaneous updates in different databases, but that's probably not a big issue for us.

Regards,

Ivan

[adamw]

Let me know if it works for you.

If not, maybe some "revision ordering strategy" should be introduced?

In fact timestamps can differ between servers as well . To be able to properly order the revisions, some kind of synchronization between the DBs would be needed anyway.

Btw. what database are you using?

Adam

[nikovan]

Adam,

This problem is actually trickier than it seems :-)

We're using Oracle databases and they are actually geographically distributed around the globe (that's why the replication is in place, i.e. this is not clustering). You can't always synchronise DBs time correctly because of the connectivity could be down, network has it's latency etc. Basically, different nodes should be completely autonomous and replicate their content only when the connectivity is available.

The only revision ordering strategy I could think of is probably to sort them by timestamps. That's obviously not a 100% bullet-proof solution (unsynchronised time etc), but it should work in most of the cases for us since the databases are not updated too often. As with any replication-related problem any conflicts would have to be resolved via some tools, e.g. the user will have to manually point to the correct revision if you have the same data updated at about the same time on several databases.

Thanks,

Ivan

[adamw]

Hmm so to do a write the DBs don't have to be connected? How do you make sure there are no conflicts? Or is it the reason for using Envers?

Anyway, I guess in this situation a composite id would be best, consisting of: timestamp + node id. Sorting lexicographically. Don't you think so?

Adam

[nikovan]

Yes, this is not a two-phase-commit type of transactions. That's why I think Envers could be useful avoiding the conflicts.

There still going to be the replication update conflicts due to timing/latency/downtime, but that could be managed by using the exception log in oracle.

I agree, the composite id is probably the best

Thanks,

Ivan

Message was edited by: Ivan Liakhovenko - fixed typo

[adamw]

So maybe then you could create a feature request for Envers to support composite revision entity ids? And/or create a pull request?

Adam