Home > Archive > MySQL ODBC Connector > February 2006 > Why do these transactions show table locks?









You are viewing an archived Text-only version of the thread. To view this thread in it's original format and/or if you want to reply to this thread please [click here]

 

Author Why do these transactions show table locks?
Robert DiFalco

2006-02-28, 8:28 pm

My understanding is that innodb should not be using table locks for
insert, update, or delete. However, the following transactions are
showing table locks. What's up?

R.

---TRANSACTION 0 4573, ACTIVE 1 sec, OS thread id 3112 setting table
lock
mysql tables in use 1, locked 0
LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250
MySQL thread id 4, query id 566875 squid.tripwire.com 10.150.1.30 root
System lock
INSERT INTO
ELEMS(oid,E_NID,E_RI
D,E_NMID,E_INSC,E_ET
,E_BLID,E_LCID,E_LCN
O,E_LCTYP,E_
LCSEV)
VALUES(- 9223372036854523873,
- 9223372036854775299,
- 9223372036854775181,
-9
223372036854744697,1
,-1,- 9223372036854027123,
- 9223372036854027123,
1,0,0)
------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `space/elems` trx id 0 4573 lock mode X waiting
------------------
---TRANSACTION 0 4569, ACTIVE 1 sec, OS thread id 1708 setting table
lock
mysql tables in use 1, locked 0
LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250
MySQL thread id 6, query id 565737 squid.tripwire.com 10.150.1.30 root
System lock
INSERT INTO
ELEMS(oid,E_NID,E_RI
D,E_NMID,E_INSC,E_ET
,E_BLID,E_LCID,E_LCN
O,E_LCTYP,E_
LCSEV)
VALUES(- 9223372036854557373,
- 9223372036854775352,
- 9223372036854775181,
-9
223372036854601648,1
,-1,- 9223372036854027623,
- 9223372036854027623,
1,0,0)
------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `space/elems` trx id 0 4569 lock mode X waiting
------------------
---TRANSACTION 0 4565, ACTIVE 2 sec, OS thread id 4008 setting table
lock
mysql tables in use 1, locked 0
LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250
MySQL thread id 5, query id 564870 squid.tripwire.com 10.150.1.30 root
System lock
INSERT INTO
ELEMS(oid,E_NID,E_RI
D,E_NMID,E_INSC,E_ET
,E_BLID,E_LCID,E_LCN
O,E_LCTYP,E_
LCSEV)
VALUES(- 9223372036854578873,
- 9223372036854775355,
- 9223372036854775181,
-9
223372036854744697,1
,-1,- 9223372036854033123,
- 9223372036854033123,
1,0,0)
------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `space/elems` trx id 0 4565 lock mode X waiting
------------------
---TRANSACTION 0 4561, ACTIVE 2 sec, OS thread id 3148 setting table
lock
mysql tables in use 1, locked 0
LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250
MySQL thread id 7, query id 563809 squid.tripwire.com 10.150.1.30 root
System lock
INSERT INTO
ELEMS(oid,E_NID,E_RI
D,E_NMID,E_INSC,E_ET
,E_BLID,E_LCID,E_LCN
O,E_LCTYP,E_
LCSEV)
VALUES(- 9223372036854557623,
- 9223372036854775352,
- 9223372036854775181,
-9
223372036854757305,1
,-1,- 9223372036854033623,
- 9223372036854033623,
1,0,0)
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `space/elems` trx id 0 4561 lock mode X waiting
------------------


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Robert DiFalco

2006-02-28, 8:28 pm

It might be important to note that I have a delete trigger on the ELEMS
table, also, this INSERT call is being made from a stored procedure. The
stored procedure only has one line, this INSERT statement. Could this
have anything to do with bug# 16229?

http://bugs.mysql.com/bug.php?id=3D16229=20

-----Original Message-----
From: Robert DiFalco & #91;mailto:rdifalco@
tripwire.com]=20
Sent: Sunday, February 26, 2006 9:33 AM
To: mysql@lists.mysql.com; internals@lists.mysql.com
Subject: Why do these transactions show table locks?

My understanding is that innodb should not be using table locks for
insert, update, or delete. However, the following transactions are
showing table locks. What's up?

R.

---TRANSACTION 0 4573, ACTIVE 1 sec, OS thread id 3112 setting table
lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap
size 320, undo log entries 250 MySQL thread id 4, query id 566875
squid.tripwire.com 10.150.1.30 root System lock INSERT INTO
ELEMS(oid,E_NID,E_RI
D,E_NMID,E_INSC,E_ET
,E_BLID,E_LCID,E_LCN
O,E_LCTYP,E_
LCSEV)
VALUES(- 9223372036854523873,
- 9223372036854775299,
- 9223372036854775181,
-9
223372036854744697,1
,-1,- 9223372036854027123,
- 9223372036854027123,
1,0,0)
------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `space/elems` trx id 0 4573 lock mode X waiting
------------------
---TRANSACTION 0 4569, ACTIVE 1 sec, OS thread id 1708 setting table
lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap
size 320, undo log entries 250 MySQL thread id 6, query id 565737
squid.tripwire.com 10.150.1.30 root System lock INSERT INTO
ELEMS(oid,E_NID,E_RI
D,E_NMID,E_INSC,E_ET
,E_BLID,E_LCID,E_LCN
O,E_LCTYP,E_
LCSEV)
VALUES(- 9223372036854557373,
- 9223372036854775352,
- 9223372036854775181,
-9
223372036854601648,1
,-1,- 9223372036854027623,
- 9223372036854027623,
1,0,0)
------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `space/elems` trx id 0 4569 lock mode X waiting
------------------
---TRANSACTION 0 4565, ACTIVE 2 sec, OS thread id 4008 setting table
lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap
size 320, undo log entries 250 MySQL thread id 5, query id 564870
squid.tripwire.com 10.150.1.30 root System lock INSERT INTO
ELEMS(oid,E_NID,E_RI
D,E_NMID,E_INSC,E_ET
,E_BLID,E_LCID,E_LCN
O,E_LCTYP,E_
LCSEV)
VALUES(- 9223372036854578873,
- 9223372036854775355,
- 9223372036854775181,
-9
223372036854744697,1
,-1,- 9223372036854033123,
- 9223372036854033123,
1,0,0)
------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `space/elems` trx id 0 4565 lock mode X waiting
------------------
---TRANSACTION 0 4561, ACTIVE 2 sec, OS thread id 3148 setting table
lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap
size 320, undo log entries 250 MySQL thread id 7, query id 563809
squid.tripwire.com 10.150.1.30 root System lock INSERT INTO
ELEMS(oid,E_NID,E_RI
D,E_NMID,E_INSC,E_ET
,E_BLID,E_LCID,E_LCN
O,E_LCTYP,E_
LCSEV)
VALUES(- 9223372036854557623,
- 9223372036854775352,
- 9223372036854775181,
-9
223372036854757305,1
,-1,- 9223372036854033623,
- 9223372036854033623,
1,0,0)
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `space/elems` trx id 0 4561 lock mode X waiting
------------------


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql? unsub...br /> pwire.com




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Heikki Tuuri

2006-02-28, 8:28 pm

Robert,

----- Original Message -----
From: ""Robert DiFalco"" <rdifalco@tripwire.com>
Newsgroups: mailing.database.myodbc
Sent: Sunday, February 26, 2006 8:27 PM
Subject: RE: Why do these transactions show table locks?


> It might be important to note that I have a delete trigger on the ELEMS
> table, also, this INSERT call is being made from a stored procedure. The
> stored procedure only has one line, this INSERT statement. Could this
> have anything to do with bug# 16229?
>
> http://bugs.mysql.com/bug.php?id=3D16229=20

yes, I think this is:

http://bugs.mysql.com/bug.php?id=16229

which is fixed in 5.0.19. I just tested that an ordinary INSERT in 5.0.18
does not use full explicit table locks in InnoDB.

Best regards,

Heikki

Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

> -----Original Message-----
> From: Robert DiFalco & #91;mailto:rdifalco@
tripwire.com]=20
> Sent: Sunday, February 26, 2006 9:33 AM
> To: mysql@lists.mysql.com; internals@lists.mysql.com
> Subject: Why do these transactions show table locks?
>
> My understanding is that innodb should not be using table locks for
> insert, update, or delete. However, the following transactions are
> showing table locks. What's up?
>
> R.
>
> ---TRANSACTION 0 4573, ACTIVE 1 sec, OS thread id 3112 setting table
> lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap
> size 320, undo log entries 250 MySQL thread id 4, query id 566875
> squid.tripwire.com 10.150.1.30 root System lock INSERT INTO
> ELEMS(oid,E_NID,E_RI
D,E_NMID,E_INSC,E_ET
,E_BLID,E_LCID,E_LCN
O,E_LCTYP,E_
> LCSEV)
> VALUES(- 9223372036854523873,
- 9223372036854775299,
- 9223372036854775181,
-9
> 223372036854744697,1
,-1,- 9223372036854027123,
- 9223372036854027123,
1,0,0)
> ------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
> TABLE LOCK table `space/elems` trx id 0 4573 lock mode X waiting
> ------------------
> ---TRANSACTION 0 4569, ACTIVE 1 sec, OS thread id 1708 setting table
> lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap
> size 320, undo log entries 250 MySQL thread id 6, query id 565737
> squid.tripwire.com 10.150.1.30 root System lock INSERT INTO
> ELEMS(oid,E_NID,E_RI
D,E_NMID,E_INSC,E_ET
,E_BLID,E_LCID,E_LCN
O,E_LCTYP,E_
> LCSEV)
> VALUES(- 9223372036854557373,
- 9223372036854775352,
- 9223372036854775181,
-9
> 223372036854601648,1
,-1,- 9223372036854027623,
- 9223372036854027623,
1,0,0)
> ------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
> TABLE LOCK table `space/elems` trx id 0 4569 lock mode X waiting
> ------------------
> ---TRANSACTION 0 4565, ACTIVE 2 sec, OS thread id 4008 setting table
> lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap
> size 320, undo log entries 250 MySQL thread id 5, query id 564870
> squid.tripwire.com 10.150.1.30 root System lock INSERT INTO
> ELEMS(oid,E_NID,E_RI
D,E_NMID,E_INSC,E_ET
,E_BLID,E_LCID,E_LCN
O,E_LCTYP,E_
> LCSEV)
> VALUES(- 9223372036854578873,
- 9223372036854775355,
- 9223372036854775181,
-9
> 223372036854744697,1
,-1,- 9223372036854033123,
- 9223372036854033123,
1,0,0)
> ------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
> TABLE LOCK table `space/elems` trx id 0 4565 lock mode X waiting
> ------------------
> ---TRANSACTION 0 4561, ACTIVE 2 sec, OS thread id 3148 setting table
> lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap
> size 320, undo log entries 250 MySQL thread id 7, query id 563809
> squid.tripwire.com 10.150.1.30 root System lock INSERT INTO
> ELEMS(oid,E_NID,E_RI
D,E_NMID,E_INSC,E_ET
,E_BLID,E_LCID,E_LCN
O,E_LCTYP,E_
> LCSEV)
> VALUES(- 9223372036854557623,
- 9223372036854775352,
- 9223372036854775181,
-9
> 223372036854757305,1
,-1,- 9223372036854033623,
- 9223372036854033623,
1,0,0)
> ------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
> TABLE LOCK table `space/elems` trx id 0 4561 lock mode X waiting
> ------------------
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql? unsub...br /> pwire.com
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links





Also available: Server administration forum archive | Web Design forum archive | Software forum archive | Hardware reviews archive | Programming forum archive

Copyright 2009 droptable.com