I am often writing datascrubs that update millions of rows of data. The data resides in a 24x7x365 OLTP MySQL database using InnoDB. The updates may scrub every row of the table (in which the DB ends up acquiring a table-level lock) or may just be scrubbing 10% of the rows in a table (which could still be in the millions). 

To avoid creating massive transaction sizes and minimize contention I usually end up trying to break up my one massive UPDATE statement into a series of smaller UPDATE transactions. So I end up writing a looping construct which restricts my UPDATE's WHERE clause like this:

(warning: this is just pseudo-code to get the point across)

```
@batch_size=10000;
@max_primary_key_value = select max(pk) from table1

for (int i=0; i<=@max_primary_key_value; i=i+@batch_size)
{
start transaction;

update IGNORE table1
set col2 = "flag set"
where col2 = "flag not set"
and pk > i
and pk < i+@batchsize;

commit;
}

```

This approach just plain sucks for so many reasons.

I would like to issue an UPDATE statement without the database trying to group all of the records being updated into a single transaction unit. I don't want the UPDATE to succeed or fail as a single unit of work. If 1/2 the rows fail to update... no problem, just let me know. Essentially, each row is it's own unit of work, but batching or cursoring is the only way I can figure out how to represent that to the database engine.

I looked at setting isolation levels for my session, but that doesn't appear to help me in this specific case.

Any other ideas out there?