Hi,
Is transaction support for N1QL on the timeline in the near future?
Otherwise, is there a way to get a list of all the documents affected by a N1QL query (by document ID), from which a simple locking system could be built?
Thanks.
Thereās nothing to announce from a timeline perspective, but we are regularly looking at these kinds of things. In fact a group of us, including @keshav_m just chatted about the possibilities here recently!
With respect to your question, you could certainly use the RETURNING clause along with any items your statement is mutating. Depending on what youāre doing, this could certainly give you what you need to implement application logic around aspects of transactions.
Itād be great if you could outline any details on what youāre looking to do. Feel free to message us directly with the details too.
2 Likes
Thanks for your reply.
I had a look at the returning clause, but I donāt get the impression itās what I need. Let me explain :
Iām trying to create a generic library for creating different types of transactions. There are multiple types that I am implementing, but the most important is for locking transactions.
Iād like to do something like this :
- Get a list of the IDs of the documents that a particular N1QL query affects
- Lock all the files that are affected (using some customized locking logic)
- Check that there are no pending rollbacks on any of those files (because of broken transactions)
- Run the query
- Clear the rollbacks
- Unlock the files
Everything apart from 2 is fine, itās just getting the lists of documents that would be used/changed (but not actually changing any of them) that Iām not sure how to do.
Obviously if you know all the document IDs in advance, thatās fine, but Iād like to make it generic, where in general you donāt know the IDs.
What Iād like to do is to send the N1QL query to Couchbase, not run the query itself (in the sense of altering the documents), but just return the list of documents affected.
Iām hoping that CB already provides this ability, but if not, then I see it as a potentially a good way for you to allow admins to write their own locking mechanisms for certain types of queries, even if you donāt yet provide the locking yourself. Putting it as an option into the EXPLAIN command could be a possibility.
I understand that there are many queries that would result in a different number of affected documents, or that would need to iterate over values from lots of documents. Itās really only for queries that have set files that will change each time
Implementing multiple document transactions internally would be an awesome feature addition to CB, so +++ for that. 
Thanks.
Hi,
I am a product manager at Couchbase and am curious if your āqueryā is actually an update statement? Can you just take the select part of it and run to get the list of document IDs affected by the statement?
Thanks.
I was hoping to be able to write a generic function to handle all types of queries - which could include updates or inserts as well as selects.
Upon further thought, though, I realised that itās not really possible to do that. Also, I found various ways to create locks that will work in practice for the different actions I need to perform. It does feel sort of hacky, but will work in this particular case.
There is a project that Iām planning on developing in the next couple of years, though, where Iām sure that having ACID support for transactions will realistically be needed. Do you think that itās likely that weāll see support for this in CB by the end of 2019?
Thanks.
Hi @Eugaia,
In your case, youād like to get a potential list of documents, you can create your APIs accordingly.
Consider this:
UPDATE bucket SET sal = sal * 1.1 WHERE department = 'XYZ' and rating = 1;
Youāll have to create the API to accept the WHERE clause (and bucket namet/etc, depending on your requirements) and execute this:
SELECT META().id FROM bucket WHERE department = 'XYZ' and rating = 1;
Of course, the list of documents returned is ONLY a POTENTIAL list of documents since the documents can change between this query and the actual UPDATE operation. Even for this statement, youād have to choose the consistency level depending on your requirement.
DITTO with DELETE.
With INSERT, youāre generating the document IDs for your SIMPLE INSERT statements as well as INSERT INTOā¦ SELECT FROM statements. So, youād know before you INSERT.
Clint Ricker from Cisco has built infrastructure for all-or-nothing execution for his application: https://www.youtube.com/watch?v=GdVnf3Iirno
1 Like
Hi @keshav_m,
Sorry for not replying sooner. I somehow didnāt see your message.
Thanks for your reply. Iām not yet sure what kind of locking Iāll do, but itās handy to know that you can just select the META().id.
Best regards!
The need for ālockingā is also very dependant on your document model. You might need to look at embedded data (at the cost of data duplication) or using document references to remove the need for it completely.
As documents could be on different data nodes, taking a lock and releasing it could be quite a slow operation with many docs involved.
1 Like
Yes. Youāve got my mental juices flowing, now!
Iām pretty sure that I should be able to work out a way to implement all the kinds of transactions Iād want to implement without using locking, with a bit of creativity.
FYI: N1QL transaction is close byā¦ Keep an eye out for the Couchbase 7.0 Beta announcement in the next few weeks.
Early details: https://blog.couchbase.com/transactions-n1ql-couchbase-distributed-nosql/
Slides: https://www.slideshare.net/journalofinformix/n1ql-new-features-in-couchbase-70
1 Like
Thanks for letting me know.
@keshav_m This is slightly off-topic for this thread, but I thought Iād mention it since it follows on from the links you provided. Looking at the slides here:
On slide no. 16, should it read:
ā¦ LANGUAGE JAVASCARIPT as āadd3ā at 'math;
?
Currently it reads:
ā¦ LANGUAGE JAVASCARIPT as āaddā at 'math;
You are right, it should read add3.
1 Like