Solved

Detect Transaction Database Deadlock

Posted on 2007-04-01
14
1,857 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

738 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