Joe,
it is a feature which will change :). What it currently does
is to execute a SELECT MAX(auto-increment-column) before it does
the insert. When the other user comes, he will set a waiting next-key
lock in his SELECT MAX(...). The waiting lock prevents the first user
from inserting.
I knew that this would cause problems, but I did not realize
that a user would want to assign several sequence numbers in the
same transaction, which would very easily cause a deadlock.
The solution is that InnoDB should keep a counter in its data dictionary
and deal out the sequence numbers from there, bypassing the transactional
mechanism. This solution is also much faster than executing a SELECT
MAX(...).
Then we can get gaps in the sequence numbers, but the users
who do not like gaps can use the methods described in the InnoDB
manual.
I will do the change in version 3.23.39. It should be quite easy.
In the current version I can think of the following workaround:
create a separate table to hold the counter field:
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> create table counter2(a int not null, b int, primary key (a)) type = inno
db;
Query OK, 0 rows affected (0.35 sec)
mysql> update counter set b = b + 1 where a = 0;
ERROR 1054: Unknown column 'b' in 'field list'
mysql> update counter2 set b = b + 1 where a = 0;
Query OK, 1 row affected (0.39 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update counter2 set b = b + 1 where a = 0;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update counter2 set b = b + 1 where a = 0;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from counter where a = 0;
Empty set (0.06 sec)
mysql> select * from counter2 where a = 0;
+---+------+
| a | b |
+---+------+
| 0 | 3 |
+---+------+
1 row in set (0.00 sec)
Then you will not get a deadlock with another user, and neither will
you get gaps.
Regards,
Heikki
http://www.innobase.fi
At 04:39 PM 5/14/01 -0400, you wrote:
>
> 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
