[mythtv] [mythtv-commits] mythtv commit: r21780 by stuartm

[Stuart Morgan]

On Friday 11 Sep 2009 22:15:51 Daniel Kristjansson wrote:
> On Fri, 2009-09-11 at 17:14 +0000, mythtv at cvs.mythtv.org wrote:
> > Author: stuartm
> >         Date: 2009-09-11 17:14:46 +0000 (Fri, 11 Sep 2009)
> > Set the record id after insertion of a new rule using
> > QSqlQuery::lastInsertId().
> 
> lastInsertId() can be used when the database back end supports it, but
> then you should query it before and after the insert and only its value
> if the insert is successful and the value has been incremented by one
> and the insert does not use the DELAYED keyword (and obviously it can
> not be used with REPLACE inserts.)
> 
> In any case, if the record id is really needed there should be a backup
> method for obtaining it when someone else happens to do an insert
> shortly after you do an insert into the table and when the database
> backend does not support the optionally supported lastInsertId().

I'm assuming that it use's mysqls LAST_INSERT_ID() which should be perfectly 
safe, I've been using it in applications for a long time. It will always 
return the value for the last insertion on the _current_ connection. Granted 
I'm assuming that when we grab a connection from the pool we have it 
exclusively. The call to lastInsertId() is the next instruction after exec() - 
the return value of which we check first so the window for the value to change 
is in the range of 1ms. It's not being used with either DELAYED or REPLACE. 
Lastly since we only support mysql, the assumption is that the backend is 
mysql and that's an assumption made all over the code, so this wouldn't be the 
only thing to break.

I think LAST_INSERT_ID() is certainly a far safer method than the one we were 
using previously in the old code "SELECT MAX(recordid) FROM record;" - there 
exists a real danger of an additional record being inserted in the intervening 
period which might come from any frontend or backend using another mysql 
connection.
-- 
Stuart Morgan