>>>>> "Robert" == Robert A Crawford <crawford@stripped> writes:
Robert> On Thu, Nov 04, 1999 at 06:01:42PM +0000, Graham Ashton wrote:
>> I have two columns (actually I have more, but they're not relevant here) in
>> my table.
>> - `id' is simply an integer that relates directly to a real life event
>> (that I'm monitoring).
Robert> What assigns the id to an event? If you create it yourself,
Robert> use an autoincrement column. If the id is externally assigned:
Robert> 1. Attempt the update.
Robert> 2. If no rows were updated,
Robert> 2.1 Try to get an advisory lock (with GET_LOCK()) for
Robert> that id.
Robert> 2.2 If you get the lock, insert the row, release the lock,
Robert> and continue.
Robert> 2.3 If the lock is already held, start over with 1.
Robert> This way, you never really lock the table, just issue an advisory
Robert> that someone's creating that ID.
Hi!
Why not instead do it this way:
1. Attempt the update.
2. If no rows were updated,
2.1 Insert the row.
2.2 If the insert failed because of a dupplicate key, another thread
did create the row in the mean time.
2.3.1 Update the row that you now know exist.
Regards,
Monty
