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: Martin Gregorie on 9 Apr 2010 11:00 On Thu, 08 Apr 2010 01:09:05 +0100, Tom Anderson wrote: > I'd be keen to see an update in there somewhere too! > I was tired when I wrote that, but the logic is the same. Just substitute UPDATE for SELECT and the update count for the SELECT count(*) result in my pseudocode. -- martin@ | Martin Gregorie gregorie. | Essex, UK org |
From: Arne Vajhøj on 11 Apr 2010 22:13 On 07-04-2010 08:58, Andreas Leitgeb wrote: > Arne Vajhøj<arne(a)vajhoej.dk> 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. > > Would it be safe? Or could one end up with two entries for > that key, if the attempted updates happen at the same time? A sufficient high transaction isolation level should fix that. > (Ok, two equal keys could be prevented by unique-key-constraints, > but then it would mean that the "last" one doesn't always win, but > may possibly run into an DB-error, instead...) A lower transaction isolation level and such a constraint combined with a retry in the code may perform better than a high transaction isolation level depending on how the database handles this stuff. Arne
From: Arne Vajhøj on 11 Apr 2010 22:15 On 07-04-2010 17:46, 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. And the transaction isolation level is sufficient. Arne
From: Arne Vajhøj on 11 Apr 2010 22:21 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? 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. http://dev.mysql.com/doc/refman/5.0/en/replace.html <quote> Performance considerations: Please note that REPLACE INTO is a much slower performer than an UPDATE statement. Keep in mind that a REPLACE INTO requires a test on the keys, and if a matching unique key is found on any or all columns, a DELETE FROM is executed, then an INSERT is executed. There's a lot of management of rows involved in this, and if you're doing it frequently, you'll hurt your performance unless you simply cannot do with any other syntax. The only time when I can see where you'd actually need a REPLACE INTO is when you have multiple unique constraints on a table, and need to drop any rows that would match any of the constraints. Then REPLACE INTO becomes more efficient from DELETE FROM... INSERT INTO... If you're looking at a single unique column table (Primary Key), please use UPDATE, or INSERT. Also, check out INSERT ... ON DUPLIATE KEY UPDATE... as an alternative if you're willing to stick to MySQL 4.1+ </quote> does not promise blazing speed. Arne
From: Tom Anderson on 17 Apr 2010 07:54
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. Mind you, with RDBMSs which implement serializable as not-really-serializable on top of MVCC, like Oracle, the performance hit should be much smaller. It would be interesting to get numbers on this, of course. > 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. tom -- Standing on the shoulders of Google |