2015年3月

sql使用for update控制并发

MySQL 中select * from table where …... for update的用法

由于InnoDB预设是Row-Level Lock(行级锁)，所以只有「明确」的指定主键，MySQL才会执行Row lock (只锁住被选取的资料例) ，否则MySQL将会执行Table Lock (表锁)。

举个例子: 假设有个表单t，里面有id跟name二个栏位，id是主键。

CREATE TABLE t (
id int(11) NOT NULL DEFAULT '0',
name varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

例1: (明确指定主键，并且有此笔资料，row lock)

  • 由此可见，当明确指定主键，并且有此资料时，锁的是where后面的记录，即这里的id= 1； 接下来来看下没有此资料的情况下会不会被锁。

窗口一：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where id = 1 for update;
id name
1 bingo
1 row in set (0.00 sec)

窗口二：

mysql> select * from t where id = 1;
id    name
1    bingo
1 row in set (0.01 sec)
mysql> update t set name = 'xxm' where id = 2;
Query OK, 1 row affected 
(0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t set name = 'icey' where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

例2: (明确指定主键，若查无此笔资料，无lock)

  • 由此得出结论，在没有此资料的情况下，即使你for update也是不锁的

窗口1：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where id = 11 for update;
Empty set (0.00 sec)

窗口2：

mysql> update t set name = 'qweq' where id = 1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t set name = 'qw' where id = 2;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t set name = 'vqw' where id = 3;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t set name = 'vqws' where id = 4;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t set name = 'vqs' where id = 5;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t set name = 'vqs' where id = 11;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> select * from t;
+----+------+

id    name
1    qweq
2    qw
3    vqw
4    vqws
5    vqs

例3: (无主键，table lock)

  • 由此可见，没有明确主键的情况下锁全表；

窗口1：

mysql> select * from t where name ='qweq' for update;
id    name
1    qweq
1 row in set (0.00 sec)

窗口2：

mysql> update t set name = 'vqs' where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t set name = 'vqs' where id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

例4: (主键不明确，table lock)

  • 在主键不明确的情况下，锁全表
SELECT * FROM products WHERE id<>'3' FOR UPDATE;

窗口1：

mysql> select * from t where id <> 2 for update;
id    name
1    qweq
3    vqw
4    vqws
5    vqs
4 rows in set (0.00 sec)

窗口2：

mysql> update t set name = 'vqs' where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t set name = 'vqs' where id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

例5: (主键不明确，table lock)

  • 锁全表

窗口1：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where id like 3 for update;
id    name
3    vqw
1 row in set (0.00 sec)

窗口2：

mysql> update t set name = 'vqs' where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t set name = 'vqs' where id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

注1: FOR UPDATE仅适用于InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。

一些常用php的header头

<?php 
header('HTTP/1.1 200 OK'); // ok 正常访问
header('HTTP/1.1 404 Not Found'); //通知浏览器 页面不存在
header('HTTP/1.1 301 Moved Permanently'); //设置地址被永久的重定向 301
header('Location: http://www.ithhc.cn/'); //跳转到一个新的地址
header('Refresh: 10; url=http://www.ithhc.cn/'); //延迟转向 也就是隔几秒跳转
header('X-Powered-By: PHP/6.0.0'); //修改 X-Powered-By信息
header('Content-language: en'); //文档语言
header('Content-Length: 1234'); //设置内容长度
header('Last-Modified: '.gmdate('D, d M Y H:i:s', $time).' GMT'); //告诉浏览器最后一次修改时间
header('HTTP/1.1 304 Not Modified'); //告诉浏览器文档内容没有发生改变

###内容类型### 
header('Content-Type: text/html; charset=utf-8'); //网页编码 
header('Content-Type: text/plain'); //纯文本格式 
header('Content-Type: image/jpeg'); //JPG、JPEG 
header('Content-Type: application/zip'); // ZIP文件 
header('Content-Type: application/pdf'); // PDF文件 
header('Content-Type: audio/mpeg'); // 音频文件 
header('Content-type: text/css'); //css文件
header('Content-type: text/javascript'); //js文件
header('Content-type: application/json'); //json
header('Content-type: application/pdf'); //pdf 
header('Content-type: text/xml'); //xml
header('Content-Type: application/x-shockw**e-flash'); //Flash动画 

###### 

###声明一个下载的文件###
header('Content-Type: application/octet-stream'); 
header('Content-Disposition: attachment; filename="ITblog.zip"'); 
header('Content-Transfer-Encoding: binary'); 
readfile('test.zip');
######

###对当前文档禁用缓存###
header('Cache-Control: no-cache, no-store, max-age=0, must-revalidate'); 
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); 
######

###显示一个需要验证的登陆对话框### 
header('HTTP/1.1 401 Unauthorized'); 
header('WWW-Authenticate: Basic realm="Top Secret"'); 
######


###声明一个需要下载的xls文件###
header('Content-Disposition: attachment; filename=ithhc.xlsx');
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Length: '.filesize('./test.xls')); 
header('Content-Transfer-Encoding: binary'); 
header('Cache-Control: must-revalidate'); 
header('Pragma: public'); 
readfile('./test.xls'); 
######


?>