Announcement

Announcement Module
Collapse
No announcement yet.

2 Columns AUTO-INCREMENT emulation in InnoDB

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 2 Columns AUTO-INCREMENT emulation in InnoDB

    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

  • #2
    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.

    Comment


    • #3
      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 ...

      Comment


      • #4
        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.

        Comment


        • #5
          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.

          Comment


          • #6
            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.

            Comment


            • #7
              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.

              Comment


              • #8
                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"

                Comment


                • #9
                  Julegner wrote on Fri, 24 November 2006 06:14

                  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"


                  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';

                  Comment


                  • #10
                    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';

                    Comment


                    • #11
                      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.

                      Comment


                      • #12
                        Hello everyone. Nice to meet you all. D


                        pret auto

                        Comment

                        Working...
                        X