I am trying to update a document when a user requests it based on an orderby condition. This will help me always sending a unique document to the user for working on the document. The pseudo query is as below.
################
Query1:
Update Bucket1
use keys (select raw (meta().id) from bucket1 where docType=‘case’ and caseType=‘new’ order by createdTrimStamp,field1,field2
limit 1 )
set caseType=‘working’ , analyst=‘user1’
returning meta().id as docId
################
At time T when I try hitting with multiple requests, how will couchbase handle the concurrency as at time T all selects will/should return the valid same document as at time T there are no updates to the document (yet).
Note: The scan consistency is kept at REQUEST_PLUS
Also, will the below query help with solving the concurrency?
################
Query2:
Update Bucket1
set caseType=‘working’ , analyst=‘user1’
where meta().id = (select raw (meta().id) from bucket1 where docType=‘case’ and caseType=‘new’ order by createdTrimStamp,field1,field2
limit 1 )
returning meta().id as docId
################
If yes, how is it different from the first Query at time T for multiple requests. If not, how do we handle such scenarios?
Note: Couchbase version 6.5 (EE)
Query 2 : SUBQUERY returns array so equality will not match,
where meta().id = (select raw (meta().id) from bucket1 where docType=‘case’ and caseType=‘new’ order by createdTrimStamp,field1,field2 limit 1 )[0]
Another problem is WHERE cluase need to fetch all the documents from the bucket because parent query must evaluate first.
UPDATE is based on CAS. So if issue multiple updates in parallel one succeed other will fail. Then you should retry.
Thank you vsr1 for you quick response,
So will Query1 work for concurrent users providing only 1 success and the rest fail for me to retry?
I am trying to test Query1 with an invocation of multiple requests from postman with all calls(requests) waiting till time T. On time T, I allow all the waiting requests to pass to the method (to simulate concurrency) calling this n1ql query to get me results. The results are not exactly correct, sometimes, I do get multiple docIds for the multiple request; some records do fail for retry. These outcomes are not allowing me to understand how should I go about resolving this concurrency issue.
I do find that the CAS has changed, but the condition should have been false when the updates to the same document were tried. for example the document will be ‘working’ with first update. so the second update should not happen.
Please find the 8 concurrent records, of which there are duplicates in 5 and 3 failed with CAS mismatch (not printing the error ones here)
Case ID: GSI::6718f6d3-f21f-4aff-b91c-8b4544056b84::CAS value: 1611069956917297152
Case ID: GSI::6718f6d3-f21f-4aff-b91c-8b4544056b84::CAS value: 1611159961383469056
Case ID: GSI::a74c9c32-d4ed-4bf3-884f-68f319677246::CAS value: 1611069967313993728
Case ID: GSI::a74c9c32-d4ed-4bf3-884f-68f319677246::CAS value: 1611159961423314944
Case ID: GSI::a74c9c32-d4ed-4bf3-884f-68f319677246::CAS value: 1611159961404964864
MERGE default AS m USING (SELECT META(b).id FROM default AS b WHERE docType = "case" AND caseType="new" ORDER BY createdTrimStamp, field1, field2 LIMIT 1) AS s
ON KEY s.id
WHEN MATCHED THEN UPDATE SET m.caseType="working", m.analyst="user1" WHERE m.caseType != "working" AND m.analyst != "user1" RETURNING META(m).id
;
If you get 0 results or CAS miss match error, the current session did not do UPDATE and retry