MySQL Insert/Update Best Practices

[Terra]

1) perror, or you can always look at the source code

2) pretty much, yeah - to do otherwise would create chaos

3) http://www.mysql.com/news/article-45.html

4) SleepyCat DB Hash (what #3 is partially based on)

--
Terra
--1-2-3-4--
FutureQuest

[Rich]

1) perror, or you can always look at the source code Yes, attacking the source is an option I am considering. Just wondering if that 'wheel' had been invented before...

2) pretty much, yeah - to do otherwise would create chaos It wouldn't be the first time a new software release created chaos, havoc, and a number of other undesirable things. The fact that the errors are not documented leaves this as a potential threat. But, then again, documenting them does not eliminate the threat, either. :)

3) http://www.mysql.com/news/article-45.html

4) SleepyCat DB Hash (what #3 is partially based on) The link in (3) refers to NuSphere which is simply a packaged MySQL with additional functionality. While row locking is useful, it wouldn't help in this case since it is unknown whether the row exists.

I believe the Sleepycat product is based on Berkely DB and not MySQL. I realize there are alternate db solutions which handle this type of update/insert issue more gracefully, with my first choice being Oracle. I'm just trying to determine MySQL's capabilites in a multi-user/multi-transaction write environment.

The more I learn about MySQL, the more I believe it is very good for multi-reads but limited for multi-writes without a lot of "adaptation".

Rich

[Terra]

An addition to #4 would be PostgreSQL, I believe it has row locking as well...

I'm not sure if the existence of the row matters much or not on the locking as I'm sure it's a common case that the database designers have already taken into account...

A local DB Hash will most likely be your fastest solution, as the TCP connection overhead would be non-existent...

--
Terra
--Many times I'll use a DB Hash queuing area for later injection into MySQL--
FutureQuest

[PaulKroll]

I'm not sure that approach 2 in your first message is so far from what you need. However, I'd reverse the order:

UPDATE Table SET Count=Count+1 WHERE Name='TheName';

If number of records affected = 0, then do the insert.

Because:

A) If the average counts are going to be more than "2" over time, then UPDATE is the more frequently needed command anyway. You avoid trying to INSERT needlessly all the time.

B) UPDATE returns the number of rows affected. So if that's 0, and there is no error, it's INSERT time. If it's 1 and no error, you're done. If it's anything with an error, you know that it wasn't that the record didn't exist, it was something more unusual.

That's IMHO, of course. :)

[Rich]

In my spare time (LOL), I'm devoting some time to MySQL. I'm looking for comments and feedback regarding best practices for the following, frequently encountered, application requirement. Note that I have used pseudo-code to keep this post short/sweet. :)

ASSUMPTIONS:
A table exists with the following columns/data:

Name[nbsp][nbsp]Count
Sue[nbsp][nbsp] 2
Jim[nbsp][nbsp] 4
...

Name is a primary key so is unique.

TASK:
Increment the count by one if record exists. Create new record with count=1 if record does not exist. Assume we now have the name 'Larry'.

Two approaches come to mind in handling this:

Approach 1: (brute force)
Lock table
Select name, count where name=Larry
if fetch of Larry successful, Update count=count+1 where name=Larry
if fetch of Larry unsuccessful, Insert name=Larry, count=1
Unlock table

Approach 2: (required consistent error reporting <argh>)
Insert name=Larry, count=1
if insert successful DONE!
if insert unsuccessful update count=count+1 where name=Larry

I would tend to avoid approach 1 (locking tables) unless absolutely necessary, and I realize there are instances where this is necessary. One would hope, however, that cases requiring updating a single table would not be one of them. :)

One problem with approach 2 is that I have been unable to find a definative list of ALL error codes/messages returned by MySQL, so the definition of success/failure needs to be limited to the insert failing because the record already exists, not for any of a million other reasons. :(

QUESTIONS/DISSCUSSION:

(1) Anyone know where there is a list of ALL error codes/messages returned from MySQL. (Not just &quot;some common errors.&quot;)

(2) Anyone that has used MySQL over various upgrades of the package have any experience with whether MySQL maintains consistency of error numbers between releases? In other words, if I rely on a duplicate insert error, am I guaranteed the error won't change in the future rendering approach 2 unreliable?

(3) Which of these approaches is most common in practice? Is there a best practice for how to handle this situation?

(4) Other, better approaches, I have not thought of?

Rich

[Rich]

Thanks, Paul. I think that's the ticket. Your recommendation is efficient while still allowing tight error control. I'll give this a whirl...

Rich

[Rich]

Well, on second thought...

<argh>

I still believe the Insert must be performed before the Update. If the insert fails you are guaranteed that the update will be successful.

However, if you perform the update first, you are not guaranteed that the insert will succeed. Remember, this is assumed to be a multi-write environment, so there could be hundreds or thousands of transactions pending this same sequence.

Rich
-- &quot;Did you remember to lock the door?&quot;

[dank]

Just thinking here, hopefully not out loud...[nbsp][nbsp]:)[nbsp][nbsp]Could REPLACE work?[nbsp][nbsp]It seems to combine the INSERT and UPDATE commands to some degree.

Dan

[PaulKroll]

http://www.mysql.com/doc/R/E/REPLACE.html

In other words, you can't access the values of the old row from a REPLACE statement.
So no: you can't get the old value of Count to increment from a REPLACE. Thought of that one too.

It gets a little worse.

Rich, if the INSERT fails, I don't believe anything says that the UPDATE must succeed. If MySQL has run out of space or crashed or if the record was INSERTed by one thread and DELETEd by another, you're out of luck. If you really really really wanna be safe, you're going to have to LOCK.

Of course....

If you don't mind wasting a lot of space...

You could make the Name non unique. And drop the Count field. And then simply:

INSERT INTO Table SET Name='TheName'

And to retrieve the count:

SELECT COUNT(Name) FROM Table WHERE Name='TheName'

This is called wasteful cheating. :)

[PaulKroll]

And on the subject of cheating...

INSERT INTO Table SET Name='TheName',Count=0
UPDATE Table SET Count=Count+1 WHERE Name='TheName';

On the first INSERT it's bumped to 1 immediately: all future INSERTS fail and the following UPDATE increments the troublemaker.[nbsp][nbsp]If there's any chance for deleting one of these, though, you're gonna wanna lock. Still saves a SELECT though.