how can i make sure only a single record is inserted when multiple apache threads are trying to access the database?

Question

I have a web service (xmlrpc service to be exact) that handles among other things writing data into the database. Here's the scenario:

I often receive requests to either update or insert a record. What I would do is this:

1. If the record already exists, append to the record,
2. If not, create a new record

The issue is that there are certain times I would get a 'burst' of requests, which spawns several apache threads to handle the request. These 'bursts' would come within less than milliseconds of each other. I now have several threads performing #1 and #2. Often two threads would would 'pass' number #1 and actually create two duplicate records (except for the primary key).

I'd like to use some locking mechanism to prevent other threads from accessing the table while the other thread finishes its work. I'm just afraid of using it because if something happens I don't want to leave the table locked.

• UPDATE: The table already has a primary key. The ideal situation should be that the first thread should create the record if it doesn't exist, then once the second thread comes in, it won't create the another record, but just update the record that was already created. It's almost as though I'd like to make the threads form a single-file line.

Is there a solid way of handling this? I'm open to using locks if I can do it properly.

Thanks,

Answer 1

If you add a unique index on your table, the second insert will fail. Thus, all logic will be done by database server, and all you need is just to display an error to the user... Also, in such scenario, you don't have to lock table[s] during insert operations.

Answer 2

Add a unique or primary index and use:

INSERT INTO table (..) VALUES (...) ON DUPLICATE KEY UPDATE .....

Answer 3

You can put numerous steps that forms an atomic operation in a TRANSACTION

Answer 4

If you truly want to serialize your process, you can grab a Lock Tables tablename Write at the start of your service, and Unlock Tables when done.

If you are using Innodb and transactions, you have to perform the Lock Tables after the start of the transaction.

I am not advocating this method, as there is usually a better way of handling, however if you need a quick and dirty solution, this will work with a minimal amount of code changes.