Read with txParticipation=readOnly and write in same Application
Closed this issue · 13 comments
Hi,
Sorry for posting my question in the issue section, but i had an issue that i am stuck at for some time now so wanted to check if anybody could kindly provide any help here.
We are working on creating a purge application that runs throughout the day. We fetch objects using the listCursor and for each x rows, check if they meet certain criteria and delete. We stop purge and also stop fetching data using the cursor(but do not close the cursor ,effectively pausing the cursor )if the database is busy or if other processes are running.
I was looking to find a way such that the read from the listCursor would be in readOnly mode so it does not hold on to any locks(this is currently blocking other processes even thought the cursor is paused and not fetching any data) and the delete's can happen in the same JVM.
Since we declare txParticpation at the connection level, it would not let me do it currently.
Could someone please suggest if there is a way i can specify txParticpation at query level or if could have 2 connection managers for an application so i could use the listCursor in readOnly mode and run the delete's using the second connection manager or if there is any other way to tackle this problem.
Please let me know if you need any other details.
Thanks
To get multiple connections/tx behavior, the canonical way is to use multiple threads (Reladomo inherits that from the Java Transaction API). So you can structure this like so:
Create a SingleQueueExecutor.
In your thread, do a forEachWithCursor (outside of a transaction). Take the objects from the list that you want to delete and call sqe.addForTermination.
There are other ways of doing this, which might be better. If you can express your "certain criteria" as a Finder operation, using FooFinder.findMany(op).deleteAllInBatches(batchSize) is potentially more efficient. Even for your cursor, putting as much of the criteria in the operation is the right way to go.
Thanks for your help Moh! Just a follow-up question. Would i have to instantiate my ConnectionManager for delete in the addForTermination method and use that to delete in a transaction?
Also, we already have the delete's running in a separate thread outside of a tran. I am not sure about how i can have a separate connection manager and use that in the delete thread.
We are avoiding using FooFinder.findMany(op).deleteAllInBatches(batchSize) because we are controlling when we actually delete and rather than doing it as one operation. We are puting a criteria in the operation for the cursor.
To get multiple connections/tx behavior, the canonical way is to use multiple threads (Reladomo inherits that from the Java Transaction API). So you can structure this like so:
Create a SingleQueueExecutor.
In your thread, do a forEachWithCursor (outside of a transaction). Take the objects from the list that you want to delete and callsqe.addForTermination.There are other ways of doing this, which might be better. If you can express your "certain criteria" as a Finder operation, using
FooFinder.findMany(op).deleteAllInBatches(batchSize)is potentially more efficient. Even for your cursor, putting as much of the criteria in the operation is the right way to go.
Hi,
Apologies for reaching out again, but i have still been stuck at this issue, i have still been unable to find a way such that i could read without holding any locks and use the same objects retrieved to delete(in the same jvm). Could you please suggest a solution.
Did you try the solution outlined above with the SQE?
I think you may misunderstand some of the fundamentals here, regarding connections. Reladomo is not a connection based framework. It's an ORM. Connection managers are configured and all other semantics, including locking and transaction participation are conveyed in terms of the object api, not connection management.
Did you try the solution outlined above with the SQE?
I think you may misunderstand some of the fundamentals here, regarding connections. Reladomo is not a connection based framework. It's an ORM. Connection managers are configured and all other semantics, including locking and transaction participation are conveyed in terms of the object api, not connection management.
Hi Moh,
We do use the list cursor outside of a tran and use objectList.purgeAll to delete in a different thread. Inspite of that, the cursor is holding shared read lock and blocks some updates and then subsequent reads on the table. Is there a way i could enforce that the listcursor reads with isolation level 0 so it would not hold on to shared locks as well.
Put the list in a transaction and use optimistic locking. Something like this:
MithraManagerProvider.getMithraManager().executeTransactionalCommand(tx -> {
FooFinder.setTransactionModeReadCacheWithOptimisticLocking(tx);
FooList list = FooFinder.findMany(op);
list.forEachWithCursor()...
});
Put the list in a transaction and use optimistic locking. Something like this:
MithraManagerProvider.getMithraManager().executeTransactionalCommand(tx -> { FooFinder.setTransactionModeReadCacheWithOptimisticLocking(tx); FooList list = FooFinder.findMany(op); list.forEachWithCursor()... });
Thanks for your help Moh, We are using the generic related finder and hence cannot use the method setTransactionModeReadCacheWithOptimisticLocking.
However, i am trying
tx.setTxParticipationMode(MithraObjectportal,
ReadCacheWithOptimisticLockingTxParticipationMode.getInstance());
Will keep you posted on how it goes! Thanks again for your help,really appreciate it.
Put the list in a transaction and use optimistic locking. Something like this:
MithraManagerProvider.getMithraManager().executeTransactionalCommand(tx -> { FooFinder.setTransactionModeReadCacheWithOptimisticLocking(tx); FooList list = FooFinder.findMany(op); list.forEachWithCursor()... });Thanks for your help Moh, We are using the generic related finder and hence cannot use the method setTransactionModeReadCacheWithOptimisticLocking.
However, i am trying
tx.setTxParticipationMode(MithraObjectportal,
ReadCacheWithOptimisticLockingTxParticipationMode.getInstance());
Will keep you posted on how it goes! Thanks again for your help,really appreciate it.
Hi Moh,
I tried this approach, it does append "noholdlock" to the queries, but it still blocks updates on the table.
Is there a way we could add something like "at isolation level readuncommitted"?i understant that it would be a dirty read,but it works for our use case.
Thanks
That doesn't make any sense. Your table or global configuration is likely badly setup. Make sure the table is using datarows locking and your server is configured properly. Read these two links:
That doesn't make any sense. Your table or global configuration is likely badly setup. Make sure the table is using datarows locking and your server is configured properly. Read these two links:
Hi Moh,
i am certain we have configured row level locking on all tables. But i agree with your point, the issue is only with one of the tables,it is to be working fine for the other tables, it could potentially be an issue with the table configuration. I will investigate this further and keep you posted.
Thanks for your help,really appreciate it.
That doesn't make any sense. Your table or global configuration is likely badly setup. Make sure the table is using datarows locking and your server is configured properly. Read these two links:
Hi Moh,
i am certain we have configured row level locking on all tables. But i agree with your point, the issue is only with one of the tables,it is to be working fine for the other tables, it could potentially be an issue with the table configuration. I will investigate this further and keep you posted.
Thanks for your help,really appreciate it.
Hi Moh,
I did look into this, the tables are configured for row level locks.
We tried a couple of different things, and one of the things that seems to be helpful is adding "at isolation level read uncommited" to the query .Just wanted to check with you if there is a way to strategically read at isolation level 0?
Thanks
Isolation level 0 is bug, not a feature. It effectively returns random data. Reladomo won't support that. In a purge scenario, it can be particularly dangerous. If you can't fix the problem by changing the table configuration you'll have to figure out a different way of organizing your work. Here are some suggestions:
- Add an index to the table to make the query fast
- Use a TOP query instead of streaming the whole table
- Use a more selective query
Isolation level 0 is bug, not a feature. It effectively returns random data. Reladomo won't support that. In a purge scenario, it can be particularly dangerous. If you can't fix the problem by changing the table configuration you'll have to figure out a different way of organizing your work. Here are some suggestions:
- Add an index to the table to make the query fast
- Use a TOP query instead of streaming the whole table
- Use a more selective query
Thanks Moh,
I will try and work with these options and keep you posted. One side note, for the rows that we fetch using our select, we do not delete all of them, we still check each object to see if it can be purged and only then do we delete it.