0

UPDATE and INSERT queries lock relevant row/table while query executes. But does SELECT also locks the tables? What other types cause DB locks? Does this vary by DB?

Edit: Added sql-server tag. When is nolock useful?

tvr
  • 4,455
  • 9
  • 24
  • 29
  • 2
    Which DBMS are you talking about? No modern DBMS locks the complete table when doing updates or inserts (only modified rows). And selects usually don't lock anything at all. –  Apr 20 '16 at 07:47
  • Also, not all dbms have locking, some have optimistic concurrency control instead. (https://en.wikipedia.org/wiki/Optimistic_concurrency_control) – jarlh Apr 20 '16 at 07:51
  • Sorry I meant row-level locking to some extent – tvr Apr 20 '16 at 08:02
  • By using nolock, deadlocks will not occur against other queries running against the same data and less memory is utilized due to the lack of row, page, or range level locking... The nolock is not recommended when you are using explicit transactions like 'Begin tran - end tran ' – Pirvu Georgian Apr 20 '16 at 08:05

1 Answers1

2

Basic RTFM?

This IS a complex topic, so please read the extensive documentation.

SELECT does lead to locks IF the connection and transaction settings demand that. This is to allow what is called a "repeatable read" in a transaction, which means a read row can not be changed until the reading transaction commits or rolls back.

EVERYTHING - literally - CAN cause locks. The problem is that most of this is defined in the transaction (or by default connection) settings. This really is a section you must read in the documentation because it is not a "one page" - it goes over a lot of details. It can also be changed in a SELECT statement (allowing to IGNORE locks, when I say read uncommitted for example).

TomTom
  • 61,059
  • 10
  • 88
  • 148