Re: Re: [JBoss-user] Writing a finder which returns max id used
pStatement = con.prepareStatement( "Insert into Attribute " + "(ID, Value, Display_Format, Attribute_Switches, Name, Type_ID) " + "Select max(id)+1, ?, ?, ?, ?, ? from Container "); Wouldn't most, if not all, databases handle concurrancy issues in this case? Alas, no. Inserts are queued because they are very slow (they involve index updates on the PK). The read locks will be released long before the insert executes in nearly all cases. This sort of thing isn't obvious until you work on the innards of a high performance db server. The only thing you can do to make this safe is get a table lock at the start of your operation. Which may prove impossible if other processes have read locks. As a matter of interest I've been giving some thought to JBoss's methodology for CMP updates. At the moment there are conditions where a row is deleted and re-inserted in order to avoid dependencies on column names. I'm not too thrilled with dependence on ordinal column position - this is specifically forbidden by relational theory. My religious position notwithstanding, deletion and re-insertion causes not one but *two* index updates *per table index* and therefore, I think, it will be a lot cheaper to create an updateable ResultSet and manipulate that by ordinal position. There may be other considerations. If so, I hope someone will advise before I waste too much time on it. ___ JBoss-user mailing list [EMAIL PROTECTED] http://lists.sourceforge.net/lists/listinfo/jboss-user
Re: [JBoss-user] Writing a finder which returns max id used
[EMAIL PROTECTED] wrote:
How about using some creative SQL like:
pStatement = con.prepareStatement(
"Insert into Attribute " +
"(ID, Value, Display_Format, Attribute_Switches, Name, Type_ID) " +
"Select max(id)+1, ?, ?, ?, ?, ? from Container ");
pStatement.setString(1, value);
pStatement.setString(1, displayFormat);
pStatement.setLong (2, attributeSwitches);
pStatement.setString(1, name);
pStatement.setInt (2, typeID);
if (pStatement.executeUpdate() != 1)
{
throw new CreateException(...
}
//now return the primary key...
return new Integer(???);
Just fill in the parameters with your values and the SQL statement is executed
all at once.
Wouldn't most, if not all, databases handle concurrancy issues in this case?
Yep! by locking every row in the table, either individually or by
escalating to a table lock. This approach is fine if you don't expect
too many creates of that bean, and you can get your primary key value
back so that you don't have to lie to the container (another select max
might work)
-danch
Confidential e-mail for addressee only. Access to this e-mail by anyone else is
unauthorized.
If you have received this message in error, please notify the sender immediately by
reply e-mail
and destroy the original communication.
___
JBoss-user mailing list
[EMAIL PROTECTED]
http://lists.sourceforge.net/lists/listinfo/jboss-user
RE: [JBoss-user] Writing a finder which returns max id used
What about java.rmi.server.UID? It has a hashCode() method that returns an integer. -Original Message- From: Peter Routtier-Wone [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 04, 2001 11:45 AM To: [EMAIL PROTECTED] Subject: Re: [JBoss-user] Writing a finder which returns max id used You can also use the facilities of the database engine to help with this problem. Oracle has sequences which guarantee a unique number even with concurrent access. Sybase has autoincrement columns. Use of the database as a UID dispenser has its merits with respect to the concurrency issue, but it raises portability issues. Another possibility is one of the few really good ideas to come out of Redmond - the GUID. GUIDs are unique in space and time. The first eight bytes are a machine identifier (based on the MAC address of your network interface, if I remember correctly) and the second eight bytes are a timestamp. The benefit of this approach is that it is not necessary to contact any other computer to derive a unique value. The downside is that string comparisons - and worse yet string based indexes - are *expensive*. But they are seldom as expensive as contacting another machine, or rolling back and re-running a transaction. ___ JBoss-user mailing list [EMAIL PROTECTED] http://lists.sourceforge.net/lists/listinfo/jboss-user ___ JBoss-user mailing list [EMAIL PROTECTED] http://lists.sourceforge.net/lists/listinfo/jboss-user
Re: [JBoss-user] Writing a finder which returns max id used
You can also use the facilities of the database engine to help with this problem. Oracle has sequences which guarantee a unique number even with concurrent access. Sybase has autoincrement columns. Use of the database as a UID dispenser has its merits with respect to the concurrency issue, but it raises portability issues. Another possibility is one of the few really good ideas to come out of Redmond - the GUID. GUIDs are unique in space and time. The first eight bytes are a machine identifier (based on the MAC address of your network interface, if I remember correctly) and the second eight bytes are a timestamp. The benefit of this approach is that it is not necessary to contact any other computer to derive a unique value. The downside is that string comparisons - and worse yet string based indexes - are *expensive*. But they are seldom as expensive as contacting another machine, or rolling back and re-running a transaction. ___ JBoss-user mailing list [EMAIL PROTECTED] http://lists.sourceforge.net/lists/listinfo/jboss-user
Re: [JBoss-user] Writing a finder which returns max id used
You can also use the facilities of the database engine to help with this problem. Oracle has sequences which guarantee a unique number even with concurrent access. Sybase has autoincrement columns. - Original Message - From: Peter Routtier-Wone [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 03, 2001 7:39 AM Subject: Re: [JBoss-user] Writing a finder which returns max id used Wouldn't the easiest solution be to make a select max(key) statement and then add 1 to get your new key? This isn't an EJB question, it's a general concurrency issue. What you say is true for single user exclusive access to the database. However, processes seldom run in isolation. High performance systems supporting concurrent access run a constant risk that after process A reads the max value, but before it commits ++max to the database, process B will read the database and get THE SAME max and therefore try to use the same value for a PK. This is even more possible with multi-processor arrangements. Not only the operation but the the whole transaction becomes unserialisable and is therefore aborted by the database server. You could write lock the table for the duration, but bye-bye scalability. Is this possible and how would I write it? It's entirely possible and you wouldn't write it now that you know better. You would bite the bullet and implement another, more robust solution. For example, you might build a servlet that queues requests for a single thread that dispenses PK values. This assumes that all processes writing into the table will use the dispenser. There are other issues relating to initialisation of the dispenser, and the need for it to dispense in multiple contexts (for different entities). With appropriate design it can maintain contiguity using your ++max trick when it starts a context. ___ JBoss-user mailing list [EMAIL PROTECTED] http://lists.sourceforge.net/lists/listinfo/jboss-user ___ JBoss-user mailing list [EMAIL PROTECTED] http://lists.sourceforge.net/lists/listinfo/jboss-user
