The query in question is an UPDATE mytable SET flag=1 WHERE ID IN(...) statement. I'm assuming that another system was in the middle of a performing a SELECT statement on the flag column.
In this particular case it's perfectly ok for the select statement to get slightly incomplete data (e.g. returning a record with a flag that might have just been changed), so it would be nice if I could configure the table so that select statements never lock. Is it possible to do something like that?
The first stack exchange when I googled was pretty interesting. For completeness, if you use innodb as your engine
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Seems to be the line you are asking for. There's the usual way to make it a system default in the config file. As for the difference between what you are asking to do, and what you should do, well... be careful. Especially if you're feeding the dirty read back into something that could lead to an update. That could be icky.
There's some way to do myisam stuff.
Generally I've found if you've got two things trying to lock one thing, overall processing goes a lot faster if one greedy dude grabs it for the whole thing. Cooperative multitasking doesn't work if it spends way more time lock switching than actually processing. Of course greedy processing is an excellent way to jam things up if you're not careful.
The funniest discovery I ever made about that related to importing zillions of rows with autocommit off it was dramatically faster rather than demanding an I/O store. This was the kind of data where if someone yanked the power cord it would be auto-re-imported without any loss; if your only copy of the data is in RAM and the INSERT you just ran, then autocommit off might be unwise.
Your IN (...) if ... is a select that might none the less be similar to my fun time a couple years back with dueling indexes. This is the origin of the whole never index more than you have to meme because it doesn't just waste storage and write time, an "innocent" looking pathological index intended to make some report run faster can kill read performance if its touches too many things other indexes touch while "separate" processing is happening. If you don't know A leads to B, B leads to C, C leads to D, then why does messing with A kill D performance when D should be totally separate, ah forgot about that peculiar linkage...
Note that none of this is a mysql problem. You can shoot yourself in the foot with a common 9mm or a weirdo caliber. More people get hurt by the 9mm not because it inherently magically hits feet more often but because theres just more people using it. I'm quite sure you can screw up Oracle or DB2 the same way if enough people try hard enough. I guarantee if you google you're going to find more "mysql did something weird" stories than any other DBMS simply because more people use mysql.
In this particular case it's perfectly ok for the select statement to get slightly incomplete data (e.g. returning a record with a flag that might have just been changed), so it would be nice if I could configure the table so that select statements never lock. Is it possible to do something like that?