Any timeline for N1QL transactions

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 :

  1. Get a list of the IDs of the documents that a particular N1QL query affects
  2. Lock all the files that are affected (using some customized locking logic)
  3. Check that there are no pending rollbacks on any of those files (because of broken transactions)
  4. Run the query
  5. Clear the rollbacks
  6. 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. :wink:

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.

@shivani_g

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.

@ingenthr

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.

https://developer.couchbase.com/documentation/server/5.1/data-modeling/entity-relationship-doc-design.html

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

@clinton1ql

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.