List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:April 26 2001 3:59pm
Subject:Re: Cost of transactions, and alternatives
View as plain text  
On Thu, Apr 26, 2001 at 04:31:40PM +0100, alec.cawley@stripped wrote:
> Hello, MySQL Experts,
> 
> I wonder if you could give me some advice on a database design problem.
> 
> I have a table which will have many more reads than writes (between 10 and
> 100 times). I wish to make
> updates to that table and to a slave table transaction safe. The basic
> structure is of a master slave
> relationship with a one-to-many relationship between master and slave. I
> assume that I create a
> row in the master table, getting a unique ID from autoincrement, then use
> that ID to key all the entries
> n the slave tabel. The problem is that the master record must not be
> visible until all the slaves are
> visible. I do not mind (much) if I get orphan rows in the slave table. But
> I do mind if I get visible entries
> in the master table but some of the rows never made it to the slave.
> 
> I see two approaches. I can use transaction safe table types (BDB or
> Innobase). Or I can create the
> row in the main table with an "incomplete" flag, then clear the flag when
> the slave table has been safely
> updated. General MySQL documentation suggests that one can usually engineer
> transaction-safety
> into the application, and that this is to be preferred if you want high
> performance.
> 
> I am worried about speed, which is important to me. As I said, reads
> overwhelmingly dominate, and
> (indexed) speed is a major concern. Are transaction-safe tables slower than
> MyISAM tables for
> read-only access, and if so how much? Do both tables need to be
> transaction-safe, or only the
> master?
> 
> Alternatively, is there any way I can safely generate a unique id without
> creating the master row?
> The I could put the slave rows in before I create the master row, and I
> wouldn't have a problem.

Sure, if you're willing to create a one-row table to hold the sequence
number. You can use the 1-argument form of LAST_INSERT_ID() to create
values that will be treated like the most recent AUTO_INCREMENT value.

# create the table
CREATE TABLE seqnum (num INT UNSIGNED NOT NULL);
# initialize sequence number value
INSERT INTO seqnum SET num = 0;
# Generate a new sequence number
UPDATE seqnum SET num = LAST_INSERT_ID(num+1);
# see what it was
SELECT LAST_INSERT_ID();
# Generate a new sequence number
UPDATE seqnum SET num = LAST_INSERT_ID(num+1);
# see what it was
SELECT LAST_INSERT_ID();
# Generate a new sequence number
UPDATE seqnum SET num = LAST_INSERT_ID(num+1);
# see what it was
SELECT LAST_INSERT_ID();
# etc

Thread
Cost of transactions, and alternativesalec.cawley26 Apr
  • Re: Cost of transactions, and alternativesPaul DuBois26 Apr
  • Re: Cost of transactions, and alternativesJoseph Bueno26 Apr
Re: Cost of transactions, and alternativesHeikki Tuuri26 Apr