Re: Minimize row locking for update statement
Rick James Wrote:
-------------------------------------------------------
> > There are going to be many transactions running
> at the same time
> Achieve that by making the transactions faster.
Thanks, but this is not really an option. The transactions in use are there for guaranteeing integrity, and the product I am workin on is a cms. End users can hook their own plugins that will run (unknown) code while that transaction is running.
> SHOW CREATE TABLE ezdfsfile
CREATE TABLE ezdfsfile (
`name` text NOT NULL,
name_trunk text NOT NULL,
name_hash varchar(34) NOT NULL DEFAULT '',
datatype varchar(60) NOT NULL DEFAULT 'application/octet-stream',
scope varchar(25) NOT NULL DEFAULT '',
size bigint(20) unsigned NOT NULL DEFAULT '0',
mtime int(11) NOT NULL DEFAULT '0',
expired tinyint(1) NOT NULL DEFAULT '0',
`status` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (name_hash),
KEY ezdfsfile_name (`name`(250)),
KEY ezdfsfile_name_trunk (name_trunk(250)),
KEY ezdfsfile_mtime (mtime),
KEY ezdfsfile_expired_name (expired,`name`(250))
) ENGINE=InnoDB;
> > even most of them
> So, add a where clause to avoid setting the ones
> that don't need changing.
This is what I am experimenting with. But it seems it does not help a lot.
Worst case scenario: I get more locking.
Best scanerio: after adding an index on the "expired" column, I get the same as I did before changing the "where" clause.
> [...]
>
> > one affecting expired rows, one affecting
> unexpired rows.
> One transaction would be working on some of the
> rows; the other transaction would be working on
> all the other rows (for the given name_trunk)?
>
> SHOW ENGINE INNODB STATUS; -- Look for "gap"
> locking.
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)