Floyd,
Thanks for your insight, it sounds like you've taken on this problem
already.
I'll definately try some load testing to check it out.
I misunderstood your comment the last time and I'm still not sure I follow.
Is the concern a race condition in accessing the sequence number from the
db?
Or, is it regarding the re-entrancy of the Entity ejbCreate() and generated
CMP methods?
I think I'm covered as far as a race condition to the db goes.
We use a sequence generator from the db to get the primary key value and
then supply that number when
the row is inserted. (As opposed to having a trigger in the db to put the
sequence number in by itself.)
The select nextval sql statement (to get the db generated sequence number)
is atomic from the db perspective.
So regardless of where a transaction might get swapped out, it should still
get a valid sequence number.
In this scenario user A gets swapped out right before executing the sql
statement:
- DB Sequence number generator nextval is currently 8
- User A Creates calls EJB create(row in db)
- User A gets connection, prepared statement ("Select seq.nextval FROM
DUAL")
- User B Creates a new bean instance
- User B gets connection, prepared statement ("Select seq.nextval
FROM DUAL")
- User B executes prepared statement (db returns "8", db's nextval
is now "9")
- User B gets sequence number out of result set (8) and closes
connection
- User B sets primary key value to 8
- User B finishes with postCreate() and ejbCreate() creating the row
in the database (PK=8)
- User A executes prepared statement (db returns "9", db's nextval is now
"10")
- User A gets value (9) from result set and finishes up create stuff
creating a row in the db (PK=9)
If A gets swapped out right after executing sql statement
- DB Sequence number generator nextval = 8
- User A Creates calls EJB create(row in db)
- User A gets connection, prepared statement ("Select seq.nextval FROM
DUAL")
- User A executes prepared statement (db returns "8", db's nextval is now
"9")
- User B Creates a new bean instance
- User B gets connection, prepared statement ("Select seq.nextval
FROM DUAL")
- User B executes prepared statement (db returns "9", db's nextval
is now "10")
- User B gets sequence number out of result set (9) and closes
connection
- User B sets primary key value to 9
- User B finishes with postCreate() and ejbCreate() (row created,
PK=9)
- User A gets value (8) from result set and finishes up create stuff. (row
created, PK=8)
As far as the re-entrancy of ejbCreate(), as long as my result set is on the
stack I think I should be OK.
It looks like the generated code is using the stack also.
Forgive me for being a bonehead, but what am I missing?
All comments are appreciated.
Thanks,
Dan
> -----Original Message-----
> From: Floyd Marinescu [SMTP:[EMAIL PROTECTED]]
> Sent: Monday, June 12, 2000 3:02 AM
> To: [EMAIL PROTECTED]
> Subject: Re: Creating Entity Beans with db generated sequence numbers
>
> So what happens when the following occurs:
>
> >Inside the ejbCreate() method, we added code to
> >* get a Connection from DriverManager
> >* from the Connection, get a PreparedStatement with the SQL to grab
> >the next sequence number from the db
> > ("SELECT customer_seq.nextval FROM dual")
>
> ====== ROW INSERTED FROM ANOTHER TRANSACTION HERE ==========
>
> >* execute the prepared statement query
> >* grab the sequence number out of the result set.
> >* close the Connection
>
> If this happens then your create method will return the wrong
> primary key
> to the client won't it?
>
> Floyd
>
> ---------------------------------
> Senior Architect
> The Middleware Company
> http://www.middleware-company.com
> [EMAIL PROTECTED]
> 416-889-6115
>
> Need help with EJB / J2EE? Ask about our on-site training and consulting
> services.
>
> ==========================================================================
> =
> To unsubscribe, send email to [EMAIL PROTECTED] and include in the
> body
> of the message "signoff EJB-INTEREST". For general help, send email to
> [EMAIL PROTECTED] and include in the body of the message "help".
===========================================================================
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff EJB-INTEREST". For general help, send email to
[EMAIL PROTECTED] and include in the body of the message "help".
