Jump to content

Locking Tables


ToonMariner

Recommended Posts

Hi everyone...

I have a small question about locking tables......

I need to perform a multiple insert on a table and grab the insert ids from the query - so I can only do this (it seems) by getting the last insert id and the number of affected rows...

Now I need to lock the table so no other scripts can write to the table while this is in progress. My question is when you lock a table does it lock that table for all other connections other than the one holding the lock? The manual uses the term threads and I am assuming that each thread is a different connection...

Many thanks for any help.
Link to comment
Share on other sites

?

According to the manual you can lock just certain tables.  All I wanted to know is when they talk about threads does that mean that the script running that has locked the table is the only one that can unlock it?

I have a gallery script - multi user login so if one person is inserting data into the relevant database table I want to prevent others being able to insert into that table until the current user has finished....
Link to comment
Share on other sites

[quote author=ToonMariner link=topic=109378.msg441535#msg441535 date=1159259886]
?

According to the manual you can lock just certain tables.  All I wanted to know is when they talk about threads does that mean that the script running that has locked the table is the only one that can unlock it?

I have a gallery script - multi user login so if one person is inserting data into the relevant database table I want to prevent others being able to insert into that table until the current user has finished....
[/quote]
The connection that created the lock is the one that has to release it.

If you're locking the tables because you think that the id returned by LAST_INSERT_ID will be affected by inserts done by other connections, note that PHP's mysql_insert_id() and MYSQL's LAST_INSERT_ID() will return the last auto_incremented value that the current connection created.

Other connections don't affect this value
[quote=mysql.com]
LAST_INSERT_ID(), LAST_INSERT_ID(expr)

Returns the first automatically generated value that was set for an AUTO_INCREMENT column by the most recent INSERT or UPDATE statement to affect such a column.

mysql> SELECT LAST_INSERT_ID();
        -> 195

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.
[/quote]

http://dev.mysql.com/doc/refman/4.1/en/information-functions.html
Link to comment
Share on other sites

I have achieved what I needed!!!
;)

Setting the table lock simply prevents other connections accessing that table - I can still do the insert/update myself.

Only problem is - my logic has failed and left the table locked

Can anyone tell me how to kill teh lock from the command line - bettr still kill ALL locks on all tables when logged in as admin
Link to comment
Share on other sites

The locks are also automatically released when the connection to the server is closed. You can kill a connection/thread using the [url=http://dev.mysql.com/doc/refman/4.1/en/kill.html]KILL SYNTAX[/url].

You should also be able to kill the process using your operating system's tools.
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.