Why is table-level locking better than row-level locking for large tables?

Why is table-level locking better than row-level locking for large tables?

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.

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.

• 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.
  • 엄격한 키를 읽고 업데이트합니다. 여기서 단일 키 읽기로 가져올 수 있는 행을 업데이트하거나 삭제합니다.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.

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).

RDBMS는 내부적으로 잠금 수준을 자동으로 높입니다.

테이블 잠금을 통해 많은 세션을 테이블에서 동시에 읽을 수 있습니다.

매우 높은 잠금 속도를 달성하기 위해 MySQL은 테이블 잠금을 사용합니다.

"행 수준 잠금이 더 나은 이유는 데이터를 덜 잠그기 때문이라고 생각합니다.

첫번째로 "better"는 이 페이지에서 잘 정의되지 않습니다.더 빠른 것을 의미하는 것 같습니다.

일반적으로 행 수준 잠금은 잠금 경합 때문에 더 빠를 수 없습니다.큰 결과 집합의 각 행을 잠그면 다른 큰 결과 집합 쿼리와 롤백이 발생할 가능성이 매우 높습니다.

일반적으로 많은 데이터를 잠글 필요가 있는 경우 큰 테이블의 잠금 1개가 전체 행 수준 또는 페이지 잠금보다 저렴합니다.

언급URL : https://stackoverflow.com/questions/3462643/why-is-table-level-locking-better-than-row-level-locking-for-large-tables