Concurrent modification on page

[ruslan-butyk-fntext]

Hi guys!
I need some advice/tip/help how to "conquer" concurrent transactions in ArcadeDB.

The context:
I have an instance of OracleDB where data is primarily stored and an instance of ArcadeDB (remote, standalone server).
Communication to ArcadeDB is done via RemoteDatabase (REST calls).
When some changes are done to OracleDB all the required changes are also reflected (synced) to ArcadeDB.
ArcadeDB is only used for routing and stores all the required data.
It's really important to keep data between OracleDB and ArcadeDB in sync and to do that I synchronize transactions between OracleDB and ArcadeDB.
Meaning that when Oracle transaction is created, I open an ArcadeDB transaction, if needed (not all data are synced to ArcadeDB), and commit/rollback them simultaneously.
Oracle has many parallel transactions that are making changes.

The problem:
I'm facing an exception like this a lot:
Concurrent modification on page PageId(dbname/40696/156) in file 'device_0.40696.65536.v0.bucket' (current v.66 <> database v.67). Please retry the operation (threadId=128)

I've tried to increase the delay and retry number but then I'm facing a "deadlocks" on my side (OracleDB) because Oracle transaction is waiting when ArcadeDB transaction succeed and there a lot of them and Oracle transaction can't wait for long.

Also, I saw in the documentation that ConcurrentModificationException should be thrown when a record is modified, not a page as it says in the exception message above:

Would be glad for any tips or tricks from your side how to deal with this if possible.
Thanks in advance!

[robfrank]

The new default configuration of ArcadeDB is to use a single bucket per type. In you case, my suggestion is to increase the number of buckets to, at least, the number of clients/threads.

some hints on buckets: https://docs.arcadedb.com/#_troubleshooting

to change the number of bucket for all the types, just set the configuration property arcadedb.typeDefaultBuckets.

So, if you are running the server, just pass -Darcadedb.typeDefaultBuckets=<put your number>.
Or, you can create tpes with different number of buckets: https://docs.arcadedb.com/#sql-create-type

I hope this could help

[ruslan-butyk-fntext]

@robfrank Thanks for the hint!
But unfortunately, it doesn't help in my case because I have a complex graph structure and there is a high chance of conflict when some vertex or edge is being updated (or recreated in order to change the graph structure if needed). And that's why separation to buckets doesn't help. Is it possible to have a lock on single vertex/edge if it's being touched by a transaction instead of a page (bucket)? I mean to make locks as small/atomic as possible.

[ruslan-butyk-fntext]

Let me describe you a simple example of graph structure that I have, just a small part:
Vertex type: zone, device.
Edge type: zone_device, device_device.

Use case:
For example, I have a zone zone1 and in this zone I have some devices: device1, device2.. etc.
And all those devices have relations (edges) to the zone1 and relations between devices.
And there might be a couple of users that are modifying device location (moving from one zone to another).
One user might move device1 to a new zone and other user might move device2 in a different zone.
And I have to sync these changes to ArcadeDB graph.
And if I got it correct, ArcadeDB locks bucket (or page) where zone1 is located and locks devices bucket (page) and buckets with edges.
But those two users are modifying different devices (and different vertexes/edges) but because of that "general" lock transactions fail.
And from my perspective locks on vertexes/edges might be useful here.

[ruslan-butyk-fntext]

Would it be possible to consider changing/introducing the approach for "locks" from checking page version to checking record
(vertext, edge) version on commit for remote ArcadeDB server? It would minimize transactions overlapping in my case.

[lvca]

ArcadeDB has page locks (for efficiency) and usually are not an issue unless you have massive concurrency on the same page. This is managed automatically by ArcadeDB API (most of the times you don't even see happening) because of its auto-retry mechanism. This is an optimistic locking approach.

@ruslan-butyk-fntext is this issue a performance issue or do you need help to code the automatic retry? What API are you using?

[ruslan-butyk-fntext]

@lvca I'm using RemoteDatabase API to communicate to ArcadeDB remote server. I'm already using retry but because affected data is on the same page retry takes too many turns to succeed which I can't afford. Because ArcadeDB transaction blocks Oracle transaction and Oracle transaction waits for ArcadeDB transaction to succeed. But since sometimes it waits for too long, I'm having deadlocks on Oracle side. And I'm looking for some other solution (not retry, or retry but with record locks) to not prolong ArcadeDB transaction and minimize data modification overlapping.

[achim-haeberle-fnt]

@lvca We were able to resolve most of the deadlocks, but we still run into commit exceptions as the amount of retries is sometimes not sufficient due to concurrent calls. We also noticed a decrease in performance as the retries take up quite some time (e.g. 16 retries with a delay of up to 50ms per try -> ~800ms per action imagine a REST-API call of an interface which is delayed like that.)
Do you have a suggestion how to counter that issue? Is it possible to implement a "fix" for that on ArcadeDB side?

[lvca]

@achim-haeberle-fnt is moving of a device location a non common operation? If this is true, can you serialize this changes to be executed by one thread only? If you're using ArcadeDB as embedded you should have full control of the changes to be serialized by 1 thread only, right?

We could support a pessimistic lock by opening up the internal API TransactionContext.lockFilesInOrder() to be executed at begin time instead of phase1 commit time. This would guarantee the transaction runs with locks and no concurrent change can happen. The issue here is to explicit which files to lock. These files are now collected automatically at phase1 commit by looking at all the pages changed in the transaction. In this case the user should declare which buckets/types to lock preemptively. Also, this method should get the timeout as parameters (to avoid deadlocks) instead of using the global settings.

[lvca]

With pessimistic locking, RDBMS lock resources along the way. For example if in a transaction you delete a record, an implicit lock is acquired on the table the record was deleted (or sub portion of a table).

The lock has a timeout and there is always a deadlock detection algorithm in place to avoid a stall. Classic scenario with 2 threads:

• thread 1 locks resource A
• thread 2 locks resource B
• then thread 1 tries to locks resource B, waits...
• then thread 2 tries to locks resource A, deadlock

When thread 2 tries to lock resource A, before waiting it should check it's already locking B and the owner of the lock on A is waiting for B, so if it tries to acquire the lock for A will result in a deadlock and it should throw an exception instead.

This is more complicated to implement along with our optimistic locking, but we could provide an API to explicit lock resources. Example:

db.transaction( ()-> {
  db.acquireLock().withTypes("Customer", "Invoice").withBuckets("Reports_2025").lock();
  ...
}); // ANY LOCKS ARE AUTOMATICALLY RELEASED AT COMMIT/ROLLBACK TIME

The acquireLock() will try to lock the buckets associated to the resources (types/buckets), indexes included. At commit time, when the list of resources to lock is computed it must be compared. If all the resources have been already locked, then execute commit phase 2, otherwise exception.

This allows to completely avoid the deadlock detection because ArcadeDB already locks all the resources in an atomic step (ordering by resource id, so there cannot be a deadlock).

WDYT @achim-haeberle-fnt ?