MySQL locking in Duplicate Key Error

lockingMySQLtransaction

From the docs:

If a duplicate-key error occurs, a shared lock on the duplicate index
record is set. This use of a shared lock can result in deadlock should
there be multiple sessions trying to insert the same row if another
session already has an exclusive lock. This can occur if another
session deletes the row.

Going with the example in the docs,

Suppose that an InnoDB table t1 has the following structure:

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

Now suppose that three sessions perform the following operations in order:

Session 1:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session 2:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session 3:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session 1:

ROLLBACK;

The first operation by session 1 acquires an exclusive lock for the
row. The operations by sessions 2 and 3 both result in a duplicate-key
error and they both request a shared lock for the row. When session 1
rolls back, it releases its exclusive lock on the row and the queued
shared lock requests for sessions 2 and 3 are granted. At this point,
sessions 2 and 3 deadlock: Neither can acquire an exclusive lock for
the row because of the shared lock held by the other.

I have some questions :

1) The insert query takes an exclusive lock on the row it is inserting. So, suppose T1 is inserting on row 1, it will lock row 1. Now when T2 comes to write, will INNODB evaluate the query before executing it and find out that it is going to write the same PK (row with i = 1) and make T2 wait? Or will it start execution of T2 and find that it gives duplicate key error or PK violation.

2) Why are T2 and T3 taking shared locks? How do shared locks come into picture during insert?

Best Answer

I did a small simulation of that situation, in a simple bash script that does that:

➜  rsandbox_5_6_30 cat test.sh 
./m -e "START TRANSACTION; INSERT INTO test1 VALUES(6); select  sleep(3); rollback;" test 2> tx1 &
./m -e "START TRANSACTION; INSERT INTO test1 VALUES(6); select sleep(5); commit;" test 2> tx2 &
./m -e "START TRANSACTION; INSERT INTO test1 VALUES(6); select sleep(5); commit;" test 2> tx3 &

./m -e "SHOW ENGINE INNODB STATUS\G" test > istatus

Result:

➜  rsandbox_5_6_30 cat tx1 
ERROR 1062 (23000) at line 1: Duplicate entry '6' for key 'PRIMARY'
➜  rsandbox_5_6_30 cat tx2
ERROR 1062 (23000) at line 1: Duplicate entry '6' for key 'PRIMARY'
➜  rsandbox_5_6_30 cat tx3

Transaction status:

---TRANSACTION 265035, not started
MySQL thread id 4, OS thread handle 0x700000b0b000, query id 204 localhost msandbox cleaning up
---TRANSACTION 265017, not started
MySQL thread id 3, OS thread handle 0x700000ac7000, query id 171 localhost msandbox cleaning up
---TRANSACTION 265041, ACTIVE 0 sec
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
MySQL thread id 31, OS thread handle 0x700000ca3000, query id 210 localhost msandbox User sleep
select sleep(5)

If you execute the first transaction within certain delay, you can incur on a frame where one of the subsequent transactions is killed by deadlock detection, making the other transaction to insert the value.

The status of the alive transaction reflects that still needs an explicit commit (undo log entry 1).

-- Edited 2nd

"Neither can acquire an exclusive lock for the row because of the shared lock held by the other." Actually the deadlock kills only 1 transaction, not both. So the EL is acquired by the 2nd session.

Due to how gap locking works we see the difference between executions. The deadlock happens as an intention of the 3rd transaction to acquire the shared lock when a duplicate key error is detected. When this happens, the 2nd transactions has the shared lock acquired, producing a deadlock and killing the 3rd transaction. This is a limitation in the amount of concurrent writes over the same record.

UPDATE 2014-12-10 15:12 EST

Your comment

Thx for the answer, and it does help, but the main point of the OP was to determine if there is a benefit for using SELECT INTO OUTFILE over INSERT INTO ... SELECT?

They are operationally different

SELECT INTO OUTFILE creates a text file
INSERT INTO SELECT loads one table from the results of the SELECT

UPDATE 2014-12-11 12:21 EST

The only thing I can think of in this context is the point-in-time of the data and when you are using it. With both types of operations, there will some implicit shared locking.

With SELECT INTO OUTFILE, you are preparing a result and saving it externally. Loading that data into a table using LOAD DATA INFILE will not involve any shared locking during the load process. Keep in mind that SELECT INTO OUTFILE will incur disk I/O and still impose some caching along the way.

With INSERT INTO SELECT, the shared locks would probably have to live longer in InnoDB because you are locking rows and using those same rows to INSERT into another table.

Therefore, if I was looking for a performance bonus, I would have give the edge to INSERT INTO SELECT because you are doing the same amount of shared row locking, The disk I/O for the single operation would have to be less than the separate SELECT INTO OUTFILE and subsequent LOAD DATA INFILE. Of course, you would have to compare the two methods against your dataset. What could a performance bonus for one dataset might be a performance cost for another dataset.

UPDATE 2014-12-17 00:00 EST

Your Comment

I wasn't notified that you updated your answer, so I did bounty assuming it wasn't. Your explanation does make sense, in theory, however I am looking for a more authoritative response, in hopes that the overhead of the separate file (as you correctly mentioned) may be worth the trade-off in complexity for improved performance.

The only authoritative response would to come from the MySQL Documentation.

First, what does the MySQL Documentation LOAD DATA INFILE say ?

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. LOAD DATA INFILE is the complement of SELECT ... INTO OUTFILE. (See Section 13.2.9.1, “SELECT ... INTO Syntax”.) To write data from a table to a file, use SELECT ... INTO OUTFILE. To read the file back into a table, use LOAD DATA INFILE.

Two paragraphs later, it says

For more information about the efficiency of INSERT versus LOAD DATA INFILE and speeding up LOAD DATA INFILE, see Section 8.2.2.1, “Speed of INSERT Statements”.

When you look at Speed of INSERT Statements, it says this:

To optimize insert speed, combine many small operations into a single large operation. Ideally, you make a single connection, send the data for many new rows at once, and delay all index updates and consistency checking until the very end.

The time required for inserting a row is determined by the following factors, where the numbers indicate approximate proportions:

Connecting: (3)

Sending query to server: (2)

Parsing query: (2)

Inserting row: (1 × size of row)

Inserting indexes: (1 × number of indexes)

Closing: (1)

This does not take into consideration the initial overhead to open tables, which is done once for each concurrently running query.

The size of the table slows down the insertion of indexes by log N, assuming B-tree indexes.

You can use the following methods to speed up inserts:

If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements. If you are adding data to a nonempty table, you can tune the bulk_insert_buffer_size variable to make data insertion even faster. See Section 5.1.4, “Server System Variables”.

When loading a table from a text file, use LOAD DATA INFILE. This is usually 20 times faster than using INSERT statements. See Section 13.2.6, “LOAD DATA INFILE Syntax”.

Take advantage of the fact that columns have default values. Insert values explicitly only when the value to be inserted differs from the default. This reduces the parsing that MySQL must do and improves the insert speed.

See Section 8.5.4, “Bulk Data Loading for InnoDB Tables” for tips specific to InnoDB tables.

See Section 8.6.2, “Bulk Data Loading for MyISAM Tables” for tips specific to MyISAM tables.

Things start to look a little nebulous at this point because you must tune the load process in terms of the storage engine. MyISAM is rather straight forward in this statement because the bulk insert buffer is for MyISAM only and LOAD DATA INFILE will harness the bulk insert buffer. InnoDB will not.

Take a look at this Pictorial Representation of InnoDB (Percona CTO Vadim Tchachenko)

InnoDB Architecture

There are other considerations to tweak options but LOAD DATA INFILE will literally slam everything into the InnoDB Buffer Pool, funneling changes through the Log Buffer, Double Write Buffer, Insert Buffer (if the target table has nonunique indexes), Redo Logs (ib_logfile0,ib_logfile1), and the Physical File of the Table. This is where LOAD DATA INFILE's benefits have to get nullified.

I wrote about this

Feb 06, 2012 : LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the "logging slow query" process?
Apr 20, 2012 : Why is 'LOAD DATA INFILE' faster than normal INSERT statements?
Jul 13, 2012 : Mysql load from infile stuck waiting on hard drive
Jan 11, 2013 : MySQL LOAD DATA INFILE slows by 80% after a few gigs of input with InnoDB engine

EPILOGUE

As I already said in my previous update to this answer

Therefore, if I was looking for a performance bonus, I would have give the edge to INSERT INTO SELECT because you are doing the same amount of shared row locking, The disk I/O for the single operation would have to be less than the separate SELECT INTO OUTFILE and subsequent LOAD DATA INFILE. Of course, you would have to compare the two methods against your dataset. What could a performance bonus for one dataset might be a performance cost for another dataset.

Basically, you will have to test SELECT INTO OUTFILE/LOAD DATA INFILE against INSERT INTO SELECT. It may be 6 of one, half a dozen of the other for one dataset, and a landside victory for another dataset.

All being said from the MySQL Docs and my past posts, I still give the edge to INSERT INTO SELECT. You will just have to test the two methods.

Mysql – Clarification on thesql deadlock situation

Your assumption is correct. Nevertheless, you need to pay attention to what is being locked.

Both transactions are locking

space id 10787
page no 139
n bits 176
index PERMISSION_ID of table IDENTITY.ROLE

The DB Connections 12287 and 12279 ran one INSERT each

Here is what I notice: In each INSERT, you are running SELECT DOMAIN_ID FROM DOMAIN WHERE TENANT_ID=1 AND DOMAIN_NAME='...' to fill in DOMAIN_ID. This would cause additional locks because a query's result is needed to fill DOMAIN_ID while other indexes are made to wait for the materialization of ROLE's primary key. This deadlock lasts a little longer that normal because of this intermittent need to run a SELECT.

SUGGESTION

Instead of doing

INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4188, 'everyone',
(SELECT DOMAIN_ID FROM DOMAIN WHERE TENANT_ID=1 AND DOMAIN_NAME='INTERNAL'));

you should retrieve the DOMAIN_ID separately into a variable and then use it in the INSERT

SELECT DOMAIN_ID INTO @domain_id FROM DOMAIN
WHERE TENANT_ID=1 AND DOMAIN_NAME='INTERNAL';
INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4188,'everyone',@domain_id);

This might reduce the need to pile up locks