Re: row-level locking question... - Heikki Tuuri - com.mysql.lists.mysql

3 messages in com.mysql.lists.mysqlRe: row-level locking question...

From	Sent On	Attachments
Andre Charbonneau	17 Nov 2003 07:10
Jeremy Zawodny	17 Nov 2003 16:00
Heikki Tuuri	19 Nov 2003 12:04

Subject:	Re: row-level locking question...
From:	Heikki Tuuri (Heik...@innodb.com)
Date:	11/19/2003 12:04:42 PM
List:	com.mysql.lists.mysql

Andre,

----- Original Message ----- From: "Andre Charbonneau" <andr...@nrc-cnrc.gc.ca> Newsgroups: mailing.database.myodbc Sent: Monday, November 17, 2003 5:11 PM Subject: row-level locking question...

Hi,

Let say that I have the following transaction:

1. Read value v1 from table t1. 2. Do some computation using v1. 3. Update value v2 from table t2.

If in the above I don't want any other concurrent transaction to read v2 until I'm done updating it, how should I put an exclusive lock on it?

Using InnoDB, would the following be the way to do it (in transaction mode, seriliazable isolation level)?

SELECT v2 from t2 FOR UPDATE; // (Do this to prevent others from reading v2)

SELECT v1 from t1;

(do the computation)

UPDATE t2 set v2=<new value>;

COMMIT;

In the above statements, I first read the value v2 to put an exclusive lock on that row. But I don't really need the value of v2, I just need to lock it down.

note that

UPDATE t2 set v2=<new value>;

automatically sets an x-lock on the row to update. If the above is the whole story about your application logic, you really do not need to do

SELECT v2 from t2 FOR UPDATE;

first. But, to get serializable execution, you NEED to do a locking read

SELECT v1 from t1 LOCK IN SHARE MODE;

to freeze t1 so that v1 cannot change meanwhile!

---

To sum up, the following program does serializable execution:

BEGIN;

SELECT v1 from t1 LOCK IN SHARE MODE;

(do the computation of v2 based on v1)

UPDATE t2 set v2=<new value>;

COMMIT;

Is the above approach the way to go or is there a more elegant/correct way of doing this?

Thanks.

Best regards,