I am currently evaluating the Oracle Mobile Server 11g with Berkeley DB for my company but I don't get out how the transaction control works.
For testing I am doing the following:
1. Start transaction on the client with dbsql
2. Insert some rows, one with PK value "42"
3. commit transaction
4. Insert a row with PK value "42" on the server side and commit
5. Start sync after mgp has run
After that I only see the row with value "42" from the client in the error queue. I had expected that all rows which were inserted by the client would be in the error queue.
Is there any possibility to configure the mobile server to behave like this or is the sync process simply not ACID-compatible?
I'm sorry, but this document only says that Berkley DB is full ACID compatible.
But I was talking about ths synchronisation process. In my litte example I started a transaction in bdb and commited. What I want is that if the synchronisation of one row affected by a statement during the transaction, the synchronisation of all rows changed within this specific transaction will fail.
What you are seeing is 'expected behaviour' with the MGP process.
Note that while the record is sent to the 'Error Queue' in effect it is not considered an Error requiring the whole 'Transaction' to be rolled back. Merely a 'conflict' with an existing record. In the Mobile Server you are able to edit the record and resubmit or delete it.
Note that when you Sync the Client the 'Transaction' that existed on the Client has been committed and combined with all other Transactions performed against the Client DB since the previous Sync so it would be very difficult to 'Rollback' just the Transaction.
In previous versions of Lite the MGP would put all transactions for the Publication Item in the Error Queue for the related Sync session but Customers did not like having to process potentially hundreds of records because one failed. Now individual records are put in the error queue and the remainder of the records are processed by the MGP.
Ideally you should create the Record on the Client and then edit it on the Server when it has been synced or vice versa, creating records at same time on Client and Server will cause conflicts with the Mobile Server. Perhaps you could consider using a different PK that will be unique and have a procedure/job on the Repository DB that checks for your Duplicates and acts accordingly i.e. combine/delete records etc