【已解决】关于锁表问题，请高手指点方向，谢谢！！！（急）

devilring

背景：
1、首先我用的是mysql的MyISAM引擎数据库
2、现在我有个表，这个表被多个线程频繁读和写，补充一下，几乎每0.5秒都有读写
3、数据量小的时候，比如几十万条数据的时候，读写速度都很快的情况下，不会出现问题
4、但当数据量增加后，读写速度都有所下降时，这时候就会出现锁表现象，因为我的所有读写操作都是通过存储过程来实现的，而存储过程返回失败有个超时时间，这就造成与数据库交互的服务程序中的多个数据库连接挂起（虽然我可以无限制的继续补充数据库线程池使用新的连接，但这显然不现实），这时候我只能重启服务，并移除表中的历史数据来暂时解决表锁问题；
5、所有的读操作都是使用默认锁机制，就我了解应该是MyISAM的读锁，就是锁写不锁读；所有的写操作也是用的默认锁机制，读写都锁；

考虑：
1、我曾经想过使用InnoDB的方式创建该表（为了行锁），但是我的读操作未必能使用到精确的主键，这种情况下InnoDB类型的表依然使用的是表锁，还是解决不了锁表问题
2、我也考虑过用读写分离技术，创建主备库，不过因为我刚开始使用mysql，对此不是很熟悉，还没来的及试验，而且频繁写操作之间的互斥也不能很好的解决，尤其是表的数据量每天增量都在10万以上；

问题：
1、怎样能解决读和写之间的锁表冲突
2、怎样竟可能的解决多线程写的锁表冲突

因为我刚开始接受mysql数据库，还很不熟悉，泛泛的查找资料所需时间比较长还不一定能解决问题；
所以请高手指点解决方向，这样我在查找资料和做实验的时候也好有个范围，谢谢！！！

horizonhyg

回复 1# devilring


   

1、我曾经想过使用InnoDB的方式创建该表（为了行锁），但是我的读操作未必能使用到精确的主键，这种情况下InnoDB类型的表依然使用的是表锁，还是解决不了锁表问题

   
        这是什么意思，用不到主键咋还表锁了，用不到主键建其他索引啊

devilring

使用innodb默认的是行锁机制，意思就是说默认的锁粒度最小为行，但是就我所知，如果是精确查找行锁才会有效，模糊查询时，则会升级为表锁，
如：
create table t1 (id ,  name)
id 为PK，那么在使用 select * from t1 where id = ..    查询时是行锁
如果使用select * from t1 where id >   ...  或者  select * from t1 where id like ...  是，则自动升级为表锁

这就是我这句话的意思

devilring

看来高手周末都休息，今天开始工作了，请高手们不吝赐教，谢谢

horizonhyg

devilring 发表于 2012-06-30 15:34
使用innodb默认的是行锁机制，意思就是说默认的锁粒度最小为行，但是就我所知，如果是精确查找行锁才会有效 ...

       是谁告诉你id>和like是表锁，id>是范围查询，如果加上limit，跟主键索引有啥区别，like如果不是前缀模糊，那么也会走索引，跟表锁又有什么关系？重要看优化。

devilring

晕，5楼的兄弟，能不能别再纠结这个问题了，你也说了“如果加上limit”，我的查询就是like而且其它的限制也都是范围性的，也就是说查询的结果很可能用不到主键索引，所以数据库肯定会自动把锁表粒度升级到表锁的，这个我测试过了，在我做查询的时候，insert和update都不行，当然我用的肯定不是脏读的方式，就是我的select语句没有加with share lock 或者 with rowlock什么的，就是最普通的标准sql;
顺带再说一句，如果优化能解决这个问题，也请你说详细点儿告诉我，我并不知道该如何优化，谢谢

如果你有什么好想法的话，是否能多写点儿，写详细点儿，指点我下，谢谢
又或者说我上面说的有错误，但是问题还是那2个问题，你可以在解决方法中顺序的告诉我，你看我刚接触mysql，说错了也可以理解吧，只要你给我指出来，最好能详细点儿，再次感谢！

cenalulu

首先，楼主更换成innodb是一个很好的想法，也是一个能彻底解决问题的方案，建议顺着这个思路继续走下去。
楼主提到innodb的几个问题，回答下。
1. 如果不按照主键查询，只要是能够用到索引就不会遭遇table lock （确保所有SQL都能用到索引，这一点比较容易解决，但是需要协调开发）
2. 读写分离是更进一步的实施方案，如果再换成innodb后，row lock 仍有可能同时被update 和 select征用时，可以考虑。

devilring

刚才在网上查mysql读写分离的相关资料，看见有人说做读写分离适用于以下背景：
1.写的频率必须不大.
2.基本思路是.两个库,一个库专门用来读.一个库专门用来写.当有数据写入后.触发,更新只读的数据库.这里的要求是,数据及时性没要求.并且写的频率不高.
不知道这种说法对不对?
如果是这样，我就不能用读写分离了，我的写频率很高啊

cenalulu

1. 写频率必须不大 （这个不是绝对的，这样的结论也比较不负责任，估计他想说的是，由于replication在slave端是串行并全量执行的，因此master的写请求还是要到slave上执行，届时还是会有读写之间的征用。好处是slave上的写是串行的，拆分了压力。但如果写操作过于频繁，超过了单线程的replication的执行能力，会导致slave lag，对业务方不是很友好。
2. 按照对于实时性的需求高低可以将读请求分别导向到 master或者slave。

devilring

cenalulu 发表于 2012-07-02 13:07
1. 写频率必须不大 （这个不是绝对的，这样的结论也比较不负责任，估计他想说的是，由于replication在slave ...

如果这样的话，可能我这里的情况还真不太适合使用读写分离技术
我这里目前的数据表吞吐量如下：
1、增量：10-20万条数据/每天
2、插入频率：大概1-2条数据/秒，而且每条数据在插入后要经过几次状态的修改，差不多有5个流程要读并且修改状态，也就是说数据表的变更操作实际上是每秒10次以上；
3、周期：基本上24小时不停，每日也会有那么几个小时数据的写入频率会降低一些，但是最低不会低于1条数据/2秒插入