Detect Transaction Database Deadlock

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


stfu8888Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
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
stfu8888Author Commented:
momi_sabag,

     Is this available in any of the ini file? Thanks.
0
stfu8888Author Commented:
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
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

momi_sabagCommented:
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
stfu8888Author Commented:
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
momi_sabagCommented:
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
stfu8888Author Commented:
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
stfu8888Author Commented:
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
momi_sabagCommented:
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
stfu8888Author Commented:
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
momi_sabagCommented:
try this
 set global table_lock_wait_timeout=2;
0
stfu8888Author Commented:
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
momi_sabagCommented:
ok
maybe you can't change it online, but you can change it in the my.ini file
innodb_lock_wait_timeout = 2
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stfu8888Author Commented:
momi_sabag,
        I will try it out. But what is the difference between "table_lock_wait_timeout" & "innodb_lock_wait_timeout"? Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.