Best way to handle conflicts without row-locking?

General
#1

Hi,

What is the best way to implement version safe updates in cockroach?

Say I am building a collaborative editor and want the central server to enforce a global ordering of edits. With pgsql I could do this by storing a revision id and ensure new updates are based on the latest revision before applying the changes. I could do this with “for update” row lock which cockroach does not have e.g.

tables
    Document [id : uuid, revision : integer, name : text]
    ...lots of associated data tables

update(expected_revision, new_data)
{
    Begin transaction

    // lock the document row and get the current revision number
    current_revision = “select revision from document where id = ... for update”

    if current_revision == expected_revision
        // Good to go
        update data and increment revision
        Commit()
    else
        // Conflict 
        Rollback()
        Get recent changes for client to merge and retry
}

What would be the best approach in cockroach to only allow updates if the expected revision matches? I believe I would need to get conflicting transactions to fail and then roll them back [1] but I’m not sure what the most efficient way to do this when a data update might be quite large and performance matters.

[1] https://www.cockroachlabs.com/docs/stable/transactions.html

#2

CockroachDB uses the SERIALIZABLE transaction isolation level by default, so SELECT FOR UPDATE is generally unnecessary. The FOR UPDATE clause is a way to get stronger locking for particular rows in transactions using the weaker isolation levels. In a serializable transaction you can simply remove it and get correct results without having to tell the database how to lock things.

We’re considering implementing the FOR UPDATE syntax and what effect (if any) it should have in #6583

#3

Thanks for the response!

So that means the CDB implementation would look the same as above but:

i) remove the “for update” clause
ii) catch the error on the commit, if its a 40001 retryable error, then assume it is serialisation conflict

Is that correct?

#4

Yes, that is correct.