autocommit的含义

背景

《高性能MySQL》中，针对队列表（表中包含多种类型记录：未处理记录、已处理记录、正在处理记录）的操作，讲到两种问题：
（1）随着对列表的越来越大、表深度增加，找未处理的记录的速度会变慢。
（2）一般处理方式：先找到未处理记录，然后加锁，执行业务逻辑，这种方式会增大处理实例的竞争（一个线程正在执行，其他线程只能等待），限制可扩展性。

针对（1）的作者建议：将队列表分成两部分，处理完成的记录归档到历史表。
针对（2）的方式，自查书即可，这里不再赘述。

本文讨论的问题是：如何让处理实例标识正在被处理的记录，而不是让多个实例重复处理？

解决方式

一般，select * for update；作者建议任何情况下尽量避免使用，如下为通用做法：先加行锁读取到需要处理的10条记录，然后对其进行处理。

begin
select * from email where owner = 0 and status = 'unsent' limit 10 for update;
update email set status = 'claimed', owner = CONNECTION_ID() where id in (123, 456, 789);
commit

上述的问题：一个实例处理到查和更新之间，其他实例只能被阻塞。所有的这种查询都将使用同样的索引，扫描相同的部分。

set autocommit = 1;
update email set status = 'claimed', owner = CONNECTION_ID() where owner = 0 and status = 'unsent'
set autocommit = 0;
select id from email where owner = CONNECTION_ID() and status = 'claimed';

无需使用for update，C端协议会告诉更新了哪些数据。

还存在的问题，正在处理的进程，因为某种原因退出，怎么办？
定期执行update将其更新为原始状态。

update email set owner = 0, status = 'unsent' where owner not in (0,10,20,30) and status = 'claimed' and ts < CURRENT_TIMESTAMP - INTERVAL 10 MINUTE;

owner从哪里获取？

show processlist

附录

什么是autocommit?

mysql默认开启

show variables like 'autocommit';

autocommit开启时，即使没有手动start transaction开启事务，它也会将用户的操作当做事务实时提交。
eg：执行了insert into test values(2)，mysql默认开启事务，并且在这条插入语句执行完成后，提交事务。
autocommit开启时，并且又手动开启事务，那么mysql会把start transaction 与 commit之间的语句当做一次事务来处理，默认不会帮用户提交，需要手动提交，如果用户不提交便退出了，那么事务将回滚。
将autocommit关闭后，系统默认开始事务，但是并没有默认帮你提交事务，因此如果在A客户端执行commit之后，B客户端才能查询到新的数据。
可以做实验如下：
（1）手动关闭autocommit
（2）a端执行update
（3）b端执行select
（4）a端执行commit
（5）b端执行select

connection_id

CONNECTION_ID()
返回的是这个连接的连接ID或者thread ID。
对于已经建立连接的客户端，都有一个唯一的连接ID。
这个值和 INFORMATION_SCHEMA.PROCESSLIST一样，也和SHOW PROCESSLIST输出的ID列一样，也是Performance Schema threads 表里面的PROCESSLIST_ID。