Dathremar Posted July 6, 2011 Share Posted July 6, 2011 Hello all, I was wandering if it is possible to lock just a single table row on a MySql DB with InnoDB? What I want to do, is lock manually one row in a table while I read from other table and then release the lock so the row can be updated from another point. Many Thanks Quote Link to comment Share on other sites More sharing options...
Zane Posted July 6, 2011 Share Posted July 6, 2011 Can you elaborate more on this "lock" and what InnoDB has to do with it? Have you completed this "lock" with MyISAM? What is your overall goal... other than this one-liner What I want to do, is lock manually one row in a table while I read from other table and then release the lock so the row can be updated from another point. Quote Link to comment Share on other sites More sharing options...
gizmola Posted July 6, 2011 Share Posted July 6, 2011 Yes it's very simple, you issue SELECT... FOR UPDATE. You have to wrap this with a transaction. When you commit the lock(s) will be released. However, the "other" point should really do whatever work is required inside the transaction, or the locking would be fairly pointless. Quote Link to comment Share on other sites More sharing options...
Dathremar Posted July 6, 2011 Author Share Posted July 6, 2011 Sorry about not the explicit question, because I was thinking about it at the time I *thought* it was very clear :S Let me try to elaborate more. The problem is that I want to have one table for signals, if another table had changes. This table will be updated from two scripts. One that will update when there is a change and another script which will update when the changes are read. So what I want to do is to make sure the signal table does not get updated again while I am reading with the second script from the data table. That's why I want to lock the signal table, but only one row in it (so other rows can get updated). I pointed out InnoDb, because that's the table type for both tables. Maybe it doesn't make much difference in my case, but I know that MySql uses different locking mechanisms for different types, so I just wanted to let You guys know that the table is InnoDB. Quote Link to comment Share on other sites More sharing options...
Dathremar Posted July 6, 2011 Author Share Posted July 6, 2011 I will look into the SELECT ... FOR UPDATE thing. It looks like it might help me. If that is the case I will close the thread. Thanks gizmola. Quote Link to comment Share on other sites More sharing options...
gizmola Posted July 6, 2011 Share Posted July 6, 2011 Well, it's relevant because innodb has row level locking whereas myisam does not. I'm not sure I understand your design. You can not have one process that locks a row, and then have another process that comes along and unlocks it. Quote Link to comment Share on other sites More sharing options...
Dathremar Posted July 6, 2011 Author Share Posted July 6, 2011 Yeah the SELECT ... FOR UPDATE is used for different purpose. I guess I need to do application level synchronization for this. Thanks for the effort gizmola Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.