Re: Why is this table lock?

Heikki Tuuri Sun, 24 Apr 2005 13:08:19 -0700

Reto,

InnoDB always does locking on the row level. The IS and IX table locks are 'intention locks'. They do not block anything, except full table lock requests (LOCK TABLES ... WRITE, for example). The main purpose of IX and IS is to show that someone is locking a row, or going to lock a row in the table.

I guess we will need to add a note about this to the manual, or even better, to the SHOW INNODB STATUS output. Intention locks are a standard implementation technique which is described in database literature, but they sometimes confuse users.

Best regards,

Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php

Order MySQL Network from http://www.mysql.com/network/

----- Original Message ----- From: "kernel" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Friday, April 22, 2005 7:44 PM Subject: Re: Why is this table lock?

Reto Breitenmoser wrote:

Hi

In the output from the show innodb monitor I can see, that I have a
table lock on a table.   But, I never set a table lock on a table
(only row locks). Do I misinterpret the output or what causes this
table lock?

thanks
Reto


------------
TRANSACTIONS
------------
Trx id counter 0 95338708
Purge done for trx's n:o < 0 95338686 undo n:o < 0 0
History list length 10
Total number of lock structs in row lock hash table 15
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 95338703, not started, OS thread id 1764
MySQL thread id 43, query id 1028436 localhost 127.0.0.1 ales
---TRANSACTION 0 95338693, not started, OS thread id 3184
MySQL thread id 47, query id 1029658 localhost 127.0.0.1 ales
---TRANSACTION 0 95338705, not started, OS thread id 2720
MySQL thread id 45, query id 1028497 localhost 127.0.0.1 ales
---TRANSACTION 0 95338701, not started, OS thread id 2852
MySQL thread id 44, query id 1028430 localhost 127.0.0.1 ales
---TRANSACTION 0 95338380, not started, OS thread id 2960
MySQL thread id 10, query id 973614 localhost 127.0.0.1 root
---TRANSACTION 0 0, not started, OS thread id 1228
MySQL thread id 3, query id 1029917 localhost 127.0.0.1 root
---TRANSACTION 0 95338707, ACTIVE 0 sec, OS thread id 576
25 lock struct(s), heap size 2496, undo log entries 700
MySQL thread id 46, query id 1029926 localhost 127.0.0.1 ales query end
update PARAMETER set PARAMETERIZEDELEM_ID=?, PARAMETER_IND=? where
PARAMETER_ID=?
TABLE LOCK table `pco/agentobject` trx id 0 95338707 lock mode IX
TABLE LOCK table `pco/parameterizedelement` trx id 0 95338707 lock
mode IX
RECORD LOCKS space id 0 page no 6400 n bits 240 index `PRIMARY` of
table `pco/agentobject` trx id 0 95338707 lock_mode X locks rec but
not gap
Record lock, heap no 174 PHYSICAL RECORD: n_fields 5; 1-byte offs
TRUE; info bits 0
 0: len 8; hex 800000000000062e; asc €      .;; 1: len 6; hex
000005aec0d3; asc    ®ÀÓ;; 2: len 7; hex 80000019090084; asc €
„;; 3: SQL NULL, size 0 ; 4: SQL NULL, size 0 ;

Record lock, heap no 175 PHYSICAL RECORD: n_fields 5; 1-byte offs
TRUE; info bits 0
 0: len 8; hex 800000000000062f; asc €      /;; 1: len 6; hex
000005aec0d3; asc    ®ÀÓ;; 2: len 7; hex 800000190900de; asc €
Þ;; 3: len 10; hex 636c69656e74305f7332; asc client0_s2;; 4: len 7;
hex 506c61747a2030; asc Platz 0;;

Record lock, heap no 176 PHYSICAL RECORD: n_fields 5; 1-byte offs
TRUE; info bits 0
 0: len 8; hex 8000000000000630; asc €      0;; 1: len 6; hex
000005aec0d3; asc    ®ÀÓ;; 2: len 7; hex 80000019090138; asc €
8;; 3: len 13; hex 4c6f616446726f6d456e747279; asc LoadFromEntry;; 4:
len 15; hex 67726f7570206d6f64656c6c696e67; asc group modelling;;

Record lock, heap no 177 PHYSICAL RECORD: n_fields 5; 1-byte offs
TRUE; info bits 0
 0: len 8; hex 8000000000000631; asc €      1;; 1: len 6; hex

Reto,
You might want to watch the "InnoDB Writes blocking Reads" message
thread on the list. It looks like you and Andy are having the same
issues with innodb tables being locked instead of rows being locked. I
don't have any idea...

walt

-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Re: Why is this table lock?

Reply via email to