2

I Know this question has been asked to death on SO, but I still haven't found anything that I could use. I am using MongoDB and am looking for MySQL's transaction equivalent- basically anything that lets me perform rollbacks in case of an error/failure. I found this which is somewhat close to the transaction system I designed, however, the comments suggest that performing a manual recovery of the system is a bad idea- which I agree too. Not only is it complicated, but it also seems to leave out a lot of corner cases, and a complete and successful recovery is not always guaranteed.

I don't have an application which relies heavily on transactions- we are just starting to integrate the whole concept of transactions to ensure ACID properties of the database in case it fails. MongoDB works perfectly for my Meteor application, however, I still need MySQL's transactions. A friend suggested adding another layer of MySQL underneath MongoDB, and moving everything that's transaction-related to MySQL. This sounds good in theory, however, I haven't found much literature that explains good practices about this topic, and hence seem to have a lot of doubts:

Does the transaction-related data come from MySQL or MongoDB? Do we use MongoDB as a cache and move everything to MySQL? Do we ensure that MongoDB mirrors MySQL periodically?

Any help/comments would be appreciated! Thank you.

Community
  • 1
  • 1
Surashree Kulkarni
  • 49
  • 7

3 Answers3

4

Multi-document ACID transactions are coming in MongoDB 4.0. Through snapshot isolation, transactions will provide a globally consistent view of data, and enforce all-or-nothing execution to maintain data integrity.

For more info, see https://www.mongodb.com/transactions

Grigori Melnik
  • 4,067
  • 2
  • 34
  • 40
1

I would say you would make a serious mistake to implement MySQL under or above MongoDB. This will be a hair ball and will probably cause a living hell for you. I have seen quite a few times where companies who did such a thing are scrambling to change their stack due to operational complications.

If you want speed on MySQL you should be using cache mechanisms such as memcache. MongoDB would not be a good choice for cache on top of MySQL either, it is too slow plus MongoDB isn't really designed to be cache.

If the only way you can see around your problem is really transactions (I see ways around transactions most times, and for it I get a much faster DB) then you chose the wrong tech, not sure what else I can say.

Sammaye
  • 43,242
  • 7
  • 104
  • 146
  • @SurashreeKulkarni tbh, using the right tech, 90% of projects fail due to unforeseen research (lack of normally) in getting the right techs together – Sammaye Aug 19 '15 at 20:45
  • @SurashreeKulkarni though now that I re-read what you asked, I see you meant what I do. Nomrally what I do is eventual consistentcy. Which means that I lazily reach consistent levels as required. With deletes I normally have a queue mechanism that runs the deletes over the course of days – Sammaye Aug 19 '15 at 20:47
  • could you explain 'eventual consistency' in some more detail? – Surashree Kulkarni Aug 19 '15 at 20:54
  • @SurashreeKulkarni so imagine you have a new user signup, they require a user record and maybe 2 "label" records and 1 "message" row. I will make the user record and the other rows I will just create without a transaction or anything. When the user comes to need the rows I check to see if they were created (app logic does all this) and if not then recreates them. – Sammaye Aug 19 '15 at 21:01
  • @SurashreeKulkarni the only times I have found transactions requiring is in mega restrictive cases like the bank account transactions that everyone uses as an example. Otherwise I have found that transactions are massive overhead that causes immense locking problems on larger dbs – Sammaye Aug 19 '15 at 21:03
0

You could write a transaction system yourself, which basically would use another Mongo collection to keep track of multi-phase commits. But there's a Meteor package that you might find useful, babrahams:transactions.

The official MongoDB site also talks a bit about two-phase commits that might be helpful to you. I imagine the babrahams:transactions package is doing something very similar.

ffxsam
  • 26,428
  • 32
  • 94
  • 144
  • Good to note that app level transactions are not a suitable replacement for DB consistency. – Sammaye Aug 19 '15 at 20:35
  • @Sammaye Of course, native DB transactions are always better than app level. But this is still acceptable, and better than nothing if one really needs to have this feature. – ffxsam Aug 19 '15 at 20:37
  • What happens if you wish to produce a cascading transactional delete? I do not think that extension covers that – Sammaye Aug 19 '15 at 20:38
  • I did look into two-phase commits, however, the example given there is trivial and I think that if a complete application were to use that, it would become really complicated, really fast. – Surashree Kulkarni Aug 19 '15 at 20:41
  • Try the package I linked to, it may work out fine for you. And @Sammaye, as far as cascading deletes: https://atmospherejs.com/matb33/collection-hooks – ffxsam Aug 19 '15 at 20:54