Martijn Tonies wrote:
>>I have a database for ASN information in which I save asn information for
> reference by other scripts (asn lookup can be expensive).
>>CREATE TABLE `ASNINFO` (
>>`asnInfoID` int(11) NOT NULL auto_increment,
>>`asNumber` int(11) NOT NULL default '0',
>>`description` varchar(255) default NULL,
>>`comment` varchar(255) default NULL,
>>`f_time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
>>PRIMARY KEY (`asnInfoID`),
>>UNIQUE KEY `asNumber` (`asNumber`)
>>) ENGINE=InnoDB DEFAULT CHARSET=latin1
>>Multiple Ips can share the same ASN value.
>>Is it better to do an 'insert ignore' into ASNINFO when I have to look one
> up, or is it better to try to fetch the ASN information to see if it exists,
> and then if it does not exist, do the insert. I figure the fetch is one sql
> command, and the insert is one sql command, so if an asn does not exist, it
> is two data base accesses, where if I do an 'insert ignore' it is only one
> command and if the value is in the table, the insert is ignored.
> The "insert ignore" sounds a bit hackish to me.
> Given that "asNumber" is indexed via the unique key, it will be fast anyway.
> That being said, it is entirely possible that someone will insert the
> row you are looking for between your first select and the insert
> in case you didn't find it. So you would have to handle that case
> Martijn Tonies
> Database Workbench - development tool for MySQL, and more!
> Upscene Productions
> My thoughts:
> Database development questions? Check the forum!
So, for my peace of mind, I would need to follow these procedures to
ensure Martijn's issue doesn't happen:
1. lock the table
2. execute my select
3. insert if it does not already exist
4. unlock the table
On a high level, is this a correct list of procedures?