does mysql_query in php block concurrently queries?

Question

I have a SugarCRM installation.

My problem is when I do a search in sugarcrm the search query block all other queries.

Id      User         Host                   db              Command Time    State   Info
49498   sugar        xx.xx.xx.xx:59568      sugarcrm        Query   5       Sorting result  SELECT leads.id  ,leads_cstm.cedula_c,leads_cstm.numplanilla_c,leads_cstm.profession_c,leads_cstm.b
49502   sugar        xx.xx.xx.xx:59593      sugarcrm        Sleep   5               NULL

As you can see the query Id 49502 is, I presume, waiting for query 49498 to finalize.

first query is a search query which last a looong time to execute second query is a query for the index page

The odd thing is that if I open two terminals and connect to mysql using the same user as my sugarcrm installation I can execute both queries concurrently but if I make a search in the browser and open a new tab and try to access the index page, that second tab hungs until the first tab completes execution or it get a timeout from server.

I have tested this using php both as a module and as cgi.

So I guess it should be something with the mysql_query function itself?

Any ideas? It's very hard to optimize the db (lots of tables, lots of content) but at least the site should be able to be used concurrently...

You shouldn't be using mysql_query anyway, use mysqli_query. As for performance, I'm not sure what's going on underneath mysql_query, but clients do have the option of putting a lock on a table while it does its work. Time to read some source code. Are you sure the CRM software isn't putting any locks on the tables? — Zarathuztra
– Zarathuztra, Commented Feb 6, 2014 at 23:05

Marc B · Accepted Answer · 2014-02-06 23:03:55Z

6

Probably because you're using file-based sessions. PHP will lock session files while they're in used, which essentially makes all requests by a particular session to become serial.

The only ways around this are to either use session_write_close() to release the session lock on a per-script basis, after any session-changing code is complete, or implementing your own session handler which can have its own locking logic.

answered Feb 6, 2014 at 23:03

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jonathan Kuhn Over a year ago

Thanks. I've never confirmed this but have noticed that long running php requests will block other tabs from accessing the same site while other devices (or even browsers) can connect with no problem. I always assumed this was the problem, just never took the time to look into it.

Cesar Over a year ago

@marc-b You were right, I tested using tow differents browsers and the queris ran concurrently, thanks! Will running db-based sessions avoid the problem with multiple tabs?

Marc B Over a year ago

depends on how you implement the session handler. rolling it yourself means locking is up to you. if you don't do it right, two paralle requests could stomp on each other's data.

Collectives™ on Stack Overflow

does mysql_query in php block concurrently queries?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related