REQTIMEOUT in lockstatistics & db deadlocks detection 2004-03-16 - By Ajit Aranha
Back
Hi,
The scenario under which i have deadlock is the following. (kernel
ver 7.3.30 win32)
Ten thread Odbc (committed) inserting 20000 rows a minute into a table.
1 thread (jdbc TRANSACTION_READ_UNCOMMITTED) doing a select every two
minutes. on the table
5 threads running updates that change multiple rows. (autocommited
default jdbc settings)
1 thread (jdbc TRANSACTION_READ_COMMITTED) doing occational
selects/updates on the table. I will change this to READ_UNCOMMITTED ,
however from client logs and the lockstatistics table i can see that
this thread is not involved in a deadlock.
I have a deadlock and all clients block, the clients never timeout and
the kernel never expires the locks even if the clients are killed.The
kernel cannot be shutdown and the only solution is to restart the
database machine.
How can i improve deadlock detection??
regards,
Ajit
P.S:
Contents of lockstatistics. : can someone tell me why REQTIMEOUT is
null?? I cant figure out why the db deadlocked in this case. So if u
have any ideas mail me.
"1552","
4671799","48","ONMOBILE","20040316","00182223","onmumhuttel 7DC","?","
0","row_exclusive","write","?","?","2012","ONMUMHUTTEL","ONMOBILE","VM_M
ISSEDCALLS","00000000000002A8","53","20393832303339333733362020202020203
23232333432393031352020202020203230303430333136313834383436393830303030"
,"'9820393736 '.'2223429015 '.'20040316184846980000'..."
"1553","
4671453","24","ONMOBILE","20040316","00182223","onmumhuttel 7DC","?","
0","row_exclusive","write","?","?","2012","ONMUMHUTTEL","ONMOBILE","VM_M
ISSEDCALLS","00000000000002A8","53","20393832303732373231332020202020203
93839323431313936352020202020203230303430333136313834383433373630303030"
,"'9820727213 '.'9892411965 '.'20040316184843760000'..."
"1545","
4671514","22","ONMOBILE","20040316","00182222","onmumhuttel 7DC","?","
0","row_exclusive","write","?","?","2012","ONMUMHUTTEL","ONMOBILE","VM_M
ISSEDCALLS","00000000000002A8","53","20393831393430333035322020202020203
23235363735343237352020202020203230303430333136313834383433393830303030"
,"'9819403052 '.'2256754275 '.'20040316184843980000'..."
"1578"," 4671855","55","ONMOBILE","20040316","00182809","
java","?","
0","row_exclusive","write","?","?","0","ONSERDAEMONS","ONMOBILE","VM_MIS
SEDCALLS","00000000000002A8","53","2039383230303439303733202020202020323
235363636393737312020202020203230303430333136313834313232383430303030","
'9820049073 '.'2256669771 '.'20040316184122840000'..."
"1578"," 4671855","55","ONMOBILE","20040316","00182809","
java","?","
0","row_exclusive","write","?","?","0","ONSERDAEMONS","ONMOBILE","VM_MIS
SEDCALLS","00000000000002A8","53","2039383230303439303733202020202020323
235363636393737312020202020203230303430333136313834313335383530303030","
'9820049073 '.'2256669771 '.'20040316184135850000'..."
"1578"," 4671855","55","ONMOBILE","20040316","00182809","
java","?","
0","row_exclusive","write","?","?","0","ONSERDAEMONS","ONMOBILE","VM_MIS
SEDCALLS","00000000000002A8","53","2039383230303439303733202020202020323
235363636393737322020202020203230303430333136313834313135323130303030","
'9820049073 '.'2256669772 '.'20040316184115210000'..."
"1578"," 4671855","55","ONMOBILE","20040316","00182809","
java","?","
0","row_exclusive","write","?","?","0","ONSERDAEMONS","ONMOBILE","VM_MIS
SEDCALLS","00000000000002A8","53","2039383230303439303733202020202020323
235363636393737322020202020203230303430333136313834313330343230303030","
'9820049073 '.'2256669772 '.'20040316184130420000'..."
"1578"," 4671855","55","ONMOBILE","20040316","00182809","
java","?","
0","row_exclusive","write","?","?","0","ONSERDAEMONS","ONMOBILE","VM_MIS
SEDCALLS","00000000000002A8","53","2039383230303439303733202020202020323
235363636393737322020202020203230303430333136313834313431333430303030","
'9820049073 '.'2256669772 '.'20040316184141340000'..."
"1578"," 4671855","55","ONMOBILE","20040316","00182809","
java","?","
0","row_exclusive","write","?","?","0","ONSERDAEMONS","ONMOBILE","VM_MIS
SEDCALLS","00000000000002A8","53","2039383230303439303733202020202020323
235363636393737322020202020203230303430333136313834323031323330303030","
'9820049073 '.'2256669772 '.'20040316184201230000'..."
"1578"," 4671855","55","ONMOBILE","20040316","00182809","
java","?","
0","row_exclusive","write","?","?","0","ONSERDAEMONS","ONMOBILE","VM_MIS
SEDCALLS","00000000000002A8","53","2039383230303439303733202020202020323
235363636393737322020202020203230303430333136313834353333353330303030","
'9820049073 '.'2256669772 '.'20040316184533530000'..."
"1578"," 4671855","55","ONMOBILE","20040316","00182809","
java","?","
0","row_exclusive","write","?","?","0","ONSERDAEMONS","ONMOBILE","VM_MIS
SEDCALLS","00000000000002A8","53","2039383230303439303733202020202020393
832313031343633332020202020203230303430333136313834323131393030303030","
'9820049073 '.'9821014633 '.'20040316184211900000'..."
"1578"," 4671855","55","ONMOBILE","20040316","00182809","
java","?","
0","row_exclusive","write","?","?","0","ONSERDAEMONS","ONMOBILE","VM_MIS
SEDCALLS","00000000000002A8","53","2039383230303439303733202020202020393
832313136323937352020202020203230303430333136313834323533343830303030","
'9820049073 '.'9821162975 '.'20040316184253480000'..."
"1578"," 4671855","55","ONMOBILE","20040316","00182809","
java","?","
0","row_exclusive","write","?","?","0","ONSERDAEMONS","ONMOBILE","VM_MIS
SEDCALLS","00000000000002A8","53","2039383230303439303733202020202020393
832313136323937352020202020203230303430333136313834333030333730303030","
'9820049073 '.'9821162975 '.'20040316184300370000'..."
"1578"," 4671855","55","ONMOBILE","20040316","00182809","
java","?","
0","row_exclusive","write","?","?","0","ONSERDAEMONS","ONMOBILE","VM_MIS
SEDCALLS","00000000000002A8","53","2039383230303439303733202020202020393
832313136323937352020202020203230303430333136313834333039303930303030","
'9820049073 '.'9821162975 '.'20040316184309090000'..."
"1578"," 4671855","55","ONMOBILE","20040316","00182809","
java","?","
0","row_exclusive","write","?","?","0","ONSERDAEMONS","ONMOBILE","VM_MIS
SEDCALLS","00000000000002A8","53","2039383230303439303733202020202020393
832313136323937352020202020203230303430333136313834333138363730303030","
'9820049073 '.'9821162975 '.'20040316184318670000'..."
"1578"," 4671855","55","ONMOBILE","20040316","00182809","
java","?","
0","row_exclusive","write","?","?","0","ONSERDAEMONS","ONMOBILE","VM_MIS
SEDCALLS","00000000000002A8","53","2039383230303439303733202020202020393
832313136323937352020202020203230303430333136313834333330343030303030","
'9820049073 '.'9821162975 '.'20040316184330400000'..."
"1578"," 4671855","55","ONMOBILE","20040316","00182809","
java","?","
0","row_exclusive","write","?","?","0","ONSERDAEMONS","ONMOBILE","VM_MIS
SEDCALLS","00000000000002A8","53","2039383230303439303733202020202020393
832313136323937352020202020203230303430333136313834333430303430303030","
'9820049073 '.'9821162975 '.'20040316184340040000'..."
"1578"," 4671855","55","ONMOBILE","20040316","00182809","
java","?","
0","row_exclusive","write","?","?","0","ONSERDAEMONS","ONMOBILE","VM_MIS
SEDCALLS","00000000000002A8","53","2039383230303439303733202020202020393
832313136323937352020202020203230303430333136313834333530333730303030","
'9820049073 '.'9821162975 '.'20040316184350370000'..."
"1554","
4671807","49","ONMOBILE","20040316","00182223","onmumhuttel 7DC","?","
0","row_exclusive","write","?","?","2012","ONMUMHUTTEL","ONMOBILE","VM_M
ISSEDCALLS","00000000000002A8","53","20393832303337353636312020202020203
23233383032303331342020202020203230303430333136313834383437343530303030"
,"'9820375661 '.'2238020314 '.'20040316184847450000'..."
"1555","
4671736","25","ONMOBILE","20040316","00182223","onmumhuttel 7DC","?","
0","row_exclusive","write","?","?","2012","ONMUMHUTTEL","ONMOBILE","VM_M
ISSEDCALLS","00000000000002A8","53","20393832303831353838362020202020203
23232383931393735322020202020203230303430333136313834383436353630303030"
,"'9820815886 '.'2228919752 '.'20040316184846560000'..."
"1556","
4671869","50","ONMOBILE","20040316","00182223","onmumhuttel 7DC","?","
0","row_exclusive","write","?","?","2012","ONMUMHUTTEL","ONMOBILE","VM_M
ISSEDCALLS","00000000000002A8","53","20393832303937373434322020202020203
43834323430303031392020202020203230303430333136313834383435373630303030"
,"'9820977442 '.'4842400019 '.'20040316184845760000'..."
"1557","
4671808","26","ONMOBILE","20040316","00182223","onmumhuttel 7DC","?","
0","row_exclusive","write","?","?","2012","ONMUMHUTTEL","ONMOBILE","VM_M
ISSEDCALLS","00000000000002A8","53","20393832303034333937392020202020203
23232363135363136342020202020203230303430333136313834383437343830303030"
,"'9820043979 '.'2226156164 '.'20040316184847480000'..."
"1574"," 4671858","53","ONMOBILE","20040316","00182809","
java","?","
0","row_exclusive","write","?","?","0","ONSERDAEMONS","ONMOBILE","VM_MIS
SEDCALLS","00000000000002A8","53","2039383230303532363031202020202020393
832313537363633372020202020203230303430333136313834313033353030303030","
'9820052601 '.'9821576637 '.'20040316184103500000'..."
"1574"," 4671858","53","ONMOBILE","20040316","00182809","
java","?","
0","row_exclusive","write","?","?","0","ONSERDAEMONS","ONMOBILE","VM_MIS
SEDCALLS","00000000000002A8","53","2039383230303532363031202020202020393
832313537363633372020202020203230303430333136313834323235373830303030","
'9820052601 '.'9821576637 '.'20040316184225780000'..."
"1558","
4671586","51","ONMOBILE","20040316","00182224","onmumhuttel 7DC","?","
0","row_exclusive","write","?","?","2012","ONMUMHUTTEL","ONMOBILE","VM_M
ISSEDCALLS","00000000000002A8","53","20393832303035313833322020202020203
93832303334393639362020202020203230303430333136313834383434393030303030"
,"'9820051832 '.'9820349696 '.'20040316184844900000'..."
"1550","
4671786","23","ONMOBILE","20040316","00182222","onmumhuttel 7DC","?","
0","row_exclusive","write","?","?","2012","ONMUMHUTTEL","ONMOBILE","VM_M
ISSEDCALLS","00000000000002A8","53","20393832303833303135352020202020203
23630323637373930332020202020203230303430333136313834383436393530303030"
,"'9820830155 '.'2602677903 '.'20040316184846950000'..."
"1551","
4671646","47","ONMOBILE","20040316","00182223","onmumhuttel 7DC","?","
0","row_exclusive","write","?","?","2012","ONMUMHUTTEL","ONMOBILE","VM_M
ISSEDCALLS","00000000000002A8","53","20393832303036373836382020202020203
43032343531343030322020202020203230303430333136313834383435333230303030"
,"'9820067868 '.'4024514002 '.'20040316184845320000'..."
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/maxdb?unsub=mysql@(protected)
|
|
