MySQL Bugs: #6665: deadlock between odbc functions and transaction handling

Bug #6665	deadlock between odbc functions and transaction handling
Submitted:	16 Nov 2004 13:01	Modified:	30 May 2013 12:14
Reporter:	Robert Bagyinszki	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.06	OS:	Solaris (solaris 8)
Assigned to:	Matthew Lord	CPU Architecture:	Any

Description:
We are making a multithreaded application on a Solaris 8 system, with mysql
3.23 - InnoDB tables, myodbc 3.51.06, unixODBC2.2.10.
I made a testprogram, in which 2 threads wanted to make the same select in 'for update' mode. The first thread makes the select by calling SQLExecDirect method, which returns. The second one does the same, calls an SQLExecDirect method, which are waiting for the end of the transaction of the first thread. Meanwhile the first thread wants to read the fields by calling SQLGetData method. This method doesn't return immediate, but is waiting till the transaction in second thread is rollbacked because of timeout reached. I made a snapshot about the threads with dbx.
.For information about new features see `help changes'
To remove this message, put `dbxenv suppress_startup_message 7.0' in your
.dbxrc
Reading test_db_multi_r
Reading ld.so.1
Reading librt.so.1
Reading libkstat.so.1
Reading libodbc.so.1
Reading libCstd.so.1
Reading libCrun.so.1
Reading libm.so.1
Reading libw.so.1
Reading libthread.so.1
Reading libc.so.1
Reading libaio.so.1
Reading libdl.so.1
Reading libCstd_isa.so.1
Reading libc_psr.so.1
Reading libnsl.so.1
Reading libmp.so.2
Reading en_US.ISO8859-1.so.2
Reading nss_files.so.1
Reading UCS-2BE%ISO8859-1.so
Reading ISO8859-1%UCS-2BE.so
Reading libmyodbc3_r-3.51.06.so
Reading libsocket.so.1
Reading libodbcinst.so.1
detected a multithreaded program
Attached to process 9224 with 6 LWPs
t@1 (l@1) stopped in _lwp_sema_wait at 0xfef9f49c
0xfef9f49c: _lwp_sema_wait+0x0008:      ta      0x8
(dbx) lwps
 >l@1 running          in _lwp_sema_wait()
  l@2 running          in _signotifywait()
  l@3 running          in private___lwp_cond_wait()
  l@4 running          in __door_return()
  l@5 running          in _lwp_sema_wait()
  l@6 running          in _read()
(dbx) lwp l@4 
t@null (l@4) stopped in __door_return at 0xfef9c990
0xfef9c990: __door_return+0x000c:       ta      0x8
(dbx) where
=>[1] __door_return(), at 0xfef9c990
(dbx) lwp l@5
t@4 (l@5) stopped in _lwp_sema_wait at 0xfef9f49c
0xfef9f49c: _lwp_sema_wait+0x0008:      ta      0x8
(dbx) where
=>[1] _lwp_sema_wait(0xfedf1e60, 0x0, 0x0, 0x0, 0x0, 0x2), at 0xfef9f49c
  [2] _park(0xfedf1e60, 0xff06c000, 0x0, 0xfedf1d98, 0x0, 0x0), at
0xff0496dc
  [3] _swtch(0xfedf1d98, 0xfedf1d98, 0xff06c000, 0x5, 0x1000, 0x0), at
0xff0490d8
  [4] _mutex_adaptive_lock(0xff07785c, 0x4c00, 0x1000, 0xfffeffff, 0x1,
0x4d58), at 0xff04ada0
  [5] _cmutex_lock(0xff30fec0, 0xff06c000, 0x37e48, 0xff2a2174, 0xff2c5478,
0x0), at 0xff04aab4
  [6] SQLGetData(0x1a18d8, 0x2, 0xfff00000, 0xfedf1990, 0x0, 0x0), at
0xff2a2174
  [7] DBUtil::GetData(0x1a18d8, 0x2, 0xfffffff0, 0xfedf1990, 0x0, 0x0), at
0x5af1c
  [8] DBeCSubscription::CreateJournalEntry(0x1, 0xfedf1ba4, 0xfedf1b88,
0xfedf1ba4, 0xfedf1b6c, 0xfedf1b60), at 0x410e8
  [9] TestRunnable::run(0x17a5c8, 0x1676f4, 0x0, 0x0, 0x0, 0x0), at 0x3b134
  [10] sys::sctxThread::run(0x17d980, 0x1677ae, 0x0, 0x0, 0x0, 0x0), at
0x109574
  [11] sys::sctxThread::hiddenRun(0x17d980, 0x0, 0x0, 0x0, 0x0, 0x0), at
0x1099ac
  [12] staticStart(0x17d980, 0xff06d658, 0x1, 0x1, 0xff06c000, 0x0), at
0x109db0
(dbx) lwp l@6
t@5 (l@6) stopped in _read at 0xfef9e8ac
0xfef9e8ac: _read+0x0008:       ta      0x8
(dbx) where
=>[1] _read(0x6, 0x1ada80, 0x4, 0x0, 0xffffffffffffffff, 0x0), at 0xfef9e8ac
  [2] _ti_read(0x1ac908, 0xfec31314, 0x1adae5, 0x0, 0x45, 0x1a2fe8), at
0xff059fa8
  [3] my_net_read(0x1ac908, 0x1ada80, 0x0, 0x1ada80, 0xfffffc00,
0xfee33000), at 0xfee262b0
  [4] net_safe_read(0x1ac908, 0x3, 0x1a2f90, 0x60, 0x1, 0xff0000), at
0xfee222d8
  [5] mysql_read_query_result(0x1ac908, 0x1a2f90, 0x60, 0x0, 0x1ac900,
0xfee4e1b8), at 0xfee23390
  [6] do_query(0xffffffff, 0x1a2f90, 0x0, 0x0, 0x0, 0x0), at 0xfee18b88
  [7] my_SQLExecute(0xffffffff, 0x1a2ff0, 0x0, 0xff2c5478, 0x6, 0x0), at
0xfee1a0c8
  [8] SQLExecDirect(0x1b0060, 0x1a1868, 0xfffffffd, 0x0, 0xff2c5478, 0x0),
at 0xfee1a5f4
  [9] SQLExecDirect(0x1afa88, 0x1a1868, 0xfffffffd, 0x1ad068, 0xff305a2c,
0x1), at 0xff29e3bc
  [10] DBUtil::ExecuteSelect(0xfec31998, 0xfec31988, 0x1ad010, 0x1a343c,
0x14c380, 0x1ad010), at 0x5851c
  [11] DBeCSubscription::CreateJournalEntry(0x1, 0xfec31ba4, 0xfec31b88,
0xfec31ba4, 0xfec31b6c, 0xfec31b60), at 0x41060
  [12] TestRunnable::run(0x19f2d0, 0x1676f4, 0x0, 0x0, 0x0, 0x0), at 0x3b134
  [13] sys::sctxThread::run(0x17d9a0, 0x1677ae, 0x0, 0x0, 0x0, 0x0), at
0x109574
  [14] sys::sctxThread::hiddenRun(0x17d9a0, 0x0, 0x0, 0x0, 0x0, 0x0), at
0x1099ac
  [15] staticStart(0x17d9a0, 0xff06d658, 0x1, 0x1, 0xff06c000, 0x0), at
0x109db0
(dbx) quit

How to repeat:
Testprogram which executes concurrent database operations on two threads.

Suggested fix:
The execution of an odbc function should not be blocked till an other odbc function is running on an other thread.

Robert,

are you sure the 2 threads are using DIFFERENT connections to mysqld?

And that they are not, by accident, sharing the same statement object, etc.?

Regards,

Heikki

Heiiki,

Yes, my program traces out the values of handles of connection and statement, and they are different for the two threads.

Robert

Recently encountered what may be the same with 3.51.10 and unixODBC 2.2.10 on Linux.  Looking a little closer, my problem seemed to be with the locking level of unixODBC.  It has four locking levels, the default is '3' - which means the driver is 'protected at the env level'.  Changed it to '0', which means protect the internal driver manager structures only and assume the driver is MT-safe.  ('Threading = 0' in odbcinst.init).  I then no longer had a problem.

Hi Robert,

"Recently encountered what may be the same with 3.51.10 and unixODBC 2.2.10 on
Linux.  Looking a little closer, my problem seemed to be with the locking level
of unixODBC.  It has four locking levels, the default is '3' - which means the
driver is 'protected at the env level'.  Changed it to '0', which means protect
the internal driver manager structures only and assume the driver is MT-safe. 
('Threading = 0' in odbcinst.init).  I then no longer had a problem."

Did this also relieve the problem for you?

Best Regards

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.