when will select statement without for update causing lock?

Question

I'm using MySQL,

I sometimes saw a select statement whose status is 'locked' by running 'show processlist'

but after testing it on local,I can't reproduce the 'locked' status again.

Answer 1

It probably depends on what else is happening. I'm no mySQL expert but in SQL Server various lock levels control when data can be read and written. For example in production your select stateemnt might want to read a record that is being updated. It has to wait until the update is done. Vice-versa - an update might have to wait for a read to finish.

Messing with default lock levels is dangerous. And since dev environs don't have nearly as much traffic you probasbly don't see that kind of contention.

Answer 2

If you spot that again see if you can see if any update is being made against one of the tables your select is referencing.

I'm no expect in mysql, but it sounds like another user is holding a lock against a table/field while your trying to read it.

Answer 3

I'm no MySQL expert either, but locking behavior strongly depends on the isolation level / transaction isolation. I would suggest searching for those terms in the MySQL docs.