0

I really don't understand promises about waits in case of concurrent data access.

All manuals operate by term lock. No one explains that lock probably causes SQL clients waiting for lock acquisition on the server side (and it can take seconds / minutes / infinity)?

Are there cases when locks don't cause waits? I can imagine:

  • probably READ UNCOMMITTED in some situations, like there are no DDL?
  • error is reported without waiting...
  • no one waits but first who does commit wins, other fail on their commit

I reviewed JDBC API: it mentions literally:

SQLTimeoutException - when the driver has determined that the timeout value that was specified by the setQueryTimeout method has been exceeded and has at least attempted to cancel the currently running Statement`

According to the docs timeout is detected on the client side. Widely used client API doesn't have influence on server side timeouts (including caused by locks). Probably you can set some proprietary connection properties to influence DB behavior.

I see some proprietary SQL extensions, like:

For me DB waits due to locks is such a grey area. Does a lock mean a wait?

1

1 Answer 1

2

In most cases, locks don't cause waits.

Imagine I have a simple banking system. In order to transfer money from account A to account B, I start a transaction and acquire a row-level lock on both account records. That lets me run my validations (i.e. A has enough money for the transfer, the fraud department doesn't have a flag on either account, etc.), update both accounts to reflect the new balance, generate whatever logging is needed, and commit without worrying that some other session has made a change to one or both accounts that should have prevented the transfer.

Unless some other session comes along in the fraction of a second that I'm processing the transfer which tries to lock either A or B, the locks I acquired don't generate any waits. Sure, it's possible that another session will be processing a transaction for A at the same instant that my session is, hence the need for the lock. But given the thousands of accounts and millions of transaction that need to be processed, it is very unlikely that there would be contention for the lock. Particularly if I design the system sensibly so that, for example, all batched transactions for a particular account are processed by one thread. If there is no contention, there are no waits.

5
  • Tnx, I could add an example that shared (read) lock doesn't block other shared locks. I'm more interested in other aspect of lock: is it possible to avoid wait if exclusive lock is in place (if we tolerate reading outdated/inconsistent data or want to fail fast on contention instead of waiting). Sorry if my usage of terminology wrong, hope the idea what causes difficulty is clear...
    – gavenkoa
    Apr 1, 2021 at 19:18
  • 1
    @gavenkoa - If there is no contention then an exclusive lock in my example doesn't cause a wait. If there is contention, most database engines provide syntax to allow the blocked process to time out waiting to acquire an exclusive lock (though the syntax will vary and it may depend on whether you're explicitly acquiring a lock via (Oracle syntax) select for update or whether you're doing an update and letting the database figure out what locks need to be acquired). Some databases (like SQL Server) provide a read uncommitted isolation level if you are OK with inconsistent data. Apr 1, 2021 at 19:27
  • I make conclusion: unless a client avoided contention it waits for lock. There is no notion of fail fast / no-wait => return immediately option.
    – gavenkoa
    Apr 2, 2021 at 16:42
  • 1
    @gavenkoa - That conclusion is incorrect. As I said, the syntax varies by databases and whether you're acquiring the lock explicitly or as a side effect of something else. In Oracle, for example, you can do a select ... for update wait x to specify a timeout or nowait if you want to fail immediately. Other databases have similar functionality. Apr 2, 2021 at 16:58
  • Thx for clarification. I was unaware of nowait like keywords. Postgre documentation also mentions nowait.
    – gavenkoa
    Apr 3, 2021 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.