1

I have a table like: idx (PK) clmn_1

Both are INTs. idx is not defined as auto-increment, but I am trying to simulate it. To insert into this table, I am using:

"INSERT INTO  my_tbl (idx, clmn_1)   \
 SELECT IFNULL(MAX(idx), 0) + 1, %s  \
 FROM my_tbl", val_clmn_1

Now, this works. The query that I have is about atomicity. Since I read and then insert to the same table, when multiple inserts happen simultaneous can there potentially be a duplicate-key error?

And, how can I test it myself?

I am using Percona XtraDB server 5.5.

Ethan
  • 4,915
  • 1
  • 28
  • 36

1 Answers1

2

This is not a good solution, because it creates a shared lock on my_tbl while it's doing the SELECT. Any number of threads can have a shared lock concurrently, but it blocks concurrent write locks. So this causes inserts to become serialized, waiting for the SELECT to finish.

You can observe this lock. Start this query in one session:

INSERT INTO  my_tbl (idx, clmn_1) 
 SELECT IFNULL(MAX(idx), 0) + 1, 1234+SLEEP(60) 
 FROM my_tbl;

Then go to another session and run innotop and view the locking screen (press key 'L'). You'll see output like this:

___________________________________ InnoDB Locks ___________________________________
ID  Type    Waiting  Wait   Active  Mode  DB    Table   Index    Ins Intent  Special
61  TABLE         0  00:00   00:00  IS    test  my_tbl                    0         
61  RECORD        0  00:00   00:00  S     test  my_tbl  PRIMARY           0

This is why the auto-increment mechanism works the way it does. Regardless of transaction isolation, the insert thread locks the table briefly only to increment the auto-inc number. This is extremely quick. Then the lock is released, allowing other threads to proceed immediately. Meanwhile, the first thread attempts to finish its insert.

See http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html for more details about auto-increment locking.

I'm not sure why you want to simulate auto-increment behavior instead of just defining the column as an auto-increment column. You can change an existing table to be auto-incrementing.

Re your comment:

Even if a PK is declared as auto-increment, you can still specify a value. The auto-incrementation only kicks in if you don't specify the PK column in the INSERT, or you specify NULL or DEFAULT as its value.

CREATE TABLE foo (id INT AUTO_INCREMENT PRIMARY KEY, c CHAR(1));
INSERT INTO foo (id, c) VALUES (123, 'x'); -- inserts value 123
INSERT INTO foo (id, c) VALUES (DEFAULT, 'y'); -- inserts value 124
INSERT INTO foo (id, c) VALUES (42, 'n'); -- inserts specified value 42
INSERT INTO foo (c) VALUES ('Z'); -- inserts value 125
REPLACE INTO foo (id, c) VALUES (125, 'z'); -- changes existing row with id=125

Re your comment:

START TRANSACTION; 
SELECT IFNULL(MAX(idx), 0)+1 FROM my_tbl FOR UPDATE; 
INSERT INTO my_tbl (idx, clmn_1) VALUES (new_idx_val, some_val); 
COMMIT;

This is actually worse than your first idea, because now the SELECT...FOR UPDATE creates an X lock instead of an S lock.

You should really not try to re-invent the behavior of AUTO-INCREMENT, because any SQL solution is limited by ACID properties. Auto-inc necessarily works outside of ACID.

If you need to correct existing rows atomically, use either REPLACE or INSERT...ON DUPLICATE KEY UPDATE.

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • I was thinking of this as in the backend where I populate a few tables to eventually migrate to the remote webserver, I wanted a sequencing of the idx, without holes. Because it's a new backend s/w and there will be a few operators trying to update that table, if I find some mistakes in data entry, I wanted to delete those rows and add the corrected data. I don't know how to do this with auto-increment apart from hard coding the idx value, which will be very difficult to do. – Ethan Mar 02 '13 at 19:58
  • Yes, I was aware of this feature of auto-inc. But this means manually inserting in the table. I actually wanted to avoid that -- delete those entries manually and again add them via APIs. – Ethan Mar 03 '13 at 21:48
  • What do you think of 'START TRANSACTION; SELECT IFNULL(MAX(idx), 0)+1 FROM my_tbl FOR UPDATE; INSERT INTO my_tbl (idx, clmn_1) VALUES (new_idx_val, some_val); COMMIT;'. I find that this will sequence concurrent clients as it takes the X lock, but will not have the duplicate-key error as my original version has. It will be slow than auto-inc, but that's fine as I will be using this in the backend and on the remote webserver, this table is read-only (writes only on updates). – Ethan Mar 03 '13 at 21:53
  • 1
    "I wanted a sequencing of the idx, without holes" If you ever care about what values get used for inserting on a primary key you are doing it wrong and will eventually regret your choice. If you need to insert data to be the same on a live server as on a dev server, you should make a script to create that data, and then run it as part of the deployment process. Never hard code things to expect certain primary keys to be used. – Danack Mar 04 '13 at 03:13
  • @BillKarwin Thanks Bill for your help and the link to INSERT REPLACE. – Ethan Mar 04 '13 at 23:02
  • @Danack That's a good advice. I realized that my design was at fault. However, I have learnt much on this query. – Ethan Mar 04 '13 at 23:06