Select Query Blocks read/write/update operations on transactional table

I have a Select query which executes on a transactional table having more than 4 million records. Whenever I execute this query , I observe that all write and update operations on that particular transactional table become suspended and we start getting exceptions from java side that lock wait timeout exceeds , try restarting transaction. Currently lock wait timeout is set to 200 seconds. I am unable to understand that why a select statement can create such locks on the table and block all insert/update statements. The table storage engine is InnoDb and primary key is auto-increment key. The MySQL Version is 5.1.40. I am not beginning any transaction while executing this query

Any Idea?

Here is the Query

SELECT cd.acc_id accId, cast(cd.ci_time as date) trdate, coalesce(cd.cnumber, replace(cd.executer_id, '+', '')) as cno, CASE WHEN coalesce(cd.language, 'English') = 'English' THEN 1 ELSE 2 END As language, CASE WHEN cd.cnumber is null THEN 'N' ELSE 'Y' END iscno, replace(cd.executer_id, '+', '') executer_id, count(*) trcount, Sum(coalesce ( ( SELECT count(DISTINCT distribution_log.dist_id) FROM distribution_log, distribution_log_detail WHERE distribution_log.distribution_log_id = distribution_log_detail.distribution_log_id AND distribution_log_detail.service_id not in ('P1', 'P3') and distribution_log.state_id = 'Register' AND distribution_log.dist_id = cd.dist_id ) , 0 ) ) accAbandonedduring, Sum( CASE WHEN coalesce ( ( SELECT count(DISTINCT distribution_log.dist_id) FROM distribution_log WHERE distribution_log.acc_id = 58 AND distribution_log.dist_id = cd.dist_id and distribution_log.state_id = 'Register' ) ,0 )

0 THEN 0 ELSE 1 END ) accAbandonedbef, Sum(coalesce ( ( SELECT COUNT(*) FROM cq_detail cqd WHERE cqd.dist_id = cd.dist_id ) ,0 ) ) AS opted_for_csr, Sum(coalesce ( ( SELECT count(DISTINCT cqd.dist_id) FROM cq_detail cqd, ca_detail cad WHERE cqd.dist_id = cd.dist_id AND cad.dist_id = cd.dist_id GROUP BY cqd.dist_id HAVING SUM(cad.agent_answered_flag) > 0 ) ,0 ) ) AS csr_trs_ans, Sum(coalesce ( ( SELECT count(DISTINCT cqd.dist_id) FROM cq_detail cqd WHERE cqd.dist_id = cd.dist_id AND FAnswer(cqd.dist_id) = 0 AND time_to_sec(timediff(cqd.cq_end_time, cqd.cq_init_time)) < 60 ) ,0 ) ) AS abon_at_csr_und, Sum(coalesce ( ( SELECT 开发者_开发问答 count(DISTINCT cqd.dist_id) FROM cq_detail cqd WHERE cqd.dist_id = cd.dist_id AND FAnswer(cqd.dist_id) = 0 AND time_to_sec(timediff(cqd.cq_end_time, cqd.cq_init_time)) >= 60 ) ,0 ) ) AS abon_at_csr_abv, Sum(coalesce ( CASE WHEN ( SELECT count(DISTINCT distribution_log.dist_id) FROM distribution_log, distribution_log_detail WHERE distribution_log.distribution_log_id = distribution_log_detail.distribution_log_id AND distribution_log_detail.service_id = 'P1' and distribution_log_detail.resp_code = '00' AND distribution_log.dist_id = cd.dist_id ) 0 THEN 1 END , 0 ) ) AS acc_successful, Sum(coalesce ( CASE WHEN ( SELECT count(DISTINCT distribution_log.dist_id) FROM distribution_log, distribution_log_detail WHERE distribution_log.distribution_log_id = distribution_log_detail.distribution_log_id AND distribution_log_detail.service_id = 'P1' and distribution_log_detail.resp_code <> '00' AND distribution_log.dist_id = cd.dist_id ) 0 THEN 1 END , 0 ) ) AS acc_unsuccessful FROM tr_detail cd WHERE cd.acc_id = 58 AND cd.ci_time >= '2009/11/05' AND cd.ci_time < Cast('2009/11/05' as date)+1 GROUP BY 1,2,3 limit 1;

A SELECT statement will not create locks if your transaction isolation level is REPEATABLE READ or lower, unless you use "FOR UPDATE".

Check what your txn isolation level is.

There is no need to use READ UNCOMMITTED, or even READ COMMITTED. Just avoid SERIALIZABLE.

On the other hand, a select statement will still use up resources which could impact the behaviour of the machine.

Are you doing a full table scan on the 4M row table?

(Edited after rereading based on MarkR's answer)

The online help text for InnoDb transactions is quite good. The default isolation level is repeatable read. Per MarkR's answer, a SELECT at the default isolation level will not issue row locks, and will not block updates or inserts while its transaction is open.

If you were running at serializable (the highest isolation level) you can can fall back to the default with:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

This changes the isolation level for the current session only.