Does anyone know of a method in rails to get the next ID from the database? Thanks, -Travis _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Travis Briggs wrote:>Does anyone know of a method in rails to get the next ID from the database? > >Thanks, >-Travis > >def get_max connection.select_one("SELECT MAX(id) FROM #{table_name}")[''MAX(id)''] end -- stefan
mo = MyObject.new # initialize fields to default (valid) values mo.save # mo.id is now set with the next ID You don''t want to try to guess what the next ID from the database is going to be because it''s possible (if there is more than one process accessing the DB) that the ID you would get back from doing a "SELECT MAX(id)+1 FROM mytable" is out of date by the time you get around to actually inserting the new record. -lv Travis Briggs wrote:> Does anyone know of a method in rails to get the next ID from the database? > > Thanks, > -Travis[snip]
Isn''t that the *last* id, not the next? Doesn''t the next id depend on the activity of all the connections to the database, and is therefore unknowable? Or is there a trick with transactions that can lock it? -- Alex Stefan Kaes wrote:> Travis Briggs wrote: > >> Does anyone know of a method in rails to get the next ID from the >> database? >> >> Thanks, >> -Travis >> >> > > def get_max > connection.select_one("SELECT MAX(id) FROM #{table_name}")[''MAX(id)''] > end > > -- stefan > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails
lock tables TABLENAME; select MAX(ID) +1 from TABLENAME unlock tables ; adam Alex Young wrote:> Isn''t that the *last* id, not the next? > > Doesn''t the next id depend on the activity of all the connections to the > database, and is therefore unknowable? Or is there a trick with > transactions that can lock it? >
> lock tables TABLENAME; > select MAX(ID) +1 from TABLENAME > unlock tables ;The above code still has a race condition. If a second request for the next ID comes in before you commit the insert for the first request, the insert for the second request will use the same ID as first request. You need to commit your insert before you unlock the table to avoid it. Even better, try using a database that has sequence support, such as PostgreSQL.
On 31.10.2005, at 20.32, Jeremy Evans wrote:> Even better, try using a database that has > sequence support, such as PostgreSQL.Correct. There are security concerns (xss comes to mind) that can be tackled by using signed ids in the form and there the ability to pre- fetch the coming ID from a sequence comes in really handy. //jarkko -- Jarkko Laine http://jlaine.net http://odesign.fi _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Jeremy Evans wrote:>>lock tables TABLENAME; >>select MAX(ID) +1 from TABLENAME >>unlock tables ; > > > The above code still has a race condition. If a second request for > the next ID comes in before you commit the insert for the first > request, the insert for the second request will use the same ID as > first request. You need to commit your insert before you unlock the > table to avoid it. Even better, try using a database that has > sequence support, such as PostgreSQL.It''ll also scale horribly. Every request that hits this code will be serialised by the lock. If you need your IDs to be contiguous (say, invoice numbers in the UK), you do need to do this, but otherwise you don''t and shouldn''t: use sequences, which will give you lock-free assignment of unique IDs. The "trick" with sequences and transactions is that the fact that a sequence has been incremented is visible to other transactions before commit, and an ID once "taken" is not returned if the transaction fails, so you don''t need a lock but you do "lose" IDs. Chris.
This will be database dependent at the moment, so if you use it you''ll
prevent yourself from being generic with respect to databases, but with
MySQL here''s how I maintain consistency with our legacy tables id
generating mechanism in rails
next_id = ActiveRecord::Base.connection.insert(''update files_seq
set
id=LAST_INSERT_ID(id+1)'')
You can check out the mysql docs here:
http://dev.mysql.com/doc/refman/4.1/en/information-functions.html
Interestingly the php adodb library provides a cross database format for
supporting this kind of id incrementing. Perhaps we''ll see the same
thing in rails at some point
HTH
CHEERS> SAM
Travis Briggs wrote:
> Does anyone know of a method in rails to get the next ID from the
> database?
>
> Thanks,
> -Travis
>
>------------------------------------------------------------------------
>
>_______________________________________________
>Rails mailing list
>Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
Alex Young wrote:>Isn''t that the *last* id, not the next? > >Doesn''t the next id depend on the activity of all the connections to the >database, and is therefore unknowable? Or is there a trick with >transactions that can lock it? > > >Well, the next one (n'') will be the retrieved value + 1. But as others have pointed out, using n'' to store a new record can only work reliably, if no no other process tries to use the same value. So this method works only if your web app is single threaded and single process and the only entry source for new records. -- stefan
Stefan Kaes wrote:> Well, the next one (n'') will be the retrieved value + 1. But as others > have pointed out, using n'' to store a new record can only work reliably, > if no no other process tries to use the same value. So this method works > only if your web app is single threaded and single process and the only > entry source for new records.Exactly what I was trying to say, but clearer :-) -- Alex