MySQL Tutorial: A summary of the lock mechanism in MySQL

Original link: http://blog.chinaunix.net/u/29134/showart_1087632.html

Today to briefly summarize the locking mechanism of MySQL, improper welcome to PAT Bricks!

1, for MySQL, there are three kinds of lock level: page level, table level, row level.

The typical page-level representative engine is BDB.

The typical representative engine for the table level is myisam,memory and ISAM, a long time ago.

The typical line-level representative engine is InnoDB.

2, we use the most of the actual application is the row lock.

The advantages of row-level locks are as follows:

1. Reduce lock state when many connections have different queries.

2), if there is an exception, you can reduce the loss of data. Because you can roll back only one row or a few lines of data at a time.

The disadvantages of row-level locks are as follows:

1), more memory than page-level locks and table-level locks.

2), the query is more than page-level locks and table-level locks need more I/O, so we often use row-level locks in the write operation rather than read operations.

3), easy to appear deadlock.

3, the MySQL uses writes the queue and reads the queue to realize to the database writes and reads the operation.

For write locks as follows:

1), if the table is not locked, then write lock on it.

2), otherwise, put the request into the write lock queue.

For read locks as follows:

1), if the table does not write lock, then add a read lock.

2), otherwise, put the request into the read lock queue.

Of course, we can use low_priority and high_priority to change these behaviors in both write and read operations.