SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: Locking a table

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Jan 2006
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Locking a table

    Hi All

    I have a table named products that i need to lock so no one can write to it until i have updated it. It still needs to be readable. I have read the Mysql Manual but its over my head.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    with myisam tables, that's not possible. why do oyu need to lock the table?

  3. #3
    SitePoint Member
    Join Date
    Jan 2006
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The table i like to lock shows all the rows to a user who then can update each row and takes about an hour. Other users have access to a php page that give them access to update a row at a time.

    When the full update page is open i don't want any changes to the table.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    if the super-user is in the process of updating the entire table, then what happens to the updates the normal users reuqest? are they just lost?

  5. #5
    SitePoint Member
    Join Date
    Jan 2006
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i just want them to get lost, or recieve an error to try again later.

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ok, then mysql locks are out because of two characteristics: a lock is released when your connection terminates, and a lock that allows you to write will block all access to the table.

    also transactions are out since a transaction is rolled back when the connection is terminated, which will happen after your display page loads.

    so, the only thing left is an application level lock. the mysql implementation using get_lock() and release_lock() are out since they share a characteristic with table locks and transactions: the lock is released when the connection is terminated.

    that means you will need to implement your own application lock. i suggest you implement this by creating a table called applock with one column called lockname. make it the primary key. when the super-user goes to update the page, insert a row in to the table with a meaningful name for this process, like priceupdate (or whatever it is you're updating). if the insert fails due to the uniqueness constraint of the primary key, return an error that someone else is already updating the table. when the super-user committs their changes, delete that entry from the table.

    similarly, when a regular user tries to update the row (or even better, before they're given the chance to update it) look in the applock table for a row called priceupdate. if the row exists, don't let the commit the update.
    Last edited by longneck; Jan 29, 2006 at 19:03.


Bookmarks

Posting Permissions

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