Get and delete in a single operation

[flaviu]

@vsr1 I need your recommendation.

I need to do a get and then delete multiple documents in the same query.

something like

select * from bucket where meta().id in [document1, document2, documentX]; delete from bucket where meta().id in [document1, document2, documentX]

as these documents may be updated from the moment of getting the data to the moment of deleting it by other workers, what do you think is the best approach to do this so that I make sure that the data I am going to receive would not be updated before deleting it?

later edit: came up with this idea:

delete from bucket where meta().id in [ "document1", "document2"] returning *

do you think there is a better way?

[vsr1]

N1QL DELETE or KV REMOVE() will not accept CAS.

delete from bucket USE KEYS [ "document1", "document2"] returning *;
It reads the document first and then deletes (there might be window it can be modified and you may not have upto date info

Only option you can have is transaction @graham.pople

[flaviu]

in my solution above, with the returning * , wouldn’t this means that I will get exactly what was deleted?

[vsr1]

It reads the document first and then deletes (there might be window it can be modified and you may not have upto date info
EXPLAIN shows (these two operations very small window )

 {
                                "#operator": "Fetch",
                                "keyspace": "default",
                                "namespace": "default",
                                "optimizer_estimates": {
                                    "cardinality": 1,
                                    "cost": 12.115,
                                    "fr_cost": 12.115,
                                    "size": 1
                                }
                            },
                            {
                                "#operator": "SendDelete",
                                "alias": "default",
                                "keyspace": "default",
                                "namespace": "default"
                            }

There might be option via SDKs via getAndLock and remove

[flaviu]

ok, I understand,

so, this would work?

START TRANSACTION;
delete from bucket USE KEYS [ "document1", "document2"] returning *;
COMMIT ;

[vsr1]

If transaction fails then some one modified

Also see if SDK getAndLock and followed by remove allows your session not others.

[flaviu]

it would be interesting to see how can I test a fail transaction :)))

[graham.pople]

Hi @flaviu

As @vsr1 mentions, this seems a great candidate for our ACID transactions. You can see how to use it here for the Java SDK Using Couchbase Transactions | Couchbase Docs, and we have similar documentation available for all the SDKs of course.

I would use KV gets and remove for this (ctx.get() and ctx.remove()) inside the transaction, since you know the keys. This will give you automatic protection - if any of the documents change between the get and the remove, this is automatically detected and the transaction will be retried (your lambda gets rolled back and rerun).

SQL++ even inside a transaction wouldn’t give you this same automatic protection that you get with KV operations. The documents can still change between the SELECT and the DELETE. There is a clever trick you can use to solve that though - you turn the initial SELECT into an UPDATE RETURNING instead. But if you know the keys, it’s going to be easiest to use KV operations.

[flaviu]

thanks for the additional details. would be good if you have some examples for PHP

[graham.pople]

Sure, please see Using Couchbase Transactions | Couchbase Docs