Mysql下,批量更新并发操作产生死锁

matrix1984 2014-04-19 01:40:12
有一个表test,大约有5万数据,表结构如下:

CREATE TABLE `test` (
`key` bigint(20) NOT NULL AUTO_INCREMENT,
`id` bigint(20) DEFAULT NULL,
`name` varchar(60) DEFAULT NULL,
`createTime` datetime DEFAULT NULL,
PRIMARY KEY (`key`),
KEY `idx_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=50000 DEFAULT CHARSET=utf8;


其中字段key为主键,id上有非唯一性索引。现在有并发多线程,同时对该表的数据进行批量更新操作,各自线程取数据是根据对8取模余数不同来区分的,等于下面这个8个线程取得的数据是各自不会重复的


UpdateNameTask t1 = new TestUtil().new UpdateNameTask(8, 0);
UpdateNameTask t2 = new TestUtil().new UpdateNameTask(8, 1);
UpdateNameTask t3 = new TestUtil().new UpdateNameTask(8, 2);
UpdateNameTask t4 = new TestUtil().new UpdateNameTask(8, 3);
UpdateNameTask t5 = new TestUtil().new UpdateNameTask(8, 4);
UpdateNameTask t6 = new TestUtil().new UpdateNameTask(8, 5);
UpdateNameTask t7 = new TestUtil().new UpdateNameTask(8, 6);
UpdateNameTask t8 = new TestUtil().new UpdateNameTask(8, 7);
new Thread(t1).start();
new Thread(t2).start();
new Thread(t3).start();
new Thread(t4).start();
new Thread(t5).start();
new Thread(t6).start();
new Thread(t7).start();
new Thread(t8).start();


在线程类UpdateNameTask的run()方法,执行以下两步操作:
1)根据id更新name值:update test set name='new name' where id in (XXX);
这里的XXX表示,根据不同线程取得的id,对于线程t1,就是0, 8, 16, ...,表示id对8取模余数为0的所有记录的id
2)根据主键key更新name值:update test set name='new name2' where `key` in (YYY);
这里的YYY表示,1)中的那些记录对应的key值

也就是每个线程分别会更新自己的那批记录二次,先根据id更新,再根据主键key更新。还有,更新时,由于记录比较多,用for循环更新的,比如一次3000条,即上面的XXX和YYY是被分多次update的。

程序跑起来后,发现出现了死锁的错误:

com.mysql.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1041)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2077)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1356)
at junit.test.TestUtil.updateTestData(TestUtil.java:122)
at junit.test.TestUtil$UpdateNameTask.run(TestUtil.java:152)
at java.lang.Thread.run(Thread.java:595


8个线程获取的数据彼此是不重复的,行级锁也是锁自己的记录,为什么会出现死锁呢?
另外,如果 把UpdateNameTask的run()中的第2)步注释掉,却不会出现死锁。

迷茫中,看了关于Mysql的一篇博文也没能理解:http://hedengcheng.com/?p=771#_Toc374698311

大师们,救救啦。。。


...全文
3380 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
benluobo 2014-04-19
  • 打赏
  • 举报
回复
从日志可以很简单的看出 第一个事务在执行update where key的时候已经锁定了space id 64 page no 606 n bits 304 index `PRIMARY` of table `test`.`test 而这个索引行是第二个事务在执行update where id的时候需要锁定的行 第二个事务在执行的时候已经锁定了space id 64 page no 447 n bits 400 index `PRIMARY` of table `test`.`test` 而这个索引行正是第一个事务现在需要的 真正的原因是由于id是非唯一索引,在锁定的时候不能做到record lock,必须将对应的primary key lock 你可以试着将id改成unqiue key 再试试看
matrix1984 2014-04-19
  • 打赏
  • 举报
回复
不好意思,发现我的测试程序有问题:

if(_keys.indexOf(",") > -1) {
                    _keys = ids.substring(0, _keys.lastIndexOf(","));
                }
得改成:

if(_keys.indexOf(",") > -1) {
                    _keys = _keys.substring(0, _keys.lastIndexOf(","));
                }
抱歉,浪费大家时间了。关闭。
matrix1984 2014-04-19
  • 打赏
  • 举报
回复
引用 7 楼 matrix1984 的回复:
[quote=引用 6 楼 benluobobo 的回复:] 在线程类UpdateNameTask的run()方法,执行以下两步操作: 1)根据id更新name值:update test set name='new name' where id in (XXX); 这里的XXX表示,根据不同线程取得的id,对于线程t1,就是0, 8, 16, ...,表示id对8取模余数为0的所有记录的id 2)根据主键key更新name值:update test set name='new name2' where `key` in (YYY); 这里的YYY表示,1)中的那些记录对应的key值 将以上两步放在同一个事务中
我将两步的sql通过addBatch(sql)放在一起,执行,跟你说的在一个事务中执行,是一样的吗?

_stmt = conn.createStatement();
stmt.addBatch(String.format(updateSql, "["+mod+":"+remainder+"]", _ids));
stmt.addBatch(String.format(updateSql2, "{"+mod+":"+remainder+"}", _keys));
stmt.executeBatch();
结果是:所有的线程全部死锁! [/quote] 我的意思是,之前只是个别死锁(个别没执行成功),用这样的方式后每个线程的每个sql都没执行成功的。
matrix1984 2014-04-19
  • 打赏
  • 举报
回复
引用 6 楼 benluobobo 的回复:
在线程类UpdateNameTask的run()方法,执行以下两步操作: 1)根据id更新name值:update test set name='new name' where id in (XXX); 这里的XXX表示,根据不同线程取得的id,对于线程t1,就是0, 8, 16, ...,表示id对8取模余数为0的所有记录的id 2)根据主键key更新name值:update test set name='new name2' where `key` in (YYY); 这里的YYY表示,1)中的那些记录对应的key值 将以上两步放在同一个事务中
我将两步的sql通过addBatch(sql)放在一起,执行,跟你说的在一个事务中执行,是一样的吗?

_stmt = conn.createStatement();
stmt.addBatch(String.format(updateSql, "["+mod+":"+remainder+"]", _ids));
stmt.addBatch(String.format(updateSql2, "{"+mod+":"+remainder+"}", _keys));
stmt.executeBatch();
结果是:所有的线程全部死锁!
benluobo 2014-04-19
  • 打赏
  • 举报
回复
在线程类UpdateNameTask的run()方法,执行以下两步操作: 1)根据id更新name值:update test set name='new name' where id in (XXX); 这里的XXX表示,根据不同线程取得的id,对于线程t1,就是0, 8, 16, ...,表示id对8取模余数为0的所有记录的id 2)根据主键key更新name值:update test set name='new name2' where `key` in (YYY); 这里的YYY表示,1)中的那些记录对应的key值 将以上两步放在同一个事务中
matrix1984 2014-04-19
  • 打赏
  • 举报
回复
Record lock, heap no 121 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 800000000000626b; asc bk;; 1: len 6; hex 0000000178e4; asc x ;; 2: len 7; hex 70000002c32f90; asc p / ;; 3: len 8; hex 80000000000065c7; asc e ;; 4: len 11; hex 6a6976626e755b383a375d; asc jivbnu[8:7];; 5: len 5; hex 9992a612fa; asc ;; Record lock, heap no 146 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 8000000000006284; asc b ;; 1: len 6; hex 0000000178e4; asc x ;; 2: len 7; hex 70000002c3189d; asc p ;; 3: len 8; hex 80000000000061df; asc a ;; 4: len 11; hex 626f636576725b383a375d; asc bocevr[8:7];; 5: len 5; hex 9992a612fa; asc ;; Record lock, heap no 154 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 800000000000628c; asc b ;; 1: len 6; hex 0000000178e4; asc x ;; 2: len 7; hex 70000002ca34ba; asc p 4 ;; 3: len 8; hex 800000000000717f; asc q ;; 4: len 11; hex 776b676565625b383a375d; asc wkgeeb[8:7];; 5: len 5; hex 9992a612fa; asc ;; Record lock, heap no 155 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 800000000000628d; asc b ;; 1: len 6; hex 0000000178e4; asc x ;; 2: len 7; hex 70000002ca1df6; asc p ;; 3: len 8; hex 8000000000006d9f; asc m ;; 4: len 11; hex 78756f68686d5b383a375d; asc xuohhm[8:7];; 5: len 5; hex 9992a612fa; asc ;; Record lock, heap no 160 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 800000000000623d; asc b=;; 1: len 6; hex 0000000178e4; asc x ;; 2: len 7; hex 70000002e316fc; asc p ;; 3: len 8; hex 8000000000008cdf; asc ;; 4: len 11; hex 6575746278775b383a375d; asc eutbxw[8:7];; 5: len 5; hex 9992a612fa; asc ;; Record lock, heap no 162 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 800000000000627c; asc b|;; 1: len 6; hex 0000000178e4; asc x ;; 2: len 7; hex 70000002e32dc0; asc p - ;; 3: len 8; hex 80000000000090bf; asc ;; 4: len 11; hex 6a746c63676f5b383a375d; asc jtlcgo[8:7];; 5: len 5; hex 9992a612fa; asc ;; Record lock, heap no 165 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 8000000000006252; asc bR;; 1: len 6; hex 0000000178e4; asc x ;; 2: len 7; hex 70000002e8052d; asc p -;; 3: len 8; hex 80000000000094a7; asc ;; 4: len 11; hex 676474746f675b383a375d; asc gdttog[8:7];; 5: len 5; hex 9992a612fa; asc ;; Record lock, heap no 171 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 8000000000006205; asc b ;; 1: len 6; hex 0000000178e4; asc x ;; 2: len 7; hex 70000002e81c20; asc p ;; 3: len 8; hex 800000000000988f; asc ;; 4: len 11; hex 6b7676676e795b383a375d; asc kvvgny[8:7];; 5: len 5; hex 9992a612fa; asc ;; Record lock, heap no 173 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 8000000000006201; asc b ;; 1: len 6; hex 0000000178e4; asc x ;; 2: len 7; hex 70000002e83313; asc p 3 ;; 3: len 8; hex 8000000000009c77; asc w;; 4: len 11; hex 69676b6d6a695b383a375d; asc igkmji[8:7];; 5: len 5; hex 9992a612fa; asc ;; Record lock, heap no 174 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 800000000000628b; asc b ;; 1: len 6; hex 0000000178e4; asc x ;; 2: len 7; hex 70000002e83342; asc p 3B;; 3: len 8; hex 8000000000009c7f; asc ;; 4: len 11; hex 7664757673635b383a375d; asc vduvsc[8:7];; 5: len 5; hex 9992a612fa; asc ;; Record lock, heap no 176 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 800000000000628a; asc b ;; 1: len 6; hex 0000000178e4; asc x ;; 2: len 7; hex 70000002ec0a80; asc p ;; 3: len 8; hex 800000000000a05f; asc _;; 4: len 11; hex 756a747076775b383a375d; asc ujtpvw[8:7];; 5: len 5; hex 9992a612fa; asc ;; Record lock, heap no 179 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 800000000000622e; asc b.;; 1: len 6; hex 0000000178e4; asc x ;; 2: len 7; hex 70000002ec2173; asc p !s;; 3: len 8; hex 800000000000a447; asc G;; 4: len 11; hex 6375776966665b383a375d; asc cuwiff[8:7];; 5: len 5; hex 9992a612fa; asc ;; Record lock, heap no 181 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 8000000000006220; asc b ;; 1: len 6; hex 0000000178e4; asc x ;; 2: len 7; hex 70000002ec3895; asc p 8 ;; 3: len 8; hex 800000000000a837; asc 7;; 4: len 11; hex 6e6277616e795b383a375d; asc nbwany[8:7];; 5: len 5; hex 9992a612fa; asc ;; Record lock, heap no 183 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 800000000000621d; asc b ;; 1: len 6; hex 0000000178e4; asc x ;; 2: len 7; hex 70000002ef0fd3; asc p ;; 3: len 8; hex 800000000000ac17; asc ;; 4: len 11; hex 7674726f69685b383a375d; asc vtroih[8:7];; 5: len 5; hex 9992a612fa; asc ;; Record lock, heap no 185 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 800000000000626a; asc bj;; 1: len 6; hex 0000000178e4; asc x ;; 2: len 7; hex 70000002ef26c6; asc p & ;; 3: len 8; hex 800000000000afff; asc ;; 4: len 11; hex 6e7078706b685b383a375d; asc npxpkh[8:7];; 5: len 5; hex 9992a612fa; asc ;; Record lock, heap no 190 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 800000000000622a; asc b*;; 1: len 6; hex 0000000178e4; asc x ;; 2: len 7; hex 70000002ef3db9; asc p = ;; 3: len 8; hex 800000000000b3e7; asc ;; 4: len 11; hex 7973686e67715b383a375d; asc yshngq[8:7];; 5: len 5; hex 9992a612fa; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 64 page no 606 n bits 304 index `PRIMARY` of table `test`.`test` trx id 96484 lock_mode X locks rec but not gap waiting Record lock, heap no 234 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 80000000000061e5; asc a ;; 1: len 6; hex 0000000178ef; asc x ;; 2: len 7; hex 77000002e71b60; asc w `;; 3: len 8; hex 800000000000b7cf; asc ;; 4: len 11; hex 63726e6870707b383a357d; asc crnhpp{8:5};; 5: len 5; hex 9992a612fb; asc ;; *** WE ROLL BACK TRANSACTION (1) 知道了这两天sql会死锁,但是不懂为什么呢???
matrix1984 2014-04-19
  • 打赏
  • 举报
回复
引用 2 楼 benluobobo 的回复:
当出现问题的时候执行一下 select * from information_schema.innodb_locks 贴出结果
刚才去看查,没查到东西,但是用:SHOW ENGINE INNODB STATUS 查看,看到了最近造成死锁的两条sql, ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2014-04-19 01:11:59 2144 *** (1) TRANSACTION: TRANSACTION 96495, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 8 lock struct(s), heap size 1248, 137 row lock(s), undo log entries 136 MySQL thread id 37, OS thread handle 0xeb8, query id 872 localhost 127.0.0.1 root updating update test set name=CONCAT(name, '{8:5}'), createTime=NOW() where `key` in (24005,24013,24021,24029,24037,24045,24053,24061,24069,24077,24085,24093,24101,24109,24117,24125,24133,24141,24149,24157,24165,24173,24181,24189,24197,24205,24213,24221,24229,24237,24245,24253,24261,24269,24277,24285,24293,24301,24309,24317,24325,24333,24341,24349,24357,24365,24373,24381,24389,24397,24405,24413,24421,24429,24437,24445,24453,24461,24469,24477,24485,24493,24501,24509,24517,24525,24533,24541,24549,24557,24565,24573,24581,24589,24597,24605,24613,24621,24629,24637,24645,24653,24661,24669,24677,24685,24693,24701,24709,24717,24725,24733,24741,24749,24757,24765,24773,24781,24789,24797,24805,24813,24821,24829,24837,24845,24853,24861,24869,24877,24885,24893,24901,24909,24917,24925,24933,24941,24949,24957,24965,24973,24981,24989,24997,25005,25013,25021,25029,25037,25045,25053,25061,25069,25077,25085,25093,25101,25109,25117,25125,25133,251 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 64 page no 447 n bits 256 index `PRIMARY` of table `test`.`test` trx id 96495 lock_mode X locks rec but not gap waiting Record lock, heap no 171 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 8000000000006205; asc b ;; 1: len 6; hex 0000000178e4; asc x ;; 2: len 7; hex 70000002e81c20; asc p ;; 3: len 8; hex 800000000000988f; asc ;; 4: len 11; hex 6b7676676e795b383a375d; asc kvvgny[8:7];; 5: len 5; hex 9992a612fa; asc ;; *** (2) TRANSACTION: TRANSACTION 96484, ACTIVE 1 sec starting index read mysql tables in use 1, locked 1 323 lock struct(s), heap size 47544, 8686 row lock(s), undo log entries 2881 MySQL thread id 42, OS thread handle 0x2144, query id 865 localhost 127.0.0.1 root updating update test set name=CONCAT(name, '[8:7]'), createTime=NOW() where id in (24007,24015,24023,24031,24039,24047,24055,24063,24071,24079,24087,24095,24103,24111,24119,24127,24135,24143,24151,24159,24167,24175,24183,24191,24199,24207,24215,24223,24231,24239,24247,24255,24263,24271,24279,24287,24295,24303,24311,24319,24327,24335,24343,24351,24359,24367,24375,24383,24391,24399,24407,24415,24423,24431,24439,24447,24455,24463,24471,24479,24487,24495,24503,24511,24519,24527,24535,24543,24551,24559,24567,24575,24583,24591,24599,24607,24615,24623,24631,24639,24647,24655,24663,24671,24679,24687,24695,24703,24711,24719,24727,24735,24743,24751,24759,24767,24775,24783,24791,24799,24807,24815,24823,24831,24839,24847,24855,24863,24871,24879,24887,24895,24903,24911,24919,24927,24935,24943,24951,24959,24967,24975,24983,24991,24999,25007,25015,25023,25031,25039,25047,25055,25063,25071,25079,25087,25095,25103,25111,25119,25127,25135,25143 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 64 page no 447 n bits 400 index `PRIMARY` of table `test`.`test` trx id 96484 lock_mode X locks rec but not gap Record lock, heap no 17 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 8000000000006202; asc b ;; 1: len 6; hex 0000000178e4; asc x ;; 2: len 7; hex 70000002ca1dc7; asc p ;; 3: len 8; hex 8000000000006d97; asc m ;; 4: len 11; hex 76636d6474785b383a375d; asc vcmdtx[8:7];; 5: len 5; hex 9992a612fa; asc ;; Record lock, heap no 28 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 800000000000620d; asc b ;; 1: len 6; hex 0000000178e4; asc x ;; 2: len 7; hex 70000002d3231a; asc p # ;; 3: len 8; hex 800000000000794f; asc yO;; 4: len 11; hex 6c7167726e6c5b383a375d; asc lqgrnl[8:7];; 5: len 5; hex 9992a612fa; asc ;; Record lock, heap no 52 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 8000000000006226; asc b&;; 1: len 6; hex 0000000178e4; asc x ;; 2: len 7; hex 70000002db3f60; asc p ?`;; 3: len 8; hex 80000000000088ef; asc ;; 4: len 11; hex 706770646f645b383a375d; asc pgpdod[8:7];; 5: len 5; hex 9992a612fa; asc ;; Record lock, heap no 55 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 8000000000006229; asc b);; 1: len 6; hex 0000000178e4; asc x ;; 2: len 7; hex 70000002ca0703; asc p ;; 3: len 8; hex 80000000000069b7; asc i ;; 4: len 11; hex 6d756d656d715b383a375d; asc mumemq[8:7];; 5: len 5; hex 9992a612fa; asc ;; Record lock, heap no 71 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 8000000000006239; asc b9;; 1: len 6; hex 0000000178e4; asc x ;; 2: len 7; hex 70000002d30c27; asc p ';; 3: len 8; hex 8000000000007567; asc ug;; 4: len 11; hex 776779616f775b383a375d; asc wgyaow[8:7];; 5: len 5; hex 9992a612fa; asc ;; Record lock, heap no 83 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 8000000000006245; asc bE;; 1: len 6; hex 0000000178e4; asc x ;; 2: len 7; hex 70000002d33a3c; asc p :<;; 3: len 8; hex 8000000000007d3f; asc }?;; 4: len 11; hex 62676c6568695b383a375d; asc bglehi[8:7];; 5: len 5; hex 9992a612fa; asc ;; Record lock, heap no 114 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 8000000000006264; asc bd;; 1: len 6; hex 0000000178e4; asc x ;; 2: len 7; hex 70000002db117a; asc p z;; 3: len 8; hex 800000000000811f; asc ;; 4: len 11; hex 6b646279686e5b383a375d; asc kdbyhn[8:7];; 5: len 5; hex 9992a612fa; asc ;; Record lock, heap no 116 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 8000000000006266; asc bf;; 1: len 6; hex 0000000178e4; asc x ;; 2: len 7; hex 70000002db286d; asc p (m;; 3: len 8; hex 8000000000008507; asc ;; 4: len 11; hex 6f63777264695b383a375d; asc ocwrdi[8:7];; 5: len 5; hex 9992a612fa; asc ;;
matrix1984 2014-04-19
  • 打赏
  • 举报
回复
引用 1 楼 ACMAIN_CHM 的回复:
UpdateNameTask(8, 0); 中的具体代码是什么?产生的SQL语句是什么? 这个您不是打算让大家来猜吧。

	class UpdateNameTask implements Runnable {
		private int mod;
		private int remainder;
		public UpdateNameTask(int mod, int remainder) {
			this.mod = mod;
			this.remainder = remainder;
		}
		public void run() {
			System.out.println("Thread start..." + Thread.currentThread().getName() + ", " + mod + ":" +remainder);
			try {
				TestUtil.updateTestData(mod, remainder);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

public static void updateTestData(int mod, int remainder) throws Exception {
		String selectSql = "select count(*) from test where MOD(id, %s)=%s";
		String selectIdSql = "select id, `key` from test where MOD(id, %s)=%s limit %s, %s";
		String updateSql = "update test set name=CONCAT(name, '%s'), createTime=NOW() where id in (%s)";
		String updateSql2 = "update test set name=CONCAT(name, '%s'), createTime=NOW() where `key` in (%s)";
		Connection conn = TestUtil.getDirectTestConntion();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		int count = 0;
		try {
			selectSql = String.format(selectSql, mod, remainder);
			System.out.println(String.format("[%s]count sql: %s", mod+":"+remainder, selectSql));
			stmt = conn.prepareStatement(selectSql);
			rs = stmt.executeQuery();
			if(rs.next()) {
				count = rs.getInt(1);
			}
			if(count == 0) {
				System.out.println(String.format("[%s]not record found!", mod+":"+remainder));
				return;
			}

			int updateBatchSize = 3000;
			int rmd = count % updateBatchSize;
			int updateTime = (rmd == 0 ? count/updateBatchSize : count/updateBatchSize + 1);
			
			int startIndex = 0;
			for(int i=0;i<updateTime;i++) {
				System.out.println(String.format("[%s]Process begin: startIndex-%s", mod+":"+remainder, startIndex));
				String _selectIdSql = String.format(selectIdSql, mod, remainder, startIndex, updateBatchSize);
				System.out.println(String.format("[%s]select id sql: %s", mod+":"+remainder, _selectIdSql));
				stmt = conn.prepareStatement(_selectIdSql);
				rs = stmt.executeQuery();
				StringBuilder ids = new StringBuilder();
				StringBuilder keys = new StringBuilder();
				while(rs.next()) {
					ids.append(rs.getLong(1)).append(",");
					keys.append(rs.getLong(2)).append(",");
				}
				String _ids = ids.toString();
				if(_ids.indexOf(",") > -1) {
					_ids = ids.substring(0, _ids.lastIndexOf(","));
				}
				String _keys = keys.toString();
				if(_keys.indexOf(",") > -1) {
					_keys = ids.substring(0, _keys.lastIndexOf(","));
				}
				
				// 先根据id更新(id是具有非唯一性索引)
				System.out.println(String.format("[%s]start to update by ids: %s", mod+":"+remainder, _ids));
				String _updateSql = String.format(updateSql, "["+mod+":"+remainder+"]", _ids);
				System.out.println(String.format("[%s]update sql: %s", mod+":"+remainder, _updateSql));
				stmt = conn.prepareStatement(_updateSql);
				stmt.execute();
				System.out.println(String.format("[%s]update by ids finished!", mod+":"+remainder));
				
				// 在根据key更新(key是主键)
				System.out.println(String.format("[%s]start to update by keys: %s", mod+":"+remainder, _keys));
				String _updateSql2 = String.format(updateSql2, "{"+mod+":"+remainder+"}", _keys);
				System.out.println(String.format("[%s]update sql2: %s", mod+":"+remainder, _updateSql2));
				stmt = conn.prepareStatement(_updateSql2);
				stmt.execute();
				System.out.println(String.format("[%s]update by keys finished!", mod+":"+remainder));
				
				startIndex = startIndex + updateBatchSize;
			}
			
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			if(conn != null)
				conn.close();
		}
	}
代码逻辑简单,好多是用来打日志的。。。
benluobo 2014-04-19
  • 打赏
  • 举报
回复
当出现问题的时候执行一下 select * from information_schema.innodb_locks 贴出结果
ACMAIN_CHM 2014-04-19
  • 打赏
  • 举报
回复
UpdateNameTask(8, 0); 中的具体代码是什么?产生的SQL语句是什么? 这个您不是打算让大家来猜吧。
benluobo 2014-04-19
  • 打赏
  • 举报
回复
update test set name='new name' where id in (XXX); 这里的XXX表示,根据不同线程取得的id,对于线程t1,就是0, 8, 16, ...,表示id对8取模余数为0的所有记录的id 假设还有一个id是1,9,17 你这里怎么保证id是0,8,16 对应的主键 不会跟 1,9,17对应的主键重复???
matrix1984 2014-04-19
  • 打赏
  • 举报
回复
引用 11 楼 benluobobo 的回复:
这里,主键索引上的对应记录,是不会被其它线程读到的,因为我们的程序已经保证每个线程读的记录是互不相同的。 你怎么保证? 你读的id字段虽然线程之间是不同,但是这些id对应的key 能保证不同么? 唯一性索引,除了会X锁住非唯一性索引上满足条件的记录及GAP锁,同时会锁住主键索引上的对应记录 这解释的很清楚啊
“你读的id字段虽然线程之间是不同,但是这些id对应的key 能保证不同么?” key是主键,每条记录的key都是唯一的呀。所以不同线程间的key肯定不会相同。Confusing...
benluobo 2014-04-19
  • 打赏
  • 举报
回复
这里,主键索引上的对应记录,是不会被其它线程读到的,因为我们的程序已经保证每个线程读的记录是互不相同的。 你怎么保证? 你读的id字段虽然线程之间是不同,但是这些id对应的key 能保证不同么? 唯一性索引,除了会X锁住非唯一性索引上满足条件的记录及GAP锁,同时会锁住主键索引上的对应记录 这解释的很清楚啊
matrix1984 2014-04-19
  • 打赏
  • 举报
回复
引用 9 楼 benluobobo 的回复:
从日志可以很简单的看出 第一个事务在执行update where key的时候已经锁定了space id 64 page no 606 n bits 304 index `PRIMARY` of table `test`.`test 而这个索引行是第二个事务在执行update where id的时候需要锁定的行 第二个事务在执行的时候已经锁定了space id 64 page no 447 n bits 400 index `PRIMARY` of table `test`.`test` 而这个索引行正是第一个事务现在需要的 真正的原因是由于id是非唯一索引,在锁定的时候不能做到record lock,必须将对应的primary key lock 你可以试着将id改成unqiue key 再试试看
分析的很好,按照博客“http://hedengcheng.com/?p=771#_Toc374698311”中“组合七”说的,非唯一性索引,除了会X锁住非唯一性索引上满足条件的记录及GAP锁,同时会锁住主键索引上的对应记录;这里,主键索引上的对应记录,是不会被其它线程读到的,因为我们的程序已经保证每个线程读的记录是互不相同的。 “真正的原因是由于id是非唯一索引,在锁定的时候不能做到record lock,必须将对应的primary key lock”,这句话能再详细解释一下吗??特别是“必须将对应的primary key lock”。谢谢。

56,675

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧