Andre, ----- Original Message ----- From: "Andre Charbonneau" <[EMAIL PROTECTED]> 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. > -- > Andre Charbonneau Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
