Lock Tables in MySQL

Anon

I need to lock a table and delete some old data from it.
Can I lock it with WRITE and delete some data inside while it's locked ?.

Here is my code:

$TodaysDate=date("Ymd");
$today = getdate();
$Year = $today[year];
$mon = $today[mon];
$mday = $today[mday];
$yesterday=$mday - 1;
$DelDate=$Year.$mon.$yesterday;

mysql_query("LOCK TABLES Appoints WRITE");
mysql_query("delete from Appoints where Date<='$DelDate'");
mysql_query("UNLOCK TABLES");

Thanks,
Peter

[deleted]

yes.

http://www.mysql.com/doc/L/O/LOCK_TABLES.html

Anon

Thanks Vincent !

Peter

Anon

Why on earth do you think you need to lock tables for this?

[deleted]

Inthe posted example there is no point in locking the table, because there is only one query that does all the work.

However, if there was another query, for example one that got all the ID's of the records that should be deleted, then lcoking the table before running both queries would make sense.

Anon

I simply want to be sure that other queries cannot write to this table untill the old data has been deleted.

Anon

They can't. Only one query can execute at a time. When you issue a query the table(s) affected are effectively locked since no other query can touch that table until it's done.

Locks would be used in a situation like this:

1) select some data
2) perform calculation on that data
3) insert/update records based on the calculations

AND, when it is essential that the table(s) not change between step 1 and step 3

Anon

I'm aware of that. Just making sure Peter is as well.

[deleted]

..and so was I, just explaining to the world...

Anon

Thanks guys,

I needed to be sure before running with it.

Peter