I'm having some problems with tables using autoincrementing primary
keys when I use InnoDB.. I've searched through the documentation at
Mysql and innobase's website, and havn't been able to find anything
saying this is a limitation of innodb, so I will assume this is a bug
(or unintentional feature).
Basically, if you start two transactions, and insert a record in the
first (and let the autoincrementing key be selected automatically, and
not committing), it will work. If the second transaction does the same,
it blocks.
Inserting another record from the first transaction returns a table
error 1000000. This makes both transactions kinda useless for working
with this table. Since one's frozen, and the other returns an error.
I'm sure what's happening is the second transaction is being blocked
until the first commits, so mysql knows what autoincrementing number to
issue the second insert. Problem is, this makes no sense. This would
mean effectivly only 1 transaction could be in use at a time if you are
going to be inserting into a table with a autoincrementing key. And
since the existance of a second transaction causes the first to fail too
(eg: table error 100000), it would be entirely unsafe to allow more then
1 transaction on the table.
I really could care less if my autoincrementing keys are handed out
sequentially, or if there are missing digits due to rolled back
transactions. (If I cared, I would put a timestamp on the record). Is
there some way to get this baby going? Maybe a "I don't care about
sequential auto_incrementing keys" variable?
Here's some screen dumps:
mysql> create table vroomm (i int auto_increment, j int, primary key(i))
type=innodb;
Query OK, 0 rows affected (0.03 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into vroomm (j) values(2);
Query OK, 1 row affected (0.00 sec)
--> (Second DB Transaction tries to insert vroomm)
mysql> insert into vroomm (j) values(4);
ERROR 1030: Got error 1000000 from table handler
I ran into this problem when I tried converting one of our existing
applications to run under innodb. About 3 minutes after I started it,
it deadlocked. This was the cause. Two transactions were trying to
create new DB records w/ Autoincrementing keys. The entire application
froze seconds later when the all the other threads tried to insert new
DB records too. Ooops.
Lemme know what you guys think.. If this is a bug, a feature, or how I
can get it working,
Thanks,
-Joe
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
