Couple of mysql tips

Wondering what the heck is going on on your mysql DB?

show processlist ;

Shows you all the active threads, which is very handy indeed. It shows what each thread is doing, and how long it has been doing it for.

Thread safe record insertion

When doing a record insert, another thread could be trying to insert a record that would violate any DB contraints, and if it gets there before you, then your insert will fail.

There may be cases where you want don't want to have it fail and try again; you may want to take a different action if the record already exists. For example lets say you have some data like this, where the Name is a unique key...

Name, Number of products purchase

Bob, 1,

Ben, 2,

Now if two threads try to insert a user 'Roger' who has just bought an item, then the one that arrives at the server last will fail.

INSERT INTO purchase (Name, PurchaseCount) VALUES ('Roger', '1') ON DUPLICATE KEY UPDATE PurchaseCount=PurchaseCount+1;

This will handle the key collision and execute the update clause instead, in the event that somebody beat you to it. This makes it much easier to write thread safe code that adds records.