how am i supposed to use lock tables ?

[SKANK!!!!!]

i dont understand, because i use mysql num rows and stuff someone on some other webbysite says u have to use lock tables so if someone else changes the table while the script is running then they cant if u lock the tables. but i dont understand anyone know how and what why?

[Atli]

Hey.

The LOCK TABLES and UNLOCK TABLES commands are used to block other "sessions" (other scripts, basically) from reading and/or modifying tables while your session is using them.

This is handy for scripts that need to get an accurate overview of entire tables/databases without them being modified while the script is running, like backup scripts, for example.

Or, say, if your PHP scripts needs to iterate over all rows in a table and update them, one by one, and starts by getting a list of IDs or the number of rows available. If the tables aren't locked, a single row might have been altered or removed by another script in the time it takes your PHP script to get to that specific row.

So to avoid any such problems, you lock the table at the top of your PHP script, so other scripts can't change it, execute all your commands on it, and finally release the table.

In some cases you might be better of using Transactions, though.

[SKANK!!!!!]

Hey.

The LOCK TABLES and UNLOCK TABLES commands are used to block other "sessions" (other scripts, basically) from reading and/or modifying tables while your session is using them.

This is handy for scripts that need to get an accurate overview of entire tables/databases without them being modified while the script is running, like backup scripts, for example.

Or, say, if your PHP scripts needs to iterate over all rows in a table and update them, one by one, and starts by getting a list of IDs or the number of rows available. If the tables aren't locked, a single row might have been altered or removed by another script in the time it takes your PHP script to get to that specific row.

So to avoid any such problems, you lock the table at the top of your PHP script, so other scripts can't change it, execute all your commands on it, and finally release the table.

In some cases you might be better of using Transactions, though.

ok so u only loc the table ur using? how is this done?
thanks that makes more sense to me now

[digital-ether]

ok so u only loc the table ur using? how is this done?
thanks that makes more sense to me now

See the examples on the page Atli linked:

eg:

LOCK TABLES t1 READ;

[SKANK!!!!!]

whats READ mean? and then u unlock the same way?
UNLOCK tables tbalname READ
??

[Atli]

whats READ mean?

Quoted from the link I posted before:

READ [LOCAL] lock:

• The session that holds the lock can read the table (but not write it).
• Multiple sessions can acquire a READ lock for the table at the same time.
• Other sessions can read the table without explicitly acquiring a READ lock.
• The LOCAL modifier enables nonconflicting INSERT statements (concurrent inserts) by other sessions to execute while the lock is held. (See Section 7.3.3, “Concurrent Inserts”.) However, READ LOCAL cannot be used if you are going to manipulate the database using processes external to the server while you hold the lock. For InnoDB tables, READ LOCAL is the same as READ.

Basically, you make the table read-only, preventing all sessions (including yours) from modifying the table. Other sessions can still read the table normally.

This would be ideal for the scenario I mentioned in my other post.

and then u unlock the same way?
UNLOCK tables tbalname READ
??

No. The UNLOCK TABLES statement has no optional parameters. It is always used just like that, to unlock all tables that the session has a lock on.

Note that when a session that has a lock on a table ends, the lock is automatically released, so technically you don't have to explicitly unlock tables. (Although it is a good habit to always do so anyways.)

[SKANK!!!!!]

ok well halfway through one of my sscript i want to have the tables locked on it inserts something into the table. then what?
do i have to unlock the tables? cause it wouldnt make sense if i unlocked just for that. when someone on a dif session could insert and mess it up.
is there a thing like to allow others to read but the specific session locking the tables to read and write and delete etc..?

and also if im unlocking tbales its the same thing in php as

mysql_query("UNLOCK TABLES");

right?