mysql> show variables like "%pointer%";
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| myisam_data_pointer_size | 6 |
+--------------------------+-------+
1 row in set (0.00 sec)
我们来做个实验试试
如果myisam_data_pointer_size=2,那么就意味着一个表的最大数据文件是65535/1024=64K
mysql> set global myisam_data_pointer_size=2;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "%pointer%";
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| myisam_data_pointer_size | 2 |
+--------------------------+-------+
1 row in set (0.00 sec)
我们来创建一个 大表ss
mysql> create table ss select * from information_schema.tables ;
Query OK, 54 rows affected (0.09 sec)
Records: 54 Duplicates: 0 Warnings: 0
mysql> insert into ss select * from ss;
Query OK, 108 rows affected (0.01 sec)
Records: 108 Duplicates: 0 Warnings: 0
mysql> insert into ss select * from ss;
Query OK, 216 rows affected (0.01 sec)
Records: 216 Duplicates: 0 Warnings: 0
这个时候出现了表ss满了的错误,我们看看数据文件 是64K,要想继续可以插入数据,那么就要把这个参数调大
mysql> insert into ss select * from ss;
ERROR 1114 (HY000): The table 'ss' is full
mysql> insert into ss select * from ss;
ERROR 1114 (HY000): The table 'ss' is full
mysql> insert into ss select * from ss;
ERROR 1114 (HY000): The table 'ss' is full
[root@test3 test]# ls -lh
total 116K
-rw-rw----. 1 mysql mysql 9.3K Sep 12 06:44 ss.frm
-rw-rw----. 1 mysql mysql 64K Sep 12 06:44 ss.MYD
-rw-rw----. 1 mysql mysql 1.0K Sep 12 06:44 ss.MYI
mysql> insert into ss select * from ss;
ERROR 1114 (HY000): The table 'ss' is full
mysql> alter table ss max_ROWS=10000000000 ;
Query OK, 496 rows affected (0.11 sec)
Records: 496 Duplicates: 0 Warnings: 0
mysql> insert into ss select * from ss;
Query OK, 496 rows affected (0.02 sec)
Records: 496 Duplicates: 0 Warnings: 0
mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 2979 |
| Table_locks_waited | 0 |
+-----------------------+-------+
2 rows in set (0.00 sec))
获得表film_text的READ锁定
mysql> lock table film_text read;
Query OK, 0 rows affected (0.00 sec)
当前session可以查询该表记录
mysql> select film_id,title from film_text where film_id = 1001;
+---------+------------------+
| film_id | title |
+---------+------------------+
| 1001 | ACADEMY DINOSAUR |
+---------+------------------+
1 row in set (0.00 sec)
其他session也可以查询该表的记录
mysql> select film_id,title from film_text where film_id = 1001;
+---------+------------------+
| film_id | title |
+---------+------------------+
| 1001 | ACADEMY DINOSAUR |
+---------+------------------+
1 row in set (0.00 sec)
当前session不能查询没有锁定的表
mysql> select film_id,title from film where film_id = 1001;
ERROR 1100 (HY000): Table 'film' was not locked with LOCK TABLES
其他session可以查询或者更新未锁定的表
mysql> select film_id,title from film where film_id = 1001;
+---------+---------------+
| film_id | title |
+---------+---------------+
| 1001 | update record |
+---------+---------------+
1 row in set (0.00 sec)
mysql> update film set title = 'Test' where film_id = 1001;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
当前session中插入或者更新锁定的表都会提示错误:
mysql> insert into film_text (film_id,title) values(1002,'Test');
ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updated
mysql> update film_text set title = 'Test' where film_id = 1001;
ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updated
其他session更新锁定表会等待获得锁:
mysql> update film_text set title = 'Test' where film_id = 1001;
等待
释放锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
等待
Session获得锁,更新操作完成:
mysql> update film_text set title = 'Test' where film_id = 1001;
Query OK, 1 row affected (1 min 0.71 sec)
Rows matched: 1 Changed: 1 Warnings: 0
当使用LOCK TABLES时,不仅需要一次锁定用到的所有表,而且,同一个表在SQL语句中出现多少次,就要通过与SQL语句中相同的别名锁定多少次,否则也会出错!举例说明如下。
(1)对actor表获得读锁:
mysql> lock table actor read;
Query OK, 0 rows affected (0.00 sec)
(2)但是通过别名访问会提示错误:
mysql> select a.first_name,a.last_name,b.first_name,b.last_name from actor a,actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name = 'Tom' and a.last_name <> b.last_name;
ERROR 1100 (HY000): Table 'a' was not locked with LOCK TABLES
(3)需要对别名分别锁定:
mysql> lock table actor as a read,actor as b read;
Query OK, 0 rows affected (0.00 sec)
(4)按照别名的查询可以正确执行:
mysql> select a.first_name,a.last_name,b.first_name,b.last_name from actor a,actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name = 'Tom' and a.last_name <> b.last_name;
+------------+-----------+------------+-----------+
| first_name | last_name | first_name | last_name |
+------------+-----------+------------+-----------+
| Lisa | Tom | LISA | MONROE |
+------------+-----------+------------+-----------+
1 row in set (0.00 sec)
WRITE锁表
获得表film_text的WRITE锁定
mysql> lock table film_text write;
Query OK, 0 rows affected (0.00 sec)
当前session对锁定表的查询、更新、插入操作都可以执行:
mysql> select film_id,title from film_text where film_id = 1001;
+---------+-------------+
| film_id | title |
+---------+-------------+
| 1001 | Update Test |
+---------+-------------+
1 row in set (0.00 sec)
mysql> insert into film_text (film_id,title) values(1003,'Test');
Query OK, 1 row affected (0.00 sec)
mysql> update film_text set title = 'Test' where film_id = 1001;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
其他session对锁定表的查询被阻塞,需要等待锁被释放:
mysql> select film_id,title from film_text where film_id = 1001;
等待
释放锁: