Minimize row locking for update statement
I have the following scenario: a table can contain a few millions records, of which a couple ten-thousands I am interested in. There are going to be many transactions running at the same time, and I want to minimze the risk of getting locks and deadlocks.
The query I am running is:
update ezdfsfile set expired=1, mtime=-abs(mtime) where name_trunk = 'var/etc...'
Some of the rows in the table matching the given name_trunk, even most of them, might have already an expired=1 and a negative mtime (i.e. they're already expired)
The interesting thing is that when, after running the query, I ask mysql for the number of rows updated, the count does not include the ones that where already expired. I might have 1000 rows matching the given name_trunk and get count of affected rows = 0.
BUT, if I have another transaction going on that locks some of those existing, expired rows, my update goes into wait mode until those locks are gone.
My goal would be to have 2 transactions running in parallel (ie not waiting for each other to release locks) on the rows in the table which have the same name_trunk: one affecting expired rows, one affecting unexpired rows.
So far I have tried the following:
1. add an index on the table, on columns name_trunk+expired (non-unique, as name_trunk is not)
2. alter the update query's where condition: update ezdfsfile set expired=1, mtime=-abs(mtime) where name_trunk = 'var/etc...' and expired != 1
3. lower session isolation level to read_committed
But still, if I run a
select * from ezdfsfile where name_trunk = 'var/etc...' and expired = 1 for update
it will prevent the update from executing - it looks like the update statement wants to acquire a lock on all rows in the table.
Any suggestion is welcome
ps: running on mysql 5.1 from debian