Results 1 to 8 of 8

Thread: [RESOLVED] Lock a row in MySQL

  1. #1

    Thread Starter
    Addicted Member tgf-47's Avatar
    Join Date
    Feb 2010
    Location
    CapeTown, South Africa -34.01244,18.337415
    Posts
    209

    Resolved [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?

  2. #2
    Frenzied Member
    Join Date
    Jan 2010
    Location
    Connecticut
    Posts
    1,687

    Re: Lock a row in MySQL

    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.
    VB6 Library

    If I helped you then please help me and rate my post!
    If you solved your problem, then please mark the post resolved

  3. #3

    Thread Starter
    Addicted Member tgf-47's Avatar
    Join Date
    Feb 2010
    Location
    CapeTown, South Africa -34.01244,18.337415
    Posts
    209

    Re: Lock a row in MySQL

    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?

  4. #4
    Frenzied Member
    Join Date
    Jan 2010
    Location
    Connecticut
    Posts
    1,687

    Re: Lock a row in MySQL

    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.
    VB6 Library

    If I helped you then please help me and rate my post!
    If you solved your problem, then please mark the post resolved

  5. #5

    Thread Starter
    Addicted Member tgf-47's Avatar
    Join Date
    Feb 2010
    Location
    CapeTown, South Africa -34.01244,18.337415
    Posts
    209

    Re: Lock a row in MySQL

    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.

  6. #6
    Frenzied Member
    Join Date
    Jan 2010
    Location
    Connecticut
    Posts
    1,687

    Re: Lock a row in MySQL

    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?
    VB6 Library

    If I helped you then please help me and rate my post!
    If you solved your problem, then please mark the post resolved

  7. #7

    Thread Starter
    Addicted Member tgf-47's Avatar
    Join Date
    Feb 2010
    Location
    CapeTown, South Africa -34.01244,18.337415
    Posts
    209

    Re: Lock a row in MySQL

    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

  8. #8
    Frenzied Member
    Join Date
    Jan 2010
    Location
    Connecticut
    Posts
    1,687

    Re: [RESOLVED] Lock a row in MySQL

    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.
    VB6 Library

    If I helped you then please help me and rate my post!
    If you solved your problem, then please mark the post resolved

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width