Where's my Derby?
in [Java Programming]
|
Prev: how to record screen acrtions programatically?
Next: Build A Home Internet Business For Extra Income Stream
From: Arne Vajhøj on 18 Apr 2010 21:49 On 17-04-2010 07:54, Tom Anderson wrote: > On Sun, 11 Apr 2010, Arne Vajh?j wrote: >> On 07-04-2010 17:55, Tom Anderson wrote: >>> On Wed, 7 Apr 2010, Lew wrote: >>>> Arne Vajh?j wrote: >>>>>>>> Especially with a high update ratio then an UPDATE with a WHERE and >>>>>>>> only an INSERT if no rows were updated could be worth considering. >>>> >>>> Andreas Leitgeb wrote: >>>>>>> Would it be safe? Or could one end up with two entries for that key, >>>>>>> if the attempted updates happen at the same time? >>>> >>>> Martin Gregorie wrote: >>>>>> Should be OK provided you use explicit commit units rather than >>>>>> default automatic commits. >>>> >>>> Andreas Leitgeb wrote: >>>>> "Should be ..." doesn't really sound too convincing in the context of >>>>> possible concurrency problems ;-) >>>> >>>> If the database engine supports transactions and you remember to use >>>> them, it will be safe. >>> >>> You mean, if it has them, you remember to use them, and you're happy to >>> live with the consequences of using the serializable isolation level, >>> you'll be safe. >>> >>> Being able to do an 'upsert' in a single atomic operation makes it >>> possible to be safe much faster than having to do it with two queries. >> >> Why? > > Because you don't have to use serializable transaction isolation. > >> The UPSERT will more or less have to do the same thing. The fact that >> it is one SQL command does not guarantee that it is more efficient. > > Perhaps not, but the effect of the transaction isolation level on > concurrency is, AIUI, likely to be significant. serializable transaction isolation level in this case basicly means a lock. An UPSERT statement also needs to do a lock. It is not obvious to me why the first lock is so much more expensive that the second lock. >> http://dev.mysql.com/doc/refman/5.0/en/replace.html >> >> <quote> >> Performance considerations: > > Yeah, well, MySQL is MySQL. I wouldn't expect MySQL to behave like a > real database. But anyway: > >> Also, check out INSERT ... ON DUPLIATE KEY UPDATE... as an alternative >> if you're willing to stick to MySQL 4.1+ >> </quote> > > ON DUPLICATE KEY UPDATE is the standard way to do UPSERT on MySQL, and > isn't covered by this dire warning. No. But it still has to the work. Arne |