Locking issue in innodb select query

[zafar malik]

Hi Rick,

I am getting locking issue even in select queries with innodb engine. Please check below query which normally took 2 seconds but some time it is taking more than 40 seconds and also at this time locking time is also more than normal time. Please help me why this query is taking too much time and specially reason of locking time. As per my understanding there should not be any locking in select query in innodb engine. Please help

Details are here-

# Time: 130711 17:30:21
# User@Host: bitcrm[bitcrm] @ [10.1.32.183]
# Query_time: 40.599935 Lock_time: 0.001275 Rows_sent: 22 Rows_examined: 582273
SET timestamp=1373544021;
SELECT COUNT(smt.SMTNAME) AS countval, smt.SMTNAME AS statusname, SUM(psttab.PSTPAYABLEPRICE) AS priceval FROM ISMPST AS psttab JOIN ISMSMT AS smt ON psttab.PSTSMTSTATUS = smt.SMTRFNUM AND psttab.CREATEDATE >= '2013-04-11' AND (psttab.PSTISCOMBO = 'N' OR psttab.PSTISCOMBO IS NULL) GROUP BY smt.SMTNAME;
-----------------------------------
mysql> EXPLAIN SELECT COUNT(smt.SMTNAME) AS countval, smt.SMTNAME AS statusname, SUM(psttab.PSTPAYABLEPRICE) AS priceval FROM ISMPST AS psttab JOIN ISMSMT AS smt ON psttab.PSTSMTSTATUS = smt.SMTRFNUM AND psttab.CREATEDATE >= '2013-04-11' AND (psttab.PSTISCOMBO = 'N' OR psttab.PSTISCOMBO IS NULL) GROUP BY smt.SMTNAME\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: psttab
type: range
possible_keys: IDX_PSTSMTSTATUS,IDX_CREATEDATE,IDX_PSTISCOMBO
key: IDX_CREATEDATE
key_len: 5
ref: NULL
rows: 244250
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: smt
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: bitshop.psttab.PSTSMTSTATUS
rows: 1
Extra:
2 rows in set (0.00 sec)
------------------------------------------------------------

SELECT COUNT(*) FROM PSST -- 3048426

SELECT COUNT(*) FROM SMMT -- 87
---------------------------

Thanks,
Zafar