This is a follow up on my previous question (you can skip it as I explain in this post the issue):  

[MySQL InnoDB SELECT...LIMIT 1 FOR UPDATE Vs UPDATE ... LIMIT 1](https://stackoverflow.com/questions/14039853/mysql-innodb-select-limit-1-for-update-vs-update-limit-1)

Environment:

* JSF 2.1 on Glassfish
* JPA 2.0 EclipseLink and JTA
* MySQL 5.5 InnoDB engine

I have a table:

```
CREATE TABLE v_ext (
  v_id INT NOT NULL AUTO_INCREMENT,
  product_id INT NOT NULL,
  code VARCHAR(20),
  username VARCHAR(30),
  PRIMARY KEY (v_id)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

```

It is populated with 20,000 records like this one (`product_id` is 54 for all records, `code` is randomly generated and unique, username is set to NULL):

```
v_id     product_id    code                  username
-----------------------------------------------------
1        54            '20 alphanumerical'   NULL
...
20,000   54            '20 alphanumerical'   NULL

```

When a user purchase product 54, he gets a code from that table. If the user purchases multiple times, he gets a code each times (no unique constraint on username). Because I am preparing for a high activity I want to make sure that:

* No concurrency/deadlock can occur
* Performance is not impacted by the locking mechanism which will be needed

From the SO question (see link above) I found that doing such a query is faster:

```
START TRANSACTION;
SELECT v_id FROM v_ext WHERE username IS NULL LIMIT 1 FOR UPDATE;
// Use result for next query
UPDATE v_ext SET username=xxx WHERE v_id=...;
COMMIT;

```

However I found a deadlock issue ONLY when using an index on `username` column. I thought of adding an index would help in speeding up a little bit but it creates a deadlock after about 19,970 records (actually quite consistently at this number of rows). Is there a reason for this? I don't understand. Thank you.