Locking Tables

[ToonMariner]

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.

[fenway]

A lock is database-wide, otherwise it would make no sense to lock it at all.

[ToonMariner]

?

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

[shoz]

[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

[ToonMariner]

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

[shoz]

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.