Yes, I'm using indexes, accountid is the primary key, and is numeric and autoincrement. The process doing the deadlock is no longer done...
The structure of the inserted database has changed. Originaly it was a single table with 219millions rows, now I partitioned the hable in... 60 tables, 1 for each month for 5 years.. I gain a lot of speed with the partitioning and sql adjustments, but I think I lost a lot of speed at the insert... The database stats are like 95% reading 5% writting...but that 5% is mostly done in a batch process who happends to be done inside a time window... I know I need to do more profiling, but... at least for now dev team is updating the batch process from long secuencial process with huge slow inserts, to small parallel task with burst of inserts... On Mon, May 14, 2012 at 8:18 AM, Ananda Kumar <anan...@gmail.com> wrote: > is accountid a number or varchar column > > > On Sat, May 12, 2012 at 7:38 PM, Andrés Tello <mr.crip...@gmail.com>wrote: > >> While doning a batch process... >> >> show full processlist show: >> >> | 544 | prod | 90.0.0.51:51262 | tmz2012 | Query | 6 | >> end | update `account` set `balance`= 0.00 + >> '-4000' where accountid='2583092' >> >> No other process, lo locking no nothing... >> >> so you take this same query... run it isolated, and the mu....fu... is >> just...f.... fast!!!!! >> >> >> update `account` set `balance`= 0.00 + '-4000' where accountid='2583092' >> Query OK, 0 rows affected (0.00 sec) >> Rows matched: 1 Changed: 0 Warnings: 0 >> >> >> ARRRG! I have seen this type of query take as long as 100+ seconds.. and >> I >> don't have a F*** clue... >> > >
