MySQL lock tables and unlock tables using the detailed

Source: Internet
Author: User

The Lock Tables command locks the table for the current thread. Here are 2 types of locks, one is read lock, with command lock tables TableName read, another is write lock, with command lock tables tablename Write. The following are described separately:
1. Lock Table Read lock
If a thread obtains a read lock on a table, the thread and all other threads can only read data from the table, and cannot do any write operations.
Below we test, the test table is the user table.
Different threads can be implemented by opening multiple command line MySQL clients:

Moment Point
Thread A (Command line window a)
Thread B (Command line window B)

The code is as follows Copy Code

mysql> lock tables User read;
Query OK, 0 rows Affected (0.00 sec)
Mysql>

Add a read lock to the user table.


2

The code is as follows Copy Code
Mysql> select * from user;
+------+-----------+
| ID | name |
+------+-----------+
| 22 | ABC |
| 223 | DABC |
| 2232 | DDDABC |
| 45 | Asdsagd |
| 23 | ddddddddd |
+------+-----------+
5 rows in Set (0.00 sec)
Mysql>

Your own read operation is not blocked

The code is as follows Copy Code
Mysql> select * from user;
+------+-----------+
| ID | name |
+------+-----------+
| 22 | ABC |
| 223 | DABC |
| 2232 | DDDABC |
| 45 | Asdsagd |
| 23 | ddddddddd |
+------+-----------+
5 rows in Set (0.00 sec)
Mysql>

Read from other threads is not blocked

3

The code is as follows Copy Code
mysql> INSERT into user values (' test ');
ERROR 1099 (HY000): Table ' user ' is locked with a READ lock and can ' t is updated
Mysql>

The write operation for this thread was found to be blocked

The code is as follows Copy Code
mysql> INSERT into user values (' 2test ');

Found no response, has been waiting, the description has not been written locked, has been waiting.

4

The code is as follows Copy Code
mysql> unlock tables;
Query OK, 0 rows Affected (0.00 sec)
Mysql>
Releases read locks.
mysql> INSERT INTO user values ("ddd");
Query OK, 1 row affected (1 min 27.25 sec)
Mysql>

Thread A frees up the read lock, threads B gets the resource, and the write that just waits executes.

5

The code is as follows Copy Code
mysql> Lock tables User read local;
Query OK, 0 rows Affected (0.00 sec)
Mysql>
Increase the local option when acquiring a read lock.
mysql> INSERT INTO user values (2, ' B ');
Query OK, 1 row Affected (0.00 sec)
Mysql>

An insert from another thread was found to be not blocked.

6

The code is as follows Copy Code
mysql> Update user Set name = ' AAAAAAAAAAAAAAAAAAAAA ' where id = 1;

However, the update operation for other threads is blocked.

Note: The user table must be a MyISAM table for all of the above tests to be OK, and if the user table is a InnoDB table, the lock tables user read local command may not be effective, that is, if the user table is a InnoDB table, the 6th moment will not be blocked , because the InnoDB table is transactional and is a better option for transaction tables, such as InnoDB and bdb,--single-transaction, because it doesn't require locking tables at all
2. Lock Table Write lock
If a thread gets a WRITE lock on a table, only the thread that owns the lock can read and write the table from the table. The other threads are blocked.
Write locked command: Lock tables user Write.user table is MyISAM type.
Refer to the following test:

Moment Point
Thread A (Command line window a)
Thread B (Command line window B)

The code is as follows Copy Code


mysql> lock tables user write;
Query OK, 0 rows Affected (0.00 sec)

Write a lock on the user table.

The code is as follows Copy Code


Mysql> select * from user;
+----+-----------------------+
| ID | name |
+----+-----------------------+
| 1 | aaaaaaaaaaaaaaaaaaaaa |
| 2 | B |
+----+-----------------------+
2 rows in Set (0.00 sec)

You can continue the read operation yourself

The code is as follows Copy Code
Mysql> select * from user;

Other thread read operations are blocked.

The code is as follows Copy Code
mysql> unlock tables;
Query OK, 0 rows Affected (0.00 sec)

Releases the lock.

  code is as follows copy code
 
Mysql> select * from user;
+----+-----------------------+
| id | name                   |
+----+-----------------------+
|  1 | aaaaaaaaaaaaaaaaaaaaa |
|  2 | b    & nbsp;                |
+----+-----------------------+
2 rows in Set (32.56 sec)

Other threads get resources and can read the data.


All of the above results are tested and passed under MySQL 5.4.3.

How do I use commands in MySQL to see if a table is a MyISAM type or a InnoDB type?

The code is as follows Copy Code

Show CREATE TABLE * * *

Engine=myisam auto_increment=14696 DEFAULT Charset=utf8 |


MySQL Lock-table problem


Show Processlist View the state of the table in the database and whether it is locked;

Kill ID//destroy the locked table

===================================================

  code is as follows copy code

Set autocommit=0;

Select * from t1  where uid= ' xxxx ' for update   //row lock with index (for example, UID) or table lock

INSERT into T1 values (1, ' xxxxx ');

Commit;

 

Lock tables T1 write|read;

INSERT into T1 values (2, ' xxxxx ');//Only Insert

Unlock tables;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.