1

I read that

"A lock occurs when multiple users need to access a database concurrently.", (ref)

or

"database locks exist to prevent two or more database users from updating the same exact piece of data at the same exact time" - (ref).

Still, I do not understand which data is locked when the database lock occurs.

First example, two users run sql to select multiple rows at the same time. User 1 goes first.

User 1: select * from products where price > 100;

User 2: select * from products where price < 100;

Would the user 1 query lock the database for user 2?

Second example, User 1 wants to update the product table. User 2 wants to select data from product table. User 1 goes first. Would user 1's action lock the database for user 2?

Thanks!

toby_yo
  • 179
  • 2
  • 15
  • 2
    This is a very broad-topic to read on. In short, there are several types of locks). Select operations here, won't acquire lock at all. But, update operation would acquire the lock, if there are several(more than 1) contenders – Am_I_Helpful Mar 24 '16 at 18:31
  • What actually gets locked depends on what database you are using, whether you are using transactions, what isolation mode you are using, and what kind of indexes are defined for the tables in question. – antlersoft Mar 24 '16 at 18:36

2 Answers2

2

To answer your questions at the end, it depends on the RDBMS architecture. For Ex. 1, the answer is almost always no. Reads don't block other reads. For Ex. 2, if the rows overlap in a block on disk, there may be contention. The intent to write typically won't block reads. It depends if your database uses optimistic or pessimistic locking. Most modern systems are optimistic and only lock briefly while the record is updated and won't prevent a subsequent read.

There are several types of database locks. These will depend on the internal RDBMS architecture, but I'll show a few MS SQL Server locks which is quite broad. They can be segregated by type and range:

Types: Shared or read Lock: Access is shared. Indicates that a process it reading data. Allows concurrent reads. Exclusive lock: Indicates that a process wants to write. Does not allow other reads or writes.

Range: Table: locks the whole table. Block: locks a physical (or logical) block on the disk Row: locks an individual row.

These are the basic types and each database will have others. Reads can be done concurrently but writes need to be done sequentially.

A table truncation may cause an exclusive table lock, for example. A row update may lock a block from other reads and writes.

SQL Server also specified a user-defined lock. These can be used to lock across tables in triggers. These are a less-than-optimal solution. It also has intent locks (that others like Oracle don't have) that indicate that you intend to update the record, but may or may not update it. These can be a source of deadlocks if you are careless with triggers and stored procedures.

This article gives more information on SQL Server: https://technet.microsoft.com/en-us/library/aa213039(v=sql.80).aspx

Refer to your specific database documentation for how it handles locks.

Henry LaFleur
  • 31
  • 3
0

In both of your examples, each user is only trying to read from the database, not update it. So neither will prevent the other. Were there to be a user trying to update the database, that's when users start getting blocked; exactly how that is done is, as others have commented, a pretty broad question.

Scott Hunter
  • 48,888
  • 12
  • 60
  • 101
  • Yea, I need to read more about this. But based on what you guys mentioned above, the rule is like "changes (update, insert, delete) result in lock, select does not". Is this correct? What is the other considerations? – toby_yo Mar 24 '16 at 18:43
  • Also, if i update one single row of data. Would the whole table be locked? Or just that row – toby_yo Mar 24 '16 at 18:45
  • "exactly how that is done is, ..., a pretty broad question." – Scott Hunter Mar 24 '16 at 18:50