Does a lock mean a wait?

Question

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:

• https://learn.microsoft.com/en-us/sql/t-sql/statements/set-lock-timeout-transact-sql SET LOCK_TIMEOUT timeout_period
• https://www.postgresql.org/docs/13/runtime-config-client.html set lock_timeout ms & set statement_timeout ms

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

Answer 1

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.