Almost everyday, we notice dead lock on the database due to these SQL queries. Locking entire table for single or few rows operation also causing slow running other SQL queries.

Deadlock on profiles table during concurrent executions of DELETE and UPDATE SQL queries due to bad design of SQL queries.

UPDATE profiles SET value_str='20120518211248', type=3 WHERE userid=13 AND idx='web.screens.stime' AND profileid BETWEEN 000000000000000 AND 099999999999999 AND idx2=46 AND idx2 BETWEEN 000000000000000 AND 099999999999999

The following issues with above SQL query design causing always FULL TABLE scan:
profileid (i.e. PK) values range is cover all the table rows - profileid BETWEEN 000000000000000 AND 099999999999999
idx2 non unique index also cover full table scan - idx2 BETWEEN 000000000000000 AND 099999999999999
idx2=46 is filter is invalid due to above idx2 column condition

Dead lock is caused by UPDATE query - all rows lock by by (profileid BETWEEN 000000000000000 AND 099999999999999)
condition.

To avoid dead lock or longer locks on table rows, the modified UPDATE query should be like

UPDATE profiles SET value_str='20120518211248', type=3 WHERE userid=13 AND idx='web.screens.stime' AND idx2=46;

Please fix the SQL query as suggested.

Thanks