Professional Web Applications Themes

Table remains locked after exiting script - MySQL

Hi all, I've been told that when a PHP script exits, then all tables that were locked from within the PHP script are unlocked.. obviously this is a good idea. However, I'm experiencing a problem in the website that I'm currently coding; the table doesn't unlock after I exit the script. Below is the PHP snippet relating to the problem area. $com->setQuery('LOCK TABLE updates WRITE, doents WRITE'); $com->execute(); $com->reset(); exit(); $com is a reference to an object that we use to build up and execute queries, and I don't think is that important as it doesn't do anything special (it's ...

  1. #1

    Default Table remains locked after exiting script

    Hi all,

    I've been told that when a PHP script exits, then all tables that were
    locked from within the PHP script are unlocked.. obviously this is a
    good idea.

    However, I'm experiencing a problem in the website that I'm currently
    coding; the table doesn't unlock after I exit the script. Below is the
    PHP snippet relating to the problem area.

    $com->setQuery('LOCK TABLE updates WRITE, doents WRITE');
    $com->execute();
    $com->reset();

    exit();

    $com is a reference to an object that we use to build up and execute
    queries, and I don't think is that important as it doesn't do anything
    special (it's essentially an extra layer we've added to ensure that
    any strings that are used in the query are properly escaped).

    The user that is used in executing this query is called
    'lockedDocManip'. When the Server connections are viewed in MySQL
    Admin, this user has a connection after exit() is called because
    persistent connections are being used. After running the PHP script
    above, I've clicked the 'home' link, which uses SQL to check that the
    user is currently logged in (an SQL user called 'login').

    This new connection that is established is in a 'locked' state when
    viewed in Mysql Admin, which confirms that the tables are still
    locked.

    What could be causing this problem?

    The reason why I'm using locks is that a field exists that multiple
    users can edit, but it can not be re-edited once it has been edited
    once. I thus lock the tables, see if anyone has edited the field, and
    if not, edit it. If I don't lock the tables a race condition occurs.
    Is there perhaps an alternative solution to this problem?

    Thanks

    Taras

    Taras_96 Guest

  2. #2

    Default Re: Table remains locked after exiting script

    On 15 Mar, 11:42, "Taras_96" <com> wrote: 

    How about not using persistant connections and/or issuing a and UNLOCK
    TABLES command?

    Captain Guest

  3. #3

    Default Re: Table remains locked after exiting script

    On 15 Mar 2007 04:42:10 -0700, Taras_96 wrote: 

    Presuming you're NOT using mysql_pconnect() in your abstraction layer...
    It could be the phenomenon that Axel was talking about in the "What does
    'Init DB' mean in the log?" thread, where MySQL can reuse connection
    from the same host/user/password.
     

    You get the "Good Poster" award for the day, for supplying the actual
    problem. A way to avoid the race condition is to make the value of the
    latching field part of the UPDATE's WHERE condition:

    UPDATE my_table
    SET latch_col = 'myvalue'
    WHERE user_key = 'key_value' AND
    latch_col = 'oldvalue'

    where oldvalue is whatever you picked up in the same query used to
    determin whether the column was updated previously or not. If someone
    else updates the column in the meantime, the query will have zero
    affected rows, and you can go determine why.

    A better way is to incorportate the whole mess into a stored procedure.
    Not so much for atomicity's sake but for reliable connection. A return
    value can tell you whether the update took, whether the record was
    ineligable, whether the field was changed during processing, whether
    there was some other update error, etc.
    not updated because it changed while the

    --
    "... I've seen Sun monitors on fire off the side of the multimedia lab.
    I've seen NTU lights glitter in the dark near the Mail Gate.
    All these things will be lost in time, like the root partition last week.
    Time to die...". -- Peter Gutmann in the scary.devil.monastery
    Peter Guest

  4. #4

    Default Re: Table remains locked after exiting script

    Taras_96 wrote: 

    Why in the world would you use persistent connections?

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. Delay Error after publishing, file remains locked but.LCK is gone
    By Chocochef in forum Macromedia Contribute General Discussion
    Replies: 1
    Last Post: June 27th, 01:03 AM
  2. Win32::OLE: launch IE and _keep_it_running_ after exiting script
    By James Willmore in forum PERL Miscellaneous
    Replies: 3
    Last Post: August 29th, 10:05 PM
  3. Disabling Command button if Table can't be locked
    By Pele in forum Microsoft Access
    Replies: 0
    Last Post: July 22nd, 04:36 PM
  4. script for table extent space used
    By Richard Foote in forum Oracle Server
    Replies: 4
    Last Post: December 19th, 08:30 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139