Dead-Knock MySQL (4)

Want to see the forum and all of the blog on the MySQL all over, death hit the end

See something about a database snapshot .... Don't understand, Baidu ... Then on the topic, see the table lock this thing unlock tables;

Used to lock the table .....

Mysql> INSERT into new values (' haha '); ERROR 1136 (21s01): Column count doesn ' t match value count at row 1 mysql> desc new; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | ID | Int (11) | NO | PRI |       NULL | | | name | varchar (20) | YES |         MUL |       | | +-------+-------------+------+-----+---------+-------+ 2 rows in Set (0.36 sec)

Mysql> INSERT into new values (1234, ' haha '); ERROR 1099 (HY000): Table ' new ' is locked with a READ lock and can ' t be updated

mysql> unlock tables new; Error 1064 (42000): You have a error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use-near ' new ' in line 1 mysql> Unlock tables; Query OK, 0 rows Affected (0.00 sec)

Mysql> INSERT into new values (1235, ' haha '); Query OK, 1 row affected (0.94 sec)

--lock tables New read lock, once locked does not promise other threads to write operations ...

--lock tables New Write;xie lock, once locked, does not promise other threads to read the operation ...

I saw this sentence on the Internet, but it was against my test.

Note: The user table must be a MyISAM table, the above test can be all OK, if the user table is a innodb table, then the lock tables user read local command may have no effect, that is, if the user table is a innodb table, The 6th moment will not be blocked, because the InnoDB table is transactional, for transactions

Tables such as InnoDB and bdb,--single-transaction are a better option because it does not need to lock the table at all//

Against

Take a look at my database engine

Mysql> Show CREATE TABLE new\g;
1. Row ***************************
Table:new
Create table:create Table ' new ' (
' id ' int (one) is not NULL,
' Name ' varchar (DEFAULT '),
PRIMARY KEY (' id '),
KEY ' name ' (' name ')
) Engine=innodb DEFAULT Charset=utf8
1 row in Set (0.00 sec)

There is no exception, the InnoDB database engine can also use table locks

Speculation is the difference between a database version

Mysql> select version ();
+------------+
| Version () |
+------------+
| 5.6.15-log |
+------------+
1 row in Set (0.00 sec)

Test again using MyISAM

Forgot to turn off the table lock, also found a problem, not only the table problem, and even the creation of database tables are not locked, beyond my expectation.

Mysql> CREATE TABLE NEW3 (ID int primary key, name varchar () default ") engine
=myisam,charset=utf8;
ERROR 1100 (HY000): Table ' new3 ' is not locked with LOCK TABLES

Again, it is not only the entire operation of the database table is disturbed, only read is allowed,

Whether the modification of the test table is also possible.

Mysql> ALTER TABLE new1 add haha int;
ERROR 1100 (HY000): Table ' New1 ' is not locked with LOCK TABLES

Sure enough, only read, modify, add, and modify the structure of the table are locked.

Back to test MyISAM

Mysql> CREATE TABLE NEW3 (ID int primary key, name varchar () default ") engine
=myisam,charset=utf8;
Query OK, 0 rows affected (0.06 sec)

Added successfully, the lock has been closed

Mysql> Show CREATE TABLE new3\g;
1. Row ***************************
Table:new3
Create table:create Table ' new3 ' (
' id ' int (one) is not NULL,
' Name ' varchar (DEFAULT '),
PRIMARY KEY (' id ')
) Engine=myisam DEFAULT Charset=utf8
1 row in Set (0.00 sec)

mysql> INSERT into NEW3 values (12346, ' asdf ');
ERROR 1099 (HY000): Table ' new3 ' is locked with a READ lock and can ' t be update
D

Cannot insert

mysql> Update NEW3 Set name = ' 123DSFV ' where id=1;
ERROR 1099 (HY000): Table ' new3 ' is locked with a READ lock and can ' t be update
D

Cannot update

Mysql> ALTER TABLE NEW3 add haha int;
ERROR 1099 (HY000): Table ' new3 ' is locked with a READ lock and can ' t be update
D

Cannot modify table

| 1233 | name |
| 1234 | name |
+------+------+
1235 rows in Set (0.00 sec)

can find

Compare Blog http://blog.chinaunix.net/uid-21505614-id-289450.html

Dead-Knock MySQL (4)