ansaurus

Question

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

Answer 1

A:

(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.

Andomar 2009-11-16 11:32:01

Thanks a lot for you Answer. I also want to let you know that I don't execute this query in transaction. I execute this query without beginning any transaction, even then is it possible that the select query acquire locks on the table ??

Anees 2009-11-16 12:55:15

Every query runs in a transaction. If no transaction is found, a new one is started. By default, MySQL runs with autocommit mode enabled, which means it will commit (close) the transaction after running the statement. See http://dev.mysql.com/doc/refman/5.0/en/commit.html

Andomar 2009-11-16 13:05:51

I have tried the solution given by you. But now unfortunately all update statements go into halt statement and our application becomes unable to update any row in the table.

Anees 2009-11-16 14:03:54

@Anees: Perhaps add the SELECT query to your post. Also read MarkR's answer, he sounds like a more seasoned MySQL admin (I mostly know MySQL from the dev side)

Andomar 2009-11-16 14:42:17

Answer 2

+1 A:

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?

MarkR 2009-11-16 13:10:42

+1 Quite right on repeatable read

Andomar 2009-11-16 13:39:09

Mark,The transaction isolation level is REPEATABLE READ and i am not doing any full table scan. This query runs on at most 100,000 rows.

Anees 2009-11-16 15:26:36

I have added the Query in my Question. Please have a look over it

Anees 2009-11-17 10:50:51

I asked if it was doing a full table scan - you can find this out by doing an EXPLAIN. On the other hand, if it's using 100k rows it's probably almost as bad as a full table scan (of 4M rows). Doing a range scan on 100k entries of a secondary index will not be cheap.

MarkR 2009-11-17 11:27:47

the EXPLAIN shows that all fields in where clauses are index based and getting appropriate hits. Should I assume that MySQL InnoDB Engine will lock the table and block all insert/update operations, if there exists a heavy select query as in my case.

Anees 2009-11-17 14:07:44

ansaurus

tags:

views:

answers:

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

related questions