Solved

Detect Transaction Database Deadlock

Posted on 2007-04-01
14
1,824 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now