离线并发与锁机制

离线并发与锁机制

离线并发的来源

在Web项目中，离线并发显得尤其严重。例如，用户A和用户B同时修改数据库中的某张表的R行，加入R行有两个字段分别是C1，C2。

假如按照如下过程修改：

1 用户A将数据R（C1,C2）读取到A的浏览器中。

2 用户B将数据R（C1,C2）读取到B的浏览器中。

3 用户A在浏览器上将数据修改为R（C1’，C2），同时更新到数据库。

4 用户B在浏览器上将数据修改为R（C1，C2’），同时更新到数据库。

上述过程存在两个问题，第一，第4步B在修改数据的时候数据库中的数据和B的浏览器中数据已经不一致了；第二，如果程序按照哪个字段变化在数据库中更新哪个字段的方式处理的话，那么经过上述四步修改，数据库中R行的内容是（C1’,C2’），这和A或者B的想法都不同（A认为是（C1’，C2），B认为是（C1，C2’））。

上述过程中A对数据库的修改过程或者B对数据库的修改过程，都是无法根据数据库的最新内容做修改，所以成为离线。A和B同时对记录R进行就该叫离线。

以上的环境叫离线并发。
那么如何解决离线并发过程中遇到的问题呢？我们引入锁机制。

锁机制
锁机制，就是在需要修改的数据上加互斥锁，通过互斥锁避免数据被同时修改。锁机制更具其应用环境又分为乐观锁和悲观锁

乐观锁
乐观锁，指认为冲突很少发生，所以只是在数据修改的时候比较修改的基础数据和数据库中的数据是否相同，相同则修改，否则提示用户重新装入数据库中已经变化的数据。

实现方法1：在进行update的时候使用where条件，在Where标间中比较所有上一步中查询得到的数据。如果数据库中的数据没有变化，则update可以更新到内容，否则update语句不能更新到内容，可以根据update的返回值确定更新是否成功。

实现方法2：在每一个表中追加一个特殊字段，类型为timestamp，每次更新的时候比较这个字段的值是否一致，如果一致，则更新，同时将这个字段更新为当前时间，否则，说明数据已经变更。这也可以使用update加上where实现。

悲观锁
悲观锁指，需要修改的数据，在读取的时候就对数据加锁，其他用户在准备修改，读取数据的阶段判断数据是否上锁，以此来决定是否进行修改前的读操作。


实现方法：
通常在数据库中建立一张lock表，该表的字段包括，表明，唯一索引，时间，用户信息等。


在用户读取数据准备修改的时候，首先判断lock表中是否存在自己将要读取的数据。

如果不存在，则在lock表中添加一条记录，记录对那张表的哪行数据进行修改；如果存在，在判断时间字段是否超时。

如果超时，则更新lock表中本条记录的时间字段。（防止死锁的必要手段）

如果存在，也不超时，说明本条记录正在被其他用户修改，则返回并发信息。

你可能不知道的MySQL

一、"\G"的作用；
二、"Group by"的“隐形杀手”；
三、大批量数据插入；
四、最快复制表结构方法；
五、加引号和不加引号区别；
六、前缀索引；
七、MySQL索引使用方式


前言：

实验的数据表如下定义：

mysql> desc tbl_name;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| uid | int(11) | NO | | NULL | |
| sid | mediumint(9) | NO | | NULL | |
| times | mediumint(9) | NO | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)存储引擎是MyISAM，里面有10,000条数据。

一、"\G"的作用

mysql> select * from tbl_name limit 1;
+--------+--------+-------+
| uid | sid | times |
+--------+--------+-------+
| 104460 | 291250 | 29 |
+--------+--------+-------+
1 row in set (0.00 sec)

mysql> select * from tbl_name limit 1\G;
*************************** 1. row ***************************
uid: 104460
sid: 291250
times: 29
1 row in set (0.00 sec)有时候，操作返回的列数非常多，屏幕不能一行显示完，显示折行，试试”\G”，把列数据逐行显示（”\G”挽救了我，以前看explain语句横向显示不全折行看起来巨费劲，还要把数据和列对应起来）。

二、"Group by"的“隐形杀手”

mysql> explain select uid,sum(times) from tbl_name group by uid\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl_name
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10000
Extra: Using temporary; Using filesort
1 row in set (0.00 sec)

mysql> explain select uid,sum(times) from tbl_name group by uid order by null\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl_name
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10000
Extra: Using temporary
1 row in set (0.00 sec)默认情况下，Group by col会对col字段进行排序，这就是为什么第一语句里面有Using filesort的原因，如果你不需要对col字段进行排序，加上order by null吧，要快很多，因为filesort很慢的。

三、大批量数据插入

最高效的大批量插入数据的方法：

load data infile ‘/path/to/file’ into table tbl_name;

如果没有办法先生成文本文件或者不想生成文本文件，可以一次插入多行：

insert into tbl_name values (1,2,3),(4,5,6),(7,8,9)…

注意一条sql语句的最大长度是有限制的。如果还不想这样，可以试试MySQL的prepare，应该都会比硬生生的逐条插入要快许多。

如果数据表有索引，建议先暂时禁用索引：

alter table tbl_name disable keys;

插入完毕之后再激活索引：

alter table tbl_name enable keys;

对MyISAM表尤其有用。避免每插入一条记录系统更新一下索引。

四、最快复制表结构方法

mysql> create table clone_tbl select * from tbl_name limit 0;
Query OK, 0 rows affected (0.08 sec)

只会复制表结构，索引不会复制，如果还要复制数据，把limit 0去掉即可。

五、加引号和不加引号区别

给数据表tbl_name添加索引：

mysql> create index uid on tbl_name(uid);

测试如下查询：

mysql> explain select * from tbl_name where uid = '1081283900'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl_name
type: ref
possible_keys: uid
key: uid
key_len: 4
ref: const
rows: 143
Extra:
1 row in set (0.00 sec)我们在整型字段的值上加索引，是可以用到索引的，网上不少人误传在整型字段上加引号无法使用索引。修改uid字段类型为varchar(12):

mysql> alter table tbl_name change uid uid varchar(12) not null;

测试如下查询：

mysql> explain select * from tbl_name where uid = 1081283900\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl_name
type: ALL
possible_keys: uid
key: NULL
key_len: NULL
ref: NULL
rows: 10000
Extra: Using where
1 row in set (0.00 sec)我们在查询值上不加索引，结果索引无法使用，注意安全。

六、前缀索引

有时候我们的表中有varchar(255)这样的字段，而且我们还要对该字段建索引，一般没有必要对整个字段建索引，建立前8~12个字符的索引应该就够了，很少有连续8~12个字符都相等的字段。

为什么？更短的索引意味索引更小、占用CPU时间更少、占用内存更少、占用IO更少和很更好的性能。

七、MySQL索引使用方式

MySQL在一个查询中只能用到一个索引，所以要根据查询条件建立联合索引，联合索引只有第一位的字段在查询条件中能才能使用到。

如果MySQL认为不用索引比用索引更快的话，那么就不会用索引。

mysql> create index times on tbl_name(times);
Query OK, 10000 rows affected (0.10 sec)
Records: 10000 Duplicates: 0 Warnings: 0

mysql> explain select * from tbl_name where times > 20\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl_name
type: ALL
possible_keys: times
key: NULL
key_len: NULL
ref: NULL
rows: 10000
Extra: Using where
1 row in set (0.00 sec)

mysql> explain select * from tbl_name where times > 200\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl_name
type: range
possible_keys: times
key: times
key_len: 3
ref: NULL
rows: 1599
Extra: Using where
1 row in set (0.00 sec)数据表中times字段绝大多数都比20大，所以第一个查询没有用索引，第二个才用到索引。