I've done similar things with sequences. This method is borrowed from
the DBIx::MySQLSequence perl module (the _sequences table can contain
many sequences, named in the "sequence_name" field):
update _sequences set
sequence_value = LAST_INSERT_ID(sequence_value + 1)
where sequence_name = ?
Then to get the id you will be using...
select LAST_INSERT_ID()
I'm not sure how you would make this database independent because of the
LAST_INSERT_ID() requirement. My work is restricted to mysql and
oracle, and we use oracle's sequences to handle that.
Dan.
-----Original Message-----
From: Patrick Duda [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 08, 2006 10:20 AM
To: mysql@lists.mysql.com
Subject: question about locking
Hi,
I am running MySQL 4.0.1 with j/connector 3.1 and I am having problems
trying to figure out why I am not getting the results I am expecting.
I have a table that is used for generating primary keys. It only has
one
item, an int that is incremented each time a key is needed. This is not
my
code or my design so using something like auto_incrament is not an
option.
The code runs under a container and our desire is to have several
different
containers running at the same time, all accessing the same database.
Each
container is independent so the controls need to be on the database
side.
The solution also needs to be portable to other databases so I am trying
to
stay with standard JDBC or SQL options.
The code for generating a new key is this:
try {
c = DatabaseSetup.getDBConnection();
c.setAutoCommit(false);
statement =
c.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = statement.executeQuery("select transfer_id from transferid for
update");
if (!rs.next()) {
nextTransferId = nextTransferId + 1;
StringBuffer query = new StringBuffer();
query.append("insert into transferid(transfer_id)
values (");
query.append(nextTransferId);
query.append(")");
tempStatement = c.createStatement();
// Now Update the old value with new value
tempStatement.executeUpdate(query.toString());
} else {
rs.previous();
while( rs != null && rs.next() ) {
nextTransferId = rs.getInt(1);
// Get the transfer Id and increment it instead of
using
// Db Specific sequence
nextTransferId = nextTransferId + 1;
// Now Update the old value with new value
tempStatement = c.createStatement();
tempStatement.executeUpdate("update transferid set
"+
"transfer_id= "+ nextTransferId);
}
}
} catch (SQLException e) {
if( c != null )
{
try
{
c.rollback();
c.setAutoCommit(true);
}
catch( SQLException ex )
{
}
}
throw new DBException(i18n.getMessage("dbInsertErr"),
e);
} finally {
try {
c.commit();
c.setAutoCommit(true);
if (statement != null) {
statement.close();
}
if (tempStatement != null) {
tempStatement.close();
}
if (rs != null) {
rs.close();
}
if (c != null) {
DatabaseSetup.returnDBConnection(c);
}
} catch (SQLException sql) {
logger.warn(i18n.getMessage("dbStatementErr"), sql);
}
}
return nextTransferId;
}
I thought, that if I turned off autocommit I would enter a
transaction. Then, by using the "select...for update", that I would
take
and hole a lock on the table. That no other transaction would be able
to
read the table until I released the lock. However, this is not what I
am
seeing when I run some tests. I start up a number of containers and
then
fire off a bunch of jobs to each. Each of these jobs will hit the above
code. The problem is that every so often I see the following error
message.
Error inserting records into database [Caused by: Duplicate entry
'131'
for key 1]
What am I doing wrong?
How am I suppose to be doing this via JDBC? I know it should work...
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
----------------------------------------------------------------------
This message has been scanned for viruses by TechTeam's email gateway.
--------------------------------------------------------------------------
This e-mail transmission is strictly confidential and intended solely
for the person or organization to whom it is addressed. It may contain
privileged and confidential information and if you are not the intended
recipient, you must not copy, distribute or take any action in reliance
on it. If you have received this e-mail in error, please notify the sender
as soon as possible and delete the e-mail message and any attachment(s).
This message has been scanned for viruses by TechTeam's email gateway.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
