MySQL Performance: MySQL => 2 Columns AUTO-INCREMENT emulation in InnoDB

Members

Help

Home

Home » Performance » MySQL » 2 Columns AUTO-INCREMENT emulation in InnoDB

Show: Today's Messages :: Show Polls :: Message Navigator

2 Columns AUTO-INCREMENT emulation in InnoDB [message #57]

Wed, 16 August 2006 06:21

Speeple
Messages: 91
Registered: August 2006

Member

Hi,

In MyISAM you can set 2 columns to have auto-increment properties.

So, in a messaging system you could have:

user_id, message_id

INSERT INTO messages (user_id, message) VALUES (1, 'My Message');
INSERT INTO messages (user_id, message) VALUES (1, 'My Message');
INSERT INTO messages (user_id, message) VALUES (1, 'My Message');

A SELECT * would produce

user_id, message_id
1,1
1,2
1,3

InnoDB does not currently support this, so I've done the following work around:

INSERT INTO messages (user_id, message_id, message) VALUES (1, MAX(message_id), 'My Message') WHERE user_id=1;

Are there any known problems with self-implementing support this way?

It appears to be working fine in the development enviroment, however with thousands of users... might be a different story!

Cheers

[Updated on: Wed, 16 August 2006 06:21]

Martin Gallagher | Speeple: The latest news

Report message to a moderator

Re: 2 Columns AUTO-INCREMENT emulation in InnoDB [message #60 is a reply to message #57 ]

Wed, 16 August 2006 06:58

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

I assume you mean:

INSERT INTO messages values(1,(select max(message_id)+1 from messages where user_id=1),'message')

This is actually similar to what MyISAM does to provide two column auto_increment.

This should work if subselect in insert statement is executed using read-commited repeatable mode, which it should be.

Also if you have enough users it is better than global auto_increment from contention standpoint.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: 2 Columns AUTO-INCREMENT emulation in InnoDB [message #63 is a reply to message #57 ]

Wed, 16 August 2006 07:13

Speeple
Messages: 91
Registered: August 2006

Member

Yes the actual query is like:

INSERT INTO messages (author, recipient, id, subject, body, checksum, timestamp) SELECT $user[id], @tmp:=author, (SELECT MAX(id) + 1 FROM messages WHERE ...

Martin Gallagher | Speeple: The latest news

Report message to a moderator

Re: 2 Columns AUTO-INCREMENT emulation in InnoDB [message #84 is a reply to message #57 ]

Sun, 20 August 2006 15:24

rhuddleston
Messages: 11
Registered: August 2006

Junior Member

Problem is you can't do a subselect from the same table your inserting into in mysql. It would be nice if you could but it's currently not supported.

Report message to a moderator

Re: 2 Columns AUTO-INCREMENT emulation in InnoDB [message #85 is a reply to message #84 ]

Sun, 20 August 2006 15:34

Speeple
Messages: 91
Registered: August 2006

Member

rhuddleston wrote on Sun, 20 August 2006 15:24

Problem is you can't do a subselect from the same table your inserting into in mysql. It would be nice if you could but it's currently not supported.

The above query (in it's entirety) works perfectly in MySQL 5.0.22.

Martin Gallagher | Speeple: The latest news

Report message to a moderator

Re: 2 Columns AUTO-INCREMENT emulation in InnoDB [message #86 is a reply to message #85 ]

Sun, 20 August 2006 16:56

rhuddleston
Messages: 11
Registered: August 2006

Junior Member

I guess I was refering to the insert statement Peter had:

INSERT INTO messages values(1,(select max(message_id)+1 from messages where user_id=1),'message');

Your right the insert..select would work though.

I did some testing and the only issue that I found when using the insert...select was deadlocks.

e.g.

create table foo (message_id int auto_increment, unique key foo$message_id (message_id)) engine=innodb;
insert into foo values ();

Then I started two while loops running:

insert into foo select max(message_id)+1 from foo;

I would randomly get deadlocks in this setup:

ERROR 1213 (40001) at line 1: Deadlock found when trying to get lock; try restarting transaction

This problem may go away with row based binlogging in 5.1 as it set's fewer locks for insert..select statements. But if there we're no locks there might instead get duplicate key errors in my same test.

Report message to a moderator

Re: 2 Columns AUTO-INCREMENT emulation in InnoDB [message #87 is a reply to message #86 ]

Sun, 20 August 2006 17:18

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

Yeah,

Thanks for comment Ryan. This is stupid limitation in my oppinion. It was fixed for INSERT ... SELECT (Was not it done on your request ?) by utilizing temporary table.

Speaking about deadlocks - yest it will deadlock if you use it for full table as in your case but in case you have auto-increment colums for different user ids it will only have chance of deadlock if multiple inserts are done for same user.... which is much less probable in many setups.

If it is still happens too often and you do not like deadlocks in your application you can use GET_LOCK to serialize queries for each user to avoid deadlocks.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: 2 Columns AUTO-INCREMENT emulation in InnoDB [message #419 is a reply to message #87 ]

Fri, 24 November 2006 06:14

Julegner
Messages: 2
Registered: November 2006

Junior Member

If I try to do a Query like

insert into test values (100,1,(select ifnull(max(ID)+1,1) from test where projektnummer = 100 and dbnr = 1),'Testtext')

in MySQL with InnoDB, I get the Error 1093:
"You can't specify target table 'test' for update in FROM clause"

Report message to a moderator

Re: 2 Columns AUTO-INCREMENT emulation in InnoDB [message #420 is a reply to message #419 ]

Fri, 24 November 2006 07:05

Speeple
Messages: 91
Registered: August 2006

Member

Julegner wrote on Fri, 24 November 2006 06:14

Yes, it doesn't work in that context.

Try it like so:

INSERT INTO test (...) SELECT 100,1,(select ifnull(max(ID)+1,1) from test where projektnummer = 100 and dbnr = 1), 'Testtext';

Martin Gallagher | Speeple: The latest news

Report message to a moderator

Re: 2 Columns AUTO-INCREMENT emulation in InnoDB [message #421 is a reply to message #420 ]

Fri, 24 November 2006 07:22

Julegner
Messages: 2
Registered: November 2006

Junior Member

Thanks a lot!

If I do it in this way, it is working:

insert into test (projektnummer,dbnr,ID,text) select 100,1,(select ifnull(max(ID)+1,1) from test where projektnummer = 100 and dbnr = 1),'testtext';

Report message to a moderator

Re: 2 Columns AUTO-INCREMENT emulation in InnoDB [message #1558 is a reply to message #87 ]

Wed, 25 July 2007 17:42

Spider
Messages: 1
Registered: July 2007

Junior Member

Sorry for replying to an old thread, but I'm looking at the same issue. Is it really faster to issue the INSERT ... SELECT with the GET_LOCK, than to just Lock the table get the max Id and insert it ? I would think that the temp table would be worse than issuing a second query.

Report message to a moderator