[prev in list] [next in list] [prev in thread] [next in thread] 

List:       msql-mysql-modules
Subject:    Re: Table locking in mysql
From:       Jochen Wiedmann <joe () ispsoft ! de>
Date:       1998-10-10 0:54:52
[Download RAW message or body]

> Date: Fri, 09 Oct 1998 13:32:25 -0700
> From: Nick Tonkin <nick@valueclick.com>
> Subject: Table locking in mysql
> 
> Hello
> 
> I have a routine that goes like this (on a db that is logging a web server
> using Apache::DBI and a custom mod_perl logging module):
> 
> select all records from an interim table
> process the records with perl
> insert the records in a permanent table
> delete all the records from the interim table

The easiest solution seems to me to be as follows:

  1.) Add an autoincrement column to your interim table, "id" say.
  2.) Leave the logging code unchanged. Note this makes MySQL fill
      the id column automatically and with increasing values.
  3.) Let your above code look like this:

	my $query = "SELECT * FROM interim_table ORDER BY id";
	my $sth = $dbh->prepare($query);
	my $max_id = undef;
	while (my $ref = $sth->fetchrow_arrayref) {
	    # Do something here
	    ...
	}
	if (defined($max_id)) {
	    $dbh->do("DELETE FROM interim_table WHERE id <= $max_id");
	}

Note you need neither table locking here nor do you have any time
critical
sections. The locking is done by MySQL itself while creating the id
values.


Bye,

Jochen


-- 
Jochen Wiedmann						joe@ispsoft.de
On the 8th day god created email. And got noted		+49 7123 14887
the world was good. Then the devil added HTML mail...


-----------------------------------------------------------
Send a mail to mdomo@tcx.se with
unsubscribe msql-mysql-modules your@subscription.address
in the body of the message to unsubscribe from this list.

[prev in list] [next in list] [prev in thread] [next in thread]