Solved

Detect Transaction Database Deadlock

Posted on 2007-04-01
14
1,848 Views
Last Modified: 2013-12-25
Hello Experts,

          Question. Here is the scenario... I use the MySQL command line client to execute the following SQL query.

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE t SET f = 111;
Rows matched: 1  Changed: 1  Warnings: 0

          Then I leave the MySQL command client as it is. I have not commited or rollback yet. Then I run my VB application which runs another tranaction database query. The code are as shown below.

CODE
=====
Public Sub TestTransaction()
  Dim connX As ADODB.Connection
 
  Set connX = New ADODB.Connection
  connX.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
                           "SERVER=localhost;DATABASE=test;" & _
                           "USER=root;PASSWORD=root;OPTION=3;"
  connX.CursorLocation = adUseClient
  connX.Open
  connX.Execute "BEGIN;"
  connX.Execute "UPDATE t SET f = 222;"
  connX.Execute "COMMIT;"
  connX.Close
  Set connX = Nothing
End Sub

          The VB application hangs and after 50secs (approximately), a Windows pop-up message appears saying.

Run-time error '-2147217871 (80040e31)':
Lock wait timeout exceeded; try restarting transaction

          But if I commit or rollback in the MySQL command line client before the 50 seconds expires. The VB application's transaction database is processed. I know for the fact that the VB application's transaction query is on a queue. Is there a way to reduce this wait time to say 2 seconds? Or is there a way to detect this deadlock? Thanks


0
Comment
Question by:stfu8888
  • 8
  • 6
14 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18834683
Hi

there is a system variable called table_lock_wait_timeout  and it's default value is 50 seconds
this variable deterimes the amount of time a transaction will wait on a table lock
you can change it from the command line client like this
@@table_lock_wait_timeout = 2
this will set it on 2 seconds
you can read here about other ways to change it
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

anyway, there is no way you can detect it from the application
this is not a deadlock situation but a timeout situation
a deadlock is when 2 or more trasnactions are waiting for each other in a circular manner
a timeout is when one transaction is waiting for another

momi
0
 

Author Comment

by:stfu8888
ID: 18836119
momi_sabag,

     Is this available in any of the ini file? Thanks.
0
 

Author Comment

by:stfu8888
ID: 18836369
Hummmmm.....

I tried running the following query and she does not seems to work.

SELECT @@table_lock_wait_timeout;
ERROR1193: Unknown system variale 'table_lock_wait_timeout'

SELECT @@innodb_lock_wait_timeout;
ERROR1193: Unknown system variale 'innodb_lock_wait_timeout'

      By the way I am using MySQL Server 4.1 and  MySQL Control Center to access the database. Is the MySQL Server version causing this issue? That is, is this function only available in version 5.0 and up? Thanks.
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 37

Expert Comment

by:momi_sabag
ID: 18836686
i looked at the mysql 5 reference so i guess this might be the problem
you can enter this command from the command line client
show variables like '%lock%'
to see which variables may be  relevant here (maybe it had a different name in the past)
anyway, i believe this variable can be configured in the ini file
you can follow the link i posted earlier to see how to put it in the file
0
 

Author Comment

by:stfu8888
ID: 18838324
momi,
     I tried this but it is not work...
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 34 to server version: 4.1.22-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like '%lock%';
+--------------------------------+------------+
| Variable_name                  | Value      |
+--------------------------------+------------+
| innodb_lock_wait_timeout       | 50         |
| innodb_locks_unsafe_for_binlog | OFF        |
| innodb_table_locks             | ON         |
| key_cache_block_size           | 1024       |
| max_write_lock_count           | 4294967295 |
| query_alloc_block_size         | 8192       |
| query_cache_wlock_invalidate   | OFF        |
| range_alloc_block_size         | 2048       |
| skip_external_locking          | ON         |
| transaction_alloc_block_size   | 8192       |
+--------------------------------+------------+
10 rows in set (0.00 sec)

mysql> SELECT innodb_lock_wait_timeout;
ERROR 1054 (42S22): Unknown column 'innodb_lock_wait_timeout' in 'field list'
mysql>
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18839858
so the parameter is innodb_lock_wait_timeout  
and as you can see it's current value is 50
just change it by issuing
@@innodb_lock_wait_timeout  = 2
from the command line client
0
 

Author Comment

by:stfu8888
ID: 18840154
I see that I can't query for it with the following query.

SELECT innodb_lock_wait_timeout;

Is that true? Will upgrading my MySQL Server from 4.01 to MySQL server 5.0 help?
Also can I run the following command from the VB application.

conn.Execute "@@innodb_lock_wait_timeout  = 2"

Thanks.
0
 

Author Comment

by:stfu8888
ID: 18843462
momi_sabag,

       I tried the command and it does not work. Here are the steps.

Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.1.22-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like '%lock%';
+--------------------------------+------------+
| Variable_name                  | Value      |
+--------------------------------+------------+
| innodb_lock_wait_timeout       | 50         |
| innodb_locks_unsafe_for_binlog | OFF        |
| innodb_table_locks             | ON         |
| key_cache_block_size           | 1024       |
| max_write_lock_count           | 4294967295 |
| query_alloc_block_size         | 8192       |
| query_cache_wlock_invalidate   | OFF        |
| range_alloc_block_size         | 2048       |
| skip_external_locking          | ON         |
| transaction_alloc_block_size   | 8192       |
+--------------------------------+------------+
10 rows in set (0.02 sec)

mysql> @@innodb_lock_wait_timeout=2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '@@inn
odb_lock_wait_timeout=2' at line 1

mysql> set @@innodb_lock_wait_timeout=2;
ERROR 1193 (HY000): Unknown system variable 'innodb_lock_wait_timeout'
mysql>
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18843485
well
sorry

i'm not that familiar with version 4
if you upgrade to version 5 i believe it will be solved
anyway, did you try to add it to the parameter file ?
i don't think you would be able to do it from the vb code
0
 

Author Comment

by:stfu8888
ID: 18843875
momi_sabag,

      I just completed upgrading to MySQL Server 5.0 and I still can't run "@@innodb_lock_wait_timeout=2;". It gives me the same error.
     Which file should I incorporate "innodb_lock_wait_timeout" into? "my.ini", "my-innodb-heavy-4G.ini", "my-huge.ini" or etc.?
     
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18843989
try this
 set global table_lock_wait_timeout=2;
0
 

Author Comment

by:stfu8888
ID: 18847744
momi_sabag,

         Looks like it is able to change the "table_lock_wait_timeout" but not "innodb_lock_wait_timeout". Changing the "table_lock_wait_timeout" does not reduce the wait time. I still had to wait for 50 seconds before the timeout message is displayed. And Looks like I cannot change the "innodb_lock_wait_timeout" too.



Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.0.37-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like '%lock%';
+--------------------------------+------------+
| Variable_name                  | Value      |
+--------------------------------+------------+
| innodb_lock_wait_timeout       | 50         |
| innodb_locks_unsafe_for_binlog | OFF        |
| innodb_table_locks             | ON         |
| key_cache_block_size           | 1024       |
| max_write_lock_count           | 4294967295 |
| query_alloc_block_size         | 8192       |
| query_cache_wlock_invalidate   | OFF        |
| range_alloc_block_size         | 2048       |
| skip_external_locking          | ON         |
| table_lock_wait_timeout        | 50         |
| transaction_alloc_block_size   | 8192       |
+--------------------------------+------------+
11 rows in set (0.00 sec)

mysql> set global table_lock_wait_timeout=2;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%lock%';
+--------------------------------+------------+
| Variable_name                  | Value      |
+--------------------------------+------------+
| innodb_lock_wait_timeout       | 50         |
| innodb_locks_unsafe_for_binlog | OFF        |
| innodb_table_locks             | ON         |
| key_cache_block_size           | 1024       |
| max_write_lock_count           | 4294967295 |
| query_alloc_block_size         | 8192       |
| query_cache_wlock_invalidate   | OFF        |
| range_alloc_block_size         | 2048       |
| skip_external_locking          | ON         |
| table_lock_wait_timeout        | 2          |
| transaction_alloc_block_size   | 8192       |
+--------------------------------+------------+
11 rows in set (0.00 sec)

mysql> set global table_lock_wait_timeout=50;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%lock%';
+--------------------------------+------------+
| Variable_name                  | Value      |
+--------------------------------+------------+
| innodb_lock_wait_timeout       | 50         |
| innodb_locks_unsafe_for_binlog | OFF        |
| innodb_table_locks             | ON         |
| key_cache_block_size           | 1024       |
| max_write_lock_count           | 4294967295 |
| query_alloc_block_size         | 8192       |
| query_cache_wlock_invalidate   | OFF        |
| range_alloc_block_size         | 2048       |
| skip_external_locking          | ON         |
| table_lock_wait_timeout        | 50         |
| transaction_alloc_block_size   | 8192       |
+--------------------------------+------------+
11 rows in set (0.00 sec)

mysql> set global innodb_lock_wait_timeout=2;
ERROR 1193 (HY000): Unknown system variable 'innodb_lock_wait_timeout'
mysql>


0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 250 total points
ID: 18848687
ok
maybe you can't change it online, but you can change it in the my.ini file
innodb_lock_wait_timeout = 2
0
 

Author Comment

by:stfu8888
ID: 18858096
momi_sabag,
        I will try it out. But what is the difference between "table_lock_wait_timeout" & "innodb_lock_wait_timeout"? Thanks.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question