[RESOLVED] Lock a row in MySQL

How do I lock a row in MySQL and then only allow the app that locked it to change the values of that row?

You can use logical locking;

Create a Locking Table which can store 4 columns, the app, table, rowID (Primarykey) and status, i.e. "locked" or "unlocked")

Then when you want to lock it add a row to this table. When you wish to use the same table in an other app, you first check this table, if it is not locked, then continue, otherwise procede as you would upon finding a locked row.

That is a very good idea, but I'm working with massive amounts of data here. To first check the table, then insert the rows to the table will be one step to many. I'm loading about 50 000 + rows at a time, so this takes a while.

What would be the fastest, your approach or the row-lock function in SQL?

We've been using logical locking because we can retain control over the locking, plus other issues that I can't recall that we don't have to worry about if we manage locking ourselves. In short we don't use row-lock function in SQL, so you're on your own there.

Why would you be editing 50,000 rows at a time, locking that many is extremely inefficient. If you are just displaying data, there is no need to lock it. You only want to lock a record that is being edited. I don't know ANYONE who edits 50,000 records at a time. If you are editing them from code, you can lock the whole table. Something doesn't make sense here.

The company I work for wants it this way for some reason. When an operator loads everything to do with wood or metal or whatever, without any further filters entered. There might at one time be more than 50 000 items. They want the operator to be able to amend any of the rows on his/her screen without conflict.

An example would be something like this. In each row you get a file/column "Profit Margin". Now what happens is lets say we run a special on all wood products, they would load all those onto the screen and enter the new value into a textbox and click update. (we dont have a discount field for some reason)

Now while this is busy updating, we dont want anyone else to have access to even view these rows.

Does this explain my situation better?
The "logical locking" option will work perfectly. I'm just wondering if there isn't a faster way to do this.

Yes, much better explanation. In that case you can lock the whole table. The Row locking is mainly for when someone is editing a record on the screen, you never know when they will be done, they could go to lunch while still editing, so you just tie up a single row. You're talking about using an UPDATE statement from code, right? You can lock the whole table because you're doing the update from code and it will finish relatively quickly without any user intervention.

1. Ensure there are no outstanding locks on the table, if not
2. Lock entire table
3. Perform UPDATE
4. Unlock table

Am I following you?

Yes, the only problem is that if i lock the whole table, none of the other products will be available. All stock is in one table. But I think your Logical Locking is the key. They will just have to be patient if it takes a while. Thanks

You're welcome. You can copy the table and try a few practice updates. It shouldn't take too long, maybe not even more than a minute.

And all the products will be available for reading, just not updating.