Why is table-level locking better than row-level locking for large tables?
Solution 1
from the (pre-edit) link
Slower than page-level or table-level locks when used on a large part of the table because you must acquire many more locks
use a row level lock if you are only hitting a row or two. If your code hits many or unknown rows, stick with table lock.
Solution 2
Row locking needs more memory than table or page level locking.
Have to acquire many more locks with row locking, which expends more resources
From http://www.devshed.com/c/a/MySQL/MySQL-Optimization-part-2/
-
Advantages of row-level locking:
- Fewer lock conflicts when accessing different rows in many threads.
- Fewer changes for rollbacks.
- Makes it possible to lock a single row a long time.
-
Disadvantages of row-level locking:
- Takes more memory than page-level or table-level locks.
- Is slower than page-level or table-level locks when used on a large part of the table because you must acquire many more locks.
- Is definitely much worse than other locks if you often do GROUP BY operations on a large part of the data or if you often must scan the entire table.
- With higher-level locks, you can also more easily support locks of different types to tune the application, because the lock overhead is less than for row-level locks.
-
Table locks are superior to page-level or row-level locks in the following cases:
- Most statements for the table are reads.
- Read and updates on strict keys, where you update or delete a row that can be fetched with a single key read:
UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
DELETE FROM tbl_name WHERE unique_key_col=key_value;
- SELECT combined with concurrent INSERT statements, and very few UPDATE and DELETE statements.
- Many scans or GROUP BY operations on the entire table without any writers.
Solution 3
A row Table level lock is better for a large table where major data modifications are taking place. This lets the system contend with a single lock on the table rather than having to deal with a gazillion locks (one for each row).
The RDBMS automatically escalates locking levels internally.
Solution 4
Table locking enables many sessions to read from a table at the same time
To achieve a very high lock speed, MySQL uses table locking
"I would presume that row-level locking is better because" [you lock less data].
First "better" is poorly defined in this page. It appears that better means "faster".
Row-level locking cannot (in general) be faster because of contention for locks. Locking each row of a large result set means the very real possibility of a conflict with another large result set query and a rollback.
Comments
-
Jason Baker over 3 years
According to the MySQL manual:
For large tables, table locking is often better than row locking,
Why is this? I would presume that row-level locking is better because when you lock on a larger table, you're locking more data.
-
Jon Black over 13 yearsI think you mean "A row lock is better..." :P
-
Jason Baker over 13 yearsI see. So perhaps it would be better if it were phrased more like "for large resultsets, table locking is often better than row locking".