3

I have a question about transactions in arangodb, if I run below AQL query, will it be executed as one transaction or will it be separated into two transaction? My backend is php:

LET r1 = (FOR u IN Users UPDATE u WITH { status: "inactive" } IN Users)
LET r2 = (FOR b IN Blogs UPDATE b WITH { status: "inactive" } IN Blogs)
RETURN true

For now I am using transaction as arangodb documentation suggests (using javascript code), but if using AQL query is possible, I'd prefer to remove js code from my php code!

If it's possible, do you suggest this solution for commiting transactions or using js way is preferred?

barbsan
  • 3,418
  • 11
  • 21
  • 28
Amirreza Shafaat
  • 31
  • 3

2 Answers2

3

AQL in a single server environment is executed in an ACID fashion. This is automatically within a single transaction without any further need of a separate _executeTransaction. Therefore you can use the above AQL statement to update the two collections within a single AQL statement.

There are some caveat to keep in mind:

  • after the update statement for a collection you cannot use this particular collection in any further update or read statement
  • if you enable intermediate commits then the update will no longer be atomic
Frank Celler
  • 56
  • 2
  • thanks for answer, it was very helpful. i'm using RocksDB but i dont know that intermediate commit is enable or not. how can i check it? – Amirreza Shafaat Feb 26 '19 at 13:43
  • Frank, could you point me at the docs/reference for that? Wan'st aware a single server environment acted differently. Good to know. – Andrew Grothe Feb 27 '19 at 17:20
  • The documentation https://docs.arangodb.com/3.4/AQL/Operations/Insert.html and the option is described https://docs.arangodb.com/3.4/Manual/Programs/Arangod/Options.html#rocksdb-options – Frank Celler Mar 20 '19 at 10:27
2

(edited from original)

According to the docs:

Each UPDATE operation is restricted to a single collection, and the collection name must not be dynamic. Only a single UPDATE statement per collection is allowed per AQL query, and it cannot be followed by read or write operations that access the same collection, by traversal operations, or AQL functions that can read documents.

In order to trigger a transaction you need to do so explicitly.

There are no individual BEGIN, COMMIT or ROLLBACK transaction commands in ArangoDB. Instead, a transaction in ArangoDB is started by providing a description of the transaction to the db._executeTransaction JavaScript function:

db._executeTransaction(description);

For example:

db._executeTransaction({
  collections: {
    write: [ "users", "logins" ],
    read: [ "recommendations" ]
  }
});

So, to answer the question, you need to use a client library to trigger a transaction as it will not happen automatically.

That being said, the main benefit of document/graph databases is the horizontal scaling, sharding, and cluster abilities. If you absolutely need to rely on transactions you might want to rethink why you are using a document based database. Eventual Consistency is typically good enough for a lot of use cases but in other cases you absolutely need ACID. A blog probably doesn't need ACID.

Andrew Grothe
  • 2,562
  • 1
  • 32
  • 48
  • i want to point to important word on line 2 of what you mentioned: ... single UPDATE statement **per collection** is allowed per AQL query ... so in my case because updates affect two different collections , i see no problem but i can not be sure about it! – Amirreza Shafaat Feb 23 '19 at 11:56
  • @AmirrezaShafaat I see your point, I interpreted it differently. – Andrew Grothe Feb 23 '19 at 15:36
  • @AmirrezaShafaat found more information regarding transactions and updated the answer. – Andrew Grothe Feb 23 '19 at 15:49
  • 1
    I wanted to point out and emphasize what @AndrewGrothe has said about Eventual Consistency. ACID is expensive, very expensive, and if you need a lot of simultaneous transactions, you will get a lot of write-write conflicts with ArangoDB and performance will deep-dive. Do yourself a favor and use a DB with eventual consistency if you can live with it. – Oliver Hausler Jun 03 '21 at 02:22