How to Manually release Row level DB lock?

sql

(vidhyashankar) #1

Following codeblock puts a DB lock on specific row.

if err := tx.Model(&a).Exec("Update accounts set Balance=250 where id=1").Error; err != nil {
    fmt.Println("rolling back")
    //tx.Rollback()
}
// Print out the balances.
var accounts []Account
db.Find(&accounts)
tx.commit()

It happens when a query is made to the record using a different DB connection in the middle of an open transaction. The line ‘db.Find(&accounts)’ keeps on waiting since the row gets locked in the first statement. This causes the go routine to get to zombie state. Killing the calling process still not releasing the lock. The lock get persisted.

I tried looking for lock listed in system tables. But it is not available. Truncating the table or deleting the record is not working. Dropping the table helps. (whcih is not possible in production)
How this lock can be released manually?


(Raphael 'kena' Poss) #2

This is not a lock. There is no locking in CockroachDB.
What you are observing is the effect of transaction contention: when two transactions touch the same rows, CockroachDB prefers to “push” one transaction in time until the other commits or aborts.

  1. You can adjust this using transaction priorities: if the 2nd transaction in your example has a higher priority, it will not suspend and instead encounter an error (a “retry error” to be exact, which is an invitation to try the query again later).

  2. If you want to read an “old” value on the table without conflict you can use AS OF SYSTEM TIME with a timestamp in the past.

No matter what you do you cannot violate txn atomicity (the A in ACID): you cannot read the modified value until the first txn commits.


(vidhyashankar) #3

Thanks @knz. From your comment I am able to understand that it is transaction contention. The contention is persistent even after the process gets killed. This makes the table not accessible. (queries to others rows are fine, whereas queries to this specific row keeps on waiting). At this situation how can I regain access to the table?


(Raphael 'kena' Poss) #4

Your initial example above did not say anything about processes getting killed.
Please clarify your situation:

  • if the client process is killed while holding a transaction open, and the network connection is closed as a result, CockroachDB will notice, abort the transaction and clear the write intents. The contention will not persist.

  • if the client thread merely stops and does not release the transaction and the transaction becomes abandoned with an open network connection, CockroachDB will not notice this. The transaction remains open.

    It is possible (but not guaranteed) that some other delay inside CockroachDB will cause the txn to time out and be aborted server-side. But you should not rely on this, and instead you must work to ensure the client aborts the transaction or closes the client connection.

This is true of other SQL engines too and is not particular to CockroachDB. You must work on your client code to ensure that the server resources are released on a timely manner.

If you have verified that the txn has been aborted explicitly (using ABORT) and/or the network connection has been closed and still you find evidence that the records are blocked from access, please explain to us how to reproduce this situation and we will investigate further.

Thank you