MySQL Forums
Forum List  »  InnoDB

Minimize row locking for update statement
Posted by: gaetano giunta
Date: February 14, 2012 11:17PM

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

Options: ReplyQuote

Written By
Minimize row locking for update statement
February 14, 2012 11:17PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.