You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by is_maximum <mn...@gmail.com> on 2009/05/17 15:19:29 UTC
id and @GeneratedValue
Hello
Can we tell the OpenJPA to automaticall select maximum id from the table to
set the next id rather than using the OPENJPA_SEQUENCE_TABLE or any other
database object?
thanks
--
View this message in context: http://n2.nabble.com/id-and-%40GeneratedValue-tp2916124p2916124.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: id and @GeneratedValue
Posted by is_maximum <mn...@gmail.com>.
Ok guys
This is a big application for a banking systems and as you know the Oracle's
sequences have some problem. The sequences take up cache and this ends up
with lacking of cache places and finally the whole performance will be
downgraded. There are lots of tables and each has its own sequence so in
return we decided to remove those sequences that been created for the base
tables which are created to keep basic data and we know that the insert
operation on them is low (perhapse 3000 at worst condition) so getting the
max id and increasing it is much better than using sequences and fill up the
database cache.
And for concurrency issues we know that inserting these sort of data will be
taken palce once at the installing system and rarely will be occurs and if
at worst condition a concurrent insertion happens the second user will
encounter an error and have to try again. This is much better than the other
users waits for a long time just for a small withdrawal of cash
Now what do you think on this idea?
Daryl Stultz wrote:
>
> On Sun, May 17, 2009 at 1:30 PM, Wes Wannemacher <we...@wantii.com> wrote:
>
>> That is generally a bad idea. Select max(row) will generally initiate
>> a full index scan or, even worse, a full table scan.
>>
>
> Isn't it a greater problem that this approach is a race condition? 2
> threads
> call max(id) about the same time and then try to insert max + 1 one just
> after the other.
>
> --
> Daryl Stultz
> _____________________________________
> 6 Degrees Software and Consulting, Inc.
> http://www.6degrees.com
> mailto:daryl@6degrees.com
>
>
--
View this message in context: http://n2.nabble.com/id-and-%40GeneratedValue-tp2916124p2945713.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: id and @GeneratedValue
Posted by Wes Wannemacher <we...@wantii.com>.
Yeah... I've seen stored procs like this on more than one occasion -
DECLARE @newId INT
SELECT @newId = MAX(idCol) FROM IdTable
INSERT INTO IdTable (IdTable, ... ) VALUES ( @newId +1, ... )
RETURN @newId + 1 (or SELECT @newId + 1 as OUTPUT)
*untested of course, trying to remember off the top of my head...
There are a number of things that can go wrong. First off... the
database I worked with primarily (Sybase) does not lock other
transactions from reading the data, even during the update. Even if
you wrap it in a transaction, the data is still not locked until the
UPDATE, and even then, it may not be locked from reads at all. So, two
simultaneous calls to the stored proc would execute as follows -
proc1 -> SELECT @newId = MAX(idCol) FROM IdTable
proc2 -> SELECT @newId = MAX(idCol) FROM IdTable
proc1 -> INSERT INTO IdTable (IdTable, ... ) VALUES ( @newId +1, ... )
proc2 -> INSERT INTO IdTable (IdTable, ... ) VALUES ( @newId +1, ... )
as you can see, they'll both return the same number and set the column
to the same number, which is generally not what you want to happen.
Another approach that still leads to concurrency problems is using a
separate table for generating IDs, much like the
OPENJPA_SEQUENCE_TABLE. The problem isn't in the design, but given a
table, a stored proc (or whatever you use) that does the following
will also break -
DECLARE @newId INT
SELECT @newId = MAX(idCol) FROM IdTable WHERE idColName = @procParam
UPDATE IdTable SET idCol = @newId +1 WHERE idColName = @procParam
RETURN @newId + 1 (or SELECT @newId + 1 as OUTPUT)
Given the same logic as above, duplicate ids can be returned. I don't
know for sure about other databases, but I do know that Sybase doesn't
lock the IdTable in this example from other
threads/connections/whatever from reading the data during a
transaction. Being faced with this problem, I grabbed a Sybase
employee at a Sybase training and nailed her down on this... We came
up with the following solution for this -
DECLARE @newId INT
UPDATE IdTable SET idCol = idCol +1, @newId = idCol WHERE idColName = @procParam
RETURN @newId + 1 (or SELECT @newId + 1 as OUTPUT)
Succinct, transaction/concurrency safe, etc. This doesn't solve the
problem of using MAX() though. In that case, I would suggest that
using an IDENTITY field (or whatever your database's analog is). There
are other problems you will deal with, but they tend to not be as big
as generating dupe IDs during high load.
-Wes
On Tue, May 19, 2009 at 8:04 AM, Daryl Stultz <da...@6degrees.com> wrote:
> On Sun, May 17, 2009 at 1:30 PM, Wes Wannemacher <we...@wantii.com> wrote:
>
>> That is generally a bad idea. Select max(row) will generally initiate
>> a full index scan or, even worse, a full table scan.
>>
>
> Isn't it a greater problem that this approach is a race condition? 2 threads
> call max(id) about the same time and then try to insert max + 1 one just
> after the other.
>
> --
> Daryl Stultz
> _____________________________________
> 6 Degrees Software and Consulting, Inc.
> http://www.6degrees.com
> mailto:daryl@6degrees.com
>
--
Wes Wannemacher
Author - Struts 2 In Practice
Includes coverage of Struts 2.1, Spring, JPA, JQuery, Sitemesh and more
http://www.manning.com/wannemacher
Re: id and @GeneratedValue
Posted by Daryl Stultz <da...@6degrees.com>.
On Sun, May 17, 2009 at 1:30 PM, Wes Wannemacher <we...@wantii.com> wrote:
> That is generally a bad idea. Select max(row) will generally initiate
> a full index scan or, even worse, a full table scan.
>
Isn't it a greater problem that this approach is a race condition? 2 threads
call max(id) about the same time and then try to insert max + 1 one just
after the other.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
mailto:daryl@6degrees.com
Re: id and @GeneratedValue
Posted by Miłosz Tylenda <mt...@o2.pl>.
Hello!
I don't think OpenJPA has an id generator suitable for your need but you could try creating a custom one by extending AbstractJDBCSeq or one of its subclasses. Jeremy has recently posted a sample [1] in another thread.
Regards,
Milosz
[1] http://n2.nabble.com/correct-way-to-load-persistent-metadata-at-startup-tp2865060p2909990.html
>
> Yes I know it will scan all the rows, but we need this for those small table
> that we expect no frequent insert operation on them
>
>
>
> Wes Wannemacher wrote:
> >
> > That is generally a bad idea. Select max(row) will generally initiate
> > a full index scan or, even worse, a full table scan.
> >
> > -Wes
> >
> > On 5/17/09, is_maximum wrote:
> >>
> >> Hello
> >>
> >> Can we tell the OpenJPA to automaticall select maximum id from the table
> >> to
> >> set the next id rather than using the OPENJPA_SEQUENCE_TABLE or any other
> >> database object?
> >>
> >> thanks
> >> --
> >> View this message in context:
> >> http://n2.nabble.com/id-and-%40GeneratedValue-tp2916124p2916124.html
> >> Sent from the OpenJPA Users mailing list archive at Nabble.com.
> >>
> >>
> >
> >
> > --
> > Wes Wannemacher
> > Author - Struts 2 In Practice
> > Includes coverage of Struts 2.1, Spring, JPA, JQuery, Sitemesh and more
> > http://www.manning.com/wannemacher
> >
> >
>
> --
> View this message in context: http://n2.nabble.com/id-and-%40GeneratedValue-tp2916124p2921077.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>
>
Re: id and @GeneratedValue
Posted by is_maximum <mn...@gmail.com>.
Yes I know it will scan all the rows, but we need this for those small table
that we expect no frequent insert operation on them
Wes Wannemacher wrote:
>
> That is generally a bad idea. Select max(row) will generally initiate
> a full index scan or, even worse, a full table scan.
>
> -Wes
>
> On 5/17/09, is_maximum <mn...@gmail.com> wrote:
>>
>> Hello
>>
>> Can we tell the OpenJPA to automaticall select maximum id from the table
>> to
>> set the next id rather than using the OPENJPA_SEQUENCE_TABLE or any other
>> database object?
>>
>> thanks
>> --
>> View this message in context:
>> http://n2.nabble.com/id-and-%40GeneratedValue-tp2916124p2916124.html
>> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>>
>>
>
>
> --
> Wes Wannemacher
> Author - Struts 2 In Practice
> Includes coverage of Struts 2.1, Spring, JPA, JQuery, Sitemesh and more
> http://www.manning.com/wannemacher
>
>
--
View this message in context: http://n2.nabble.com/id-and-%40GeneratedValue-tp2916124p2921077.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: id and @GeneratedValue
Posted by Wes Wannemacher <we...@wantii.com>.
That is generally a bad idea. Select max(row) will generally initiate
a full index scan or, even worse, a full table scan.
-Wes
On 5/17/09, is_maximum <mn...@gmail.com> wrote:
>
> Hello
>
> Can we tell the OpenJPA to automaticall select maximum id from the table to
> set the next id rather than using the OPENJPA_SEQUENCE_TABLE or any other
> database object?
>
> thanks
> --
> View this message in context:
> http://n2.nabble.com/id-and-%40GeneratedValue-tp2916124p2916124.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>
>
--
Wes Wannemacher
Author - Struts 2 In Practice
Includes coverage of Struts 2.1, Spring, JPA, JQuery, Sitemesh and more
http://www.manning.com/wannemacher