oltp: inserts duplicate keys when using multiple threads

[cruppstahl]

I use sysbench 1.0, and run oltp.lua with the following options:

--max-time=60 --max-requests=1000000 --num-threads=32 --oltp-table-size=1000000 

Sometimes (but not always) I get the following message:

ALERT: mysql_drv_query() returned error 1062 (Duplicate entry '500070' for key 'PRIMARY') for query 'INSERT INTO sbtest1 (id, k, c, pad) VALUES (500070, 502834, '52973511997-35955633476-77867639113-31616279923-77804179716-57875960734-64511142178-43524978742-90538254054-86456664868', '76447610835-89507148237-86713965845-06775351687-05670492505')'
FATAL: failed to execute function `event': 3

This error only appears when I use multiple threads. With just one thread everything works as expected.

In oltp.lua I found this:

i = sb_rand(1, oltp_table_size)
...
rs = db_query("INSERT INTO " .. table_name ..  " (id, k, c, pad) VALUES " .. string.format("(%d, %d, '%s', '%s')",i, sb_rand(1, oltp_table_size) , c_val, pad_val))

From what I understand, each thread creates a random ID, but how do you make sure that they are always unique?

(Disclaimer: I am not using InnoDB but my own storage engine. I have never seen this behaviour with InnoDB. I'm trying to understand what exactly happends so I can figure out if the problem is in my storage engine or in sysbench.)

[cruppstahl]

OK - i noticed that the key is deleted before it is re-inserted, and therefore there should never be a duplicate.
I'll review my "delete" function - looks like it is buggy.

[akopytov]

The conflict occurs on the primary key. You are right, the INSERT statement in oltp.lua immediately follows a DELETE statement with the same PK value (i). This way we make sure each transaction is autonomous, i.e. we won't be trying to insert duplicate keys in a single session.

With multiple concurrent sessions nothing guarantees multiple rows with the same IDs cannot be generated by concurrent transactions. In that case, conflicting transactions fail as they should, and sysbench tries to detect such failures and restarts the failed transactions. But exact behavior is implementation-specific, so it's not always detected by sysbench correctly.

MySQL/InnoDB would fail with a deadlock error or a lock wait timeout, and sysbench handles those in the MySQL driver (drv_mysql.c).

PostgreSQL fails with a duplicate key error in such cases due to a different MVCC behavior. See issue #10, especially links to StackExchange discussions for all the gory details. I fixed it by making the PostgreSQL driver handle duplicate key errors by restarting transactions rather than failing the benchmark.

But the problem you are facing brings up a valid issue with the current approach: a non-InnoDB storage engine for MySQL may have a different MVCC behavior. For example, a PostgreSQL-like behavior, which I guess is the case here.

I'm going to reopen the issue and commit an experimental fix for you to test. Thank you.

[cruppstahl]

My storage engine does not yet properly implement transactions. This leads to a race condition in oltp.lua, when two threads try to insert the same key:

• thread 1 deletes the primary key
• thread 2 deletes the primary key
• thread 2 inserts the primary key
• thread 1 inserts the primary key (fails because it already exists)

A cheap way to fix this would be to make sure that each thread only has its own range of primary keys.

If you come up with a fix then I'll test it. Thanks for all your efforts!

[akopytov]

Oh, I see. In this case you have two opions:

• have sysbench treat your engine as a non-transactional one (like MyISAM) and serialize all concurrent transactions with LOCK TABLES:

--- a/sysbench/tests/db/oltp.lua
+++ b/sysbench/tests/db/oltp.lua
@@ -5,7 +5,7 @@ dofile(pathtest .. "common.lua")
 function thread_init(thread_id)
    set_vars()

-   if (((db_driver == "mysql") or (db_driver == "attachsql")) and mysql_table_engine == "myisam") then
+   if (((db_driver == "mysql") or (db_driver == "attachsql")) and (mysql_table_engine == "myisam") or mysql_table_engine == "yourengine") then
       begin_query = "LOCK TABLES sbtest WRITE"
       commit_query = "UNLOCK TABLES"
    else

• or, generate globally unique IDs for the DELETE/INSERT combo:

diff --git a/sysbench/tests/db/oltp.lua b/sysbench/tests/db/oltp.lua
index 985ca0c..9c87091 100644
--- a/sysbench/tests/db/oltp.lua
+++ b/sysbench/tests/db/oltp.lua
@@ -78,7 +78,7 @@ function event(thread_id)

    for i=1, oltp_delete_inserts do

-   i = sb_rand(1, oltp_table_size)
+   i = sb_rand_uniq(1, oltp_table_size)

    rs = db_query("DELETE FROM " .. table_name .. " WHERE id=" .. i)

[cruppstahl]

The first solution (using LOCK TABLES) works, but i had to change

   `begin_query = "LOCK TABLES sbtest WRITE"`

to

   `begin_query = "LOCK TABLES sbtest1 WRITE"`

Your second solution (using sb_rand_uniq) also works. I'll use this as a
workaround till I have implemented proper transaction support.

Thanks for your help!

[akopytov]

Thanks for the update. I'll fix the LOCK TABLES statement in oltp.lua just in case.