Re: Innodb row locking question - Benjamin Pflugmann - com.mysql.lists.mysql

5 messages in com.mysql.lists.mysqlRe: Innodb row locking question

From	Sent On	Attachments
Mike Gohlke	06 Dec 2002 08:28
Benjamin Pflugmann	07 Dec 2002 06:49
Mike Gohlke	07 Dec 2002 08:41
Benjamin Pflugmann	07 Dec 2002 09:52
Heikki Tuuri	08 Dec 2002 05:02

Subject:	Re: Innodb row locking question
From:	Benjamin Pflugmann (benj...@pflugmann.de)
Date:	12/07/2002 06:49:03 AM
List:	com.mysql.lists.mysql

Hello.

On Fri 2002-12-06 at 10:28:23 -0600, mi...@rcs-net.net wrote:

Heikki and all, I've got a quick question which may be more general sql related but since I'm using innodb tables specifically for the row locking.

The following process description is specifically designed to prevent duplicates.

My current process: select * from run where job_id = 111 and thread_id = 0 limit 10000 for update;

update run set thread_id = 999 where job_id = 111 and thread_id = 0 and ( rec_id = x or rec_id = x1 ... ); // rec_id pulled via loop of previous select

Why do you repeat "job_id = 111 and thread_id = 0"? If you are using a transaction seperation level of at least REPEATABLE READ (which is the default), InnoDb assures that you always see the same rows within one transaction. Regardless, FOR UPDATE locks the records, which assures the values did not change meanwhile.

Btw, rec_id IN (x1, x2, x3) may provide better performance (OR clauses are still problematic with MySQL v3.23)

select main.* from run,main where run.job_id = 111 and run.thread_id = 999 and run.rec_id = main.rec_id; // this is saved off

delete from run where job_id = 111 and thread_id = 999;

I am irritated. Above you select only the 10.000 first rows and process them, but at the end, you delete all, not only max 10.000?

---- end ---- As you can see, it's quite a few queries to prevent collisions. Right now the update and delete take the longest for obvious reasons.

What I'm wondering is if I can do the following:

select main.* from run,main where run.job_id = 111 and run.thread_id = 0 and run.rec_id = main.rec_id limit 10000 for update;

delete from run where job_id = 111 and (rec_id = x or rec_id = x2 ...);

Why not? FOR UPDATE is explained on http://www.mysql.com/doc/en/SELECT.html as "If you are using FOR UPDATE on a storage engine with page/row locks, the examined rows will be write locked."

Although the name sounds otherweise, FOR UPDATE is not only suited for updates. IMHO, the write lock should work for the DELETE as for the UPDATE. Or in other words: it is either good enough for both of your variants or for neither.

The only thing you should consider is the fact that the second variant will lock records from table "main", which the first did not.

HTH,

Benjamin.