Jump to content

Single row lock


Dathremar

Recommended Posts

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

 

 

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.