Comment 3 for bug 1303336

Hi Chris,

I have also tried with that option, where I have deliberately cause a deadlock and run above test. but couldn't able to reproduce your issue.

LATEST DETECTED DEADLOCK
------------------------
2014-07-29 14:45:13 7fe39d8f7700
*** (1) TRANSACTION:
TRANSACTION 3473, ACTIVE 4 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 52, OS thread handle 0x7fe39d8c6700, query id 1061 localhost root updating
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6 page no 5 n bits 144 index `PRIMARY` of table `dbtest`.`sbtest` trx id 3473 lock_mode X waiting
*** (2) TRANSACTION:
TRANSACTION 3471, ACTIVE 147 sec starting index read
mysql tables in use 1, locked 1
14536 lock struct(s), heap size 1537576, 2000834 row lock(s)
MySQL thread id 51, OS thread handle 0x7fe39d8f7700, query id 1062 localhost root Searching rows for update
update dbtest.sbtest set k = 0 where k <> 0
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6 page no 5 n bits 144 index `PRIMARY` of table `dbtest`.`sbtest` trx id 3471 lock mode S locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6 page no 5 n bits 144 index `PRIMARY` of table `dbtest`.`sbtest` trx id 3471 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)

cause a deadlock from another two sessions even during the test.

nilnandan@nilnandan-Dell-XPS:~$ sysbench --test=oltp --oltp-table-size=1000000 --oltp-test-mode=complex --oltp-read-only=off --num-threads=100 --max-time=180 --max-requests=0 --mysql-db=dbtest --mysql-user=root --mysql-password=root run
sysbench 0.4.12: multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 100

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting...
(last message repeated 99 times)
Done.

OLTP test statistics:
    queries performed:
        read: 1348802
        write: 480865
        other: 192451
        total: 2022118
    transactions: 96108 (533.69 per sec.)
    deadlocks: 235 (1.30 per sec.)
    read/write requests: 1829667 (10160.10 per sec.)
    other operations: 192451 (1068.68 per sec.)

Test execution summary:
    total time: 180.0835s
    total number of events: 96108
    total time taken by event execution: 18001.7449
    per-request statistics:
         min: 5.19ms
         avg: 187.31ms
         max: 106460.04ms
         approx. 95 percentile: 150.12ms

Threads fairness:
    events (avg/stddev): 961.0800/12.15
    execution time (avg/stddev): 180.0174/0.02

nilnandan@nilnandan-Dell-XPS:~$

Initially, innodb_row_lock_current_waits was 100 as my above sysbench test was stalled because I didn't commit the transactions on another session, when I commit, it become 0 and at the end it was 0.

mysql> show global status like 'innodb_row_lock_current_waits';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 100 |
+-------------------------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'innodb_row_lock_current_waits';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
+-------------------------------+-------+
1 row in set (0.01 sec)

mysql> show global status like 'innodb_row_lock_current_waits';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
+-------------------------------+-------+
1 row in set (0.00 sec)

mysql>