CODEWITHSUNDEEP
mysqlselectlocking
omg
omg

Reputation: 139832

when will select statement without for update causing lock?

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.

Upvotes: 0

Views: 284

Answers (3)

rudolfson
rudolfson

Reputation: 4146

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.

Upvotes: 0

kevchadders
kevchadders

Reputation: 8335

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.

Upvotes: 1

n8wrl
n8wrl

Reputation: 19765

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.

Upvotes: 1

Related Questions