Re: Minimize row locking for update statement

[gaetano giunta]

irek kordirko Wrote:
-------------------------------------------------------
> gaetano giunta Wrote:
> --------------------------------------------------
> -----
> > This is what I am currently looking at.
> Enabling
> > the lock monitor, I tested the simple query "
> > update ezdfsfile set expired=1, mtime=-mtime
> where
> > name_trunk =
> >
> 'var/ezflow_site/cache/content/ezflow_site/1/186-'
>
> > and expired = 1", and I see there are 4 locks
> > held:
> > - table
> > - pk
> > - index "ezdfsfile_name_trunk" (record)
> > - index "ezdfsfile_name_trunk" (gap before rec)
>
> Could you be more precise ?
> What we know is that you run the above query in
> the session 1.
> What was the isolation level in this session 1 ?

The standard one

> In the same time you run the other query in the
> session 2.
> What query exactly did you run ? Was it 'SELECT
> ... FOR UPDATE' ?

Yes - not sure the s"elect for update" is what I'm gonna run in production, but I'm using it to try "simulating" the kind of concurrent operation that might happen...

> Or something else ? And What was
> the isolation level in the session 2 ?

Again, the default one.

As an update: I think I have found a good solution to my needs:
- use a (non unique) key on cols name_trunk+expired
- in my query for updating rows, use always both name_trunk and expired cols in the where condition. Important: do not use "expired != 1", use "expired = 0"
- remove exisiting indexes on single cols name_trunk and expired

With this, all the queries I tested run without locking one another.
(I had in fact tried lowering transaction isolation level to get rid of the locks, but even with binlog_unsafe_for_replication I did not get improvements)