I need to update one row per connection in ibm db2 on zos without data anomalies.
I choose FOR UPDATE WITH RS USE AND KEEP statement, but there is a problem. If one client is holding lock during data update and simultaneously second user tries to read the same row - second user will wait until lock being released.
I need in next behavior: second user gets error if row under the lock.
Is it possible in DB2 11?
What you are asking for is a conditional SELECT if you could get the UPDATE lock if I'm reading your request correctly. I do not believe there is a way to do that. However, an alternative you could do something like:
SELECT myCount FROM myTable WHERE ID = someValue
You could then update the value and execute the UPDATE with a WHERE clause that looks for the ID and the original value of myCount. A sequence like
SELECT myCount FROM myTable WHERE ID = someValue
origCount = myCount++
UPDATE myTable WHERE ID = someValue AND myCount = origValue
If your UPDATE shows zero rows updated you have an indication that someone else updated it and you then repeat the sequence or move on to your alternate logic.
I believe that would achieve what you are looking for without having to detect the presence of a lock since you indicated your accessing a single row at a time you could use CS isolation.
This is generally referred to as Optimistic Concurrency
Optimistic concurrency control (OCC) is a concurrency control method applied to transactional systems such as relational database management systems and software transactional memory. OCC assumes that multiple transactions can frequently complete without interfering with each other. While running, transactions use data resources without acquiring locks on those resources. Before committing, each transaction verifies that no other transaction has modified the data it has read. If the check reveals conflicting modifications, the committing transaction rolls back and can be restarted.[1] Optimistic concurrency control was first proposed by H.T. Kung and John T. Robinson