confused...
Why, when I create a table as follows: mysql create table requestid ( request_id int not null default 1, constraint requestid_innodb_pk_cons primary key(request_id) ) ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) Do I get the following? mysql select request_id from requestid; Empty set (0.01 sec) When I do a show create table I see: mysql show create table requestid; +---+--+ | Table | Create Table | +---+--+ | requestid | CREATE TABLE `requestid` ( `request_id` int(11) NOT NULL default '1', PRIMARY KEY (`request_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---+--+ 1 row in set (0.00 sec) Shouldn't I be getting back a '1' when I do my select??? Why am I getting an empty set? What am I not understanding? How do I create a table with a starting value of '1' or '0' for an int??? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB and locking
I guess I don't understand this locking stuff. I have a InnoDB table that has one thing in it, a counter. All I want to do is have multiple instances of the code read this counter and increment it. I want to make sure that each one is unique. Here is what I am doing in java: c.setAutoCommit(false); ... rs = statement.executeQuery(select request_id from requestid_innodb for update); ... String updateQuery = update requestid_innodb set request_id=; updateQuery = updateQuery + nextRequestId; tempStatement = c.createStatement(); tempStatement.executeUpdate(updateQuery); ... c.commit(); c.setAutoCommit(true); If I have multiple instances of this code running I end up with duplicate keys. I thought this was suppose to lock the table so that would not happen. What am I not doing right? What am I not understanding about locking? Thanks Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB and locking
At 10:52 AM 2/10/2006, [EMAIL PROTECTED] wrote: Patrick Duda [EMAIL PROTECTED] wrote on 10/02/2006 16:28:56: I guess I don't understand this locking stuff. I have a InnoDB table that has one thing in it, a counter. All I want to do is have multiple instances of the code read this counter and increment it. I want to make sure that each one is unique. Here is what I am doing in java: c.setAutoCommit(false); ... rs = statement.executeQuery(select request_id from requestid_innodb for update); ... String updateQuery = update requestid_innodb set request_id=; updateQuery = updateQuery + nextRequestId; tempStatement = c.createStatement(); tempStatement.executeUpdate(updateQuery); ... c.commit(); c.setAutoCommit(true); If I have multiple instances of this code running I end up with duplicate keys. I thought this was suppose to lock the table so that would not happen. What am I not doing right? What am I not understanding about locking? I think this problem is explained in detail at http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html Alec Yes, I know that, I have read that and that is why I am asking what it is that I am not doing right. It talks about a counter specifically: 2) read the counter first with a lock mode FOR UPDATE, and increment after that. The latter approach can be implemented as follows: SELECT counter_field FROM child_codes FOR UPDATE; UPDATE child_codes SET counter_field = counter_field + 1; A SELECT FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows. Isn't that what my Java code is doing? I start a transaction by turning off autocommit, I then do a select for update. Then I do the update and I commit. From the way I read this, no one else should be able to read the table until I commit. Yet, that is not what I am seeing. When I start several instances of the program running I get lots and lots of: Error inserting records into database [Caused by: Duplicate entry '152' for key 1] That is what has me confused. I thought I was doing things they way the manual said to. Thanks Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB and locking
At 12:54 PM 2/10/2006, Mark Matthews wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Patrick Duda wrote: I guess I don't understand this locking stuff. I have a InnoDB table that has one thing in it, a counter. All I want to do is have multiple instances of the code read this counter and increment it. I want to make sure that each one is unique. Here is what I am doing in java: c.setAutoCommit(false); ... rs = statement.executeQuery(select request_id from requestid_innodb for update); ... String updateQuery = update requestid_innodb set request_id=; updateQuery = updateQuery + nextRequestId; tempStatement = c.createStatement(); tempStatement.executeUpdate(updateQuery); ... c.commit(); c.setAutoCommit(true); If I have multiple instances of this code running I end up with duplicate keys. I thought this was suppose to lock the table so that would not happen. What am I not doing right? What am I not understanding about locking? Thanks Patrick Patrick, Are you sure the table is using the InnoDB storage engine? What does the output of SHOW CREATE TABLE for the table in question say? -Mark ysql show create table requestid_innodb; +--+-+ | Table| Create Table | +--+-+ | requestid_innodb | CREATE TABLE `requestid_innodb` ( `request_id` int(11) NOT NULL default '0', PRIMARY KEY (`request_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +--+-+ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
