MySQL Forums :: InnoDB :: Minimize row locking for update statement


Advanced Search

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


Subject Views Written By Posted
Minimize row locking for update statement 2266 gaetano giunta 02/14/2012 11:17PM
Re: Minimize row locking for update statement 938 Rick James 02/15/2012 11:12PM
Re: Minimize row locking for update statement 815 gaetano giunta 02/17/2012 06:21AM
Re: Minimize row locking for update statement 773 irek kordirko 02/17/2012 04:13PM
Re: Minimize row locking for update statement 862 gaetano giunta 02/18/2012 02:39AM
Re: Minimize row locking for update statement 3430 irek kordirko 02/18/2012 12:56PM
Re: Minimize row locking for update statement 1299 Aftab Khan 02/21/2012 04:56AM


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.