Full Innodb Table Locks deadlocking with AUTO_INC locks.

[William Newton]

Hello List,

I discovered an unusual problem with the way Innodb handles the AUTO_INC lock 
with a full table lock. I was wondering if this is a known issue, or I'm doing 
something completely wrong. I'm working with MYSQL Server version: 
5.0.42-debug-log  on Gentoo Linux.

So lets say I have this table:

CREATE TABLE `quicktable` (
  `x` int(11) NOT NULL auto_increment,
  `quicktext` varchar(50) default NULL,
  PRIMARY KEY  (`x`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1



Several connections are inserting concurrently to the table with normal single 
statements such as:

INSERT INTO quicktable (quicktext) VALUES ('Bob 25  item 5');

The value inserted into quicktable changes for every insert to indicate which 
connection and which insert its doing

Now in a unique connection is locking the table using the INNODB suggested 
method:

SET AUTOCOMMIT = 0;
LOCK TABLES quicktable WRITE;

For demonstration purposes this thread sleeps for a second to simulate 
processing that might be going on in the application.
After 1 Second:

COMMIT;
UNLOCK TABLES;
SET AUTOCOMMIT =1;



The result is a dead lock where all queries wait until one of the INSERT's 
times out then the LOCK statement manages to get the table lock. But it happens 
repeatedly with as few as two connections sending inserts. The server can 
handle many many more concurrent inserts if the lock is removed , with out 
resulting in any  locks.

Here is the output of show processlist:
SHOW PROCESSLIST;

+--+--+---++-+--+++
| Id   | User | Host  | db   | Command | Time | State  | Info   
  | 
+--+--+---++-+--+++
| 1470 | bob | localhost | test | Query   |   19 | update | INSERT INTO 
quicktable (quicktext) VALUES ('Bob 26816  item 5') |
| 1471 | bob | localhost | test | Query   |   19 | Locked | INSERT INTO 
quicktable (quicktext) VALUES ('Bob 6817  item 2') |
| 1472 | bob | localhost | test | Query   |   19 | Locked | LOCK TABLES 
quicktable WRITE |
+--+--+---++-+--++--+

SHOW INNODB STATUS:
=
071002 16:51:55 INNODB MONITOR OUTPUT
=
Per second averages calculated from the last 9 seconds
--
SEMAPHORES
--
OS WAIT ARRAY INFO: reservation count 91, signal count 91
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 170, OS waits 85; RW-excl spins 7, OS waits 6

LATEST DETECTED DEADLOCK

071002 16:51:37
*** (1) TRANSACTION:
TRANSACTION 0 26382727, ACTIVE 0 sec, process no 6820, OS thread id 24264721 
setting table lock
mysql tables in use 1, locked 0
LOCK WAIT 1 lock struct(s), heap size 320
MySQL thread id 1472, query id 24493 localhost bob System lock
LOCK TABLES quicktable WRITE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `test/quicktable` trx id 0 26382727 lock mode X waiting
*** (2) TRANSACTION:
TRANSACTION 0 26382726, ACTIVE 0 sec, process no 6819, OS thread id 24248336 
inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320
MySQL thread id 1471, query id 24483 localhost bob update
INSERT INTO quicktable (quicktext) VALUES ('Bob 6817 item 37')
*** (2) HOLDS THE LOCK(S):
TABLE LOCK table `test/quicktable` trx id 0 26382726 lock mode AUTO-INC
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `test/quicktable` trx id 0 26382726 lock mode IX waiting
*** WE ROLL BACK TRANSACTION (2)

TRANSACTIONS

Trx id counter 0 26382734
Purge done for trx's n:o  0 26382636 undo n:o  0 0
History list length 39
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 26382731, not started, process no 6818, OS thread id 24231951
mysql tables in use 1, locked 1
MySQL thread id 1470, query id 24519 localhost bob Table lock
INSERT INTO quicktable (quicktext) VALUES (' Bob 6816 item 38')
---TRANSACTION 0 0, not started, process no 3631, OS thread id 17858573
MySQL thread id 1081, query id 7444 localhost bob
---TRANSACTION 0 26375280, not started, process no 2153, OS thread id 16531468
MySQL thread id 1000, query id 24536 localhost bob
show innodb status
---TRANSACTION 0 0, not started, process no 1894, OS thread id 16318475
MySQL thread id 987, query id 1621 localhost bob
---TRANSACTION 0 26382733, ACTIVE 17 sec, process no 6819, OS thread id 
24248336 setting auto-inc lock
mysql tables in use 1, locked 1
LOCK WAIT 1 lock struct(s), heap size 320
MySQL thread id 1471, query id 24507 localhost bob update
INSERT INTO quicktable (quicktext) VALUES ('Bob 6817 item 38')
--- TRX HAS BEEN WAITING 17 SEC FOR THIS 

Re: Full Innodb Table Locks deadlocking with AUTO_INC locks.

[Baron Schwartz]

Hi William,

William Newton wrote:

Hello List,

I discovered an unusual problem with the way Innodb handles the AUTO_INC lock 
with a full table lock. I was wondering if this is a known issue, or I'm doing 
something completely wrong. I'm working with MYSQL Server version: 
5.0.42-debug-log  on Gentoo Linux.

So lets say I have this table:

CREATE TABLE `quicktable` (
  `x` int(11) NOT NULL auto_increment,
  `quicktext` varchar(50) default NULL,
  PRIMARY KEY  (`x`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1



Several connections are inserting concurrently to the table with normal single 
statements such as:

INSERT INTO quicktable (quicktext) VALUES ('Bob 25  item 5');

The value inserted into quicktable changes for every insert to indicate which 
connection and which insert its doing

Now in a unique connection is locking the table using the INNODB suggested 
method:

SET AUTOCOMMIT = 0;
LOCK TABLES quicktable WRITE;

For demonstration purposes this thread sleeps for a second to simulate 
processing that might be going on in the application.
After 1 Second:

COMMIT;
UNLOCK TABLES;
SET AUTOCOMMIT =1;



The result is a dead lock where all queries wait until one of the INSERT's 
times out then the LOCK statement manages to get the table lock. But it happens 
repeatedly with as few as two connections sending inserts. The server can 
handle many many more concurrent inserts if the lock is removed , with out 
resulting in any  locks.

Here is the output of show processlist:
SHOW PROCESSLIST;

+--+--+---++-+--+++
| Id   | User | Host  | db   | Command | Time | State  | Info | 
+--+--+---++-+--+++

| 1470 | bob | localhost | test | Query   |   19 | update | INSERT INTO 
quicktable (quicktext) VALUES ('Bob 26816  item 5') |
| 1471 | bob | localhost | test | Query   |   19 | Locked | INSERT INTO 
quicktable (quicktext) VALUES ('Bob 6817  item 2') |
| 1472 | bob | localhost | test | Query   |   19 | Locked | LOCK TABLES 
quicktable WRITE |
+--+--+---++-+--++--+

SHOW INNODB STATUS:
=
071002 16:51:55 INNODB MONITOR OUTPUT
=
Per second averages calculated from the last 9 seconds
--
SEMAPHORES
--
OS WAIT ARRAY INFO: reservation count 91, signal count 91
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 170, OS waits 85; RW-excl spins 7, OS waits 6

LATEST DETECTED DEADLOCK

071002 16:51:37
*** (1) TRANSACTION:
TRANSACTION 0 26382727, ACTIVE 0 sec, process no 6820, OS thread id 24264721 
setting table lock
mysql tables in use 1, locked 0
LOCK WAIT 1 lock struct(s), heap size 320
MySQL thread id 1472, query id 24493 localhost bob System lock
LOCK TABLES quicktable WRITE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `test/quicktable` trx id 0 26382727 lock mode X waiting
*** (2) TRANSACTION:
TRANSACTION 0 26382726, ACTIVE 0 sec, process no 6819, OS thread id 24248336 
inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320
MySQL thread id 1471, query id 24483 localhost bob update
INSERT INTO quicktable (quicktext) VALUES ('Bob 6817 item 37')
*** (2) HOLDS THE LOCK(S):
TABLE LOCK table `test/quicktable` trx id 0 26382726 lock mode AUTO-INC
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `test/quicktable` trx id 0 26382726 lock mode IX waiting
*** WE ROLL BACK TRANSACTION (2)

TRANSACTIONS

Trx id counter 0 26382734
Purge done for trx's n:o  0 26382636 undo n:o  0 0
History list length 39
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 26382731, not started, process no 6818, OS thread id 24231951
mysql tables in use 1, locked 1
MySQL thread id 1470, query id 24519 localhost bob Table lock
INSERT INTO quicktable (quicktext) VALUES (' Bob 6816 item 38')
---TRANSACTION 0 0, not started, process no 3631, OS thread id 17858573
MySQL thread id 1081, query id 7444 localhost bob
---TRANSACTION 0 26375280, not started, process no 2153, OS thread id 16531468
MySQL thread id 1000, query id 24536 localhost bob
show innodb status
---TRANSACTION 0 0, not started, process no 1894, OS thread id 16318475
MySQL thread id 987, query id 1621 localhost bob
---TRANSACTION 0 26382733, ACTIVE 17 sec, process no 6819, OS thread id 
24248336 setting auto-inc lock
mysql tables in use 1, locked 1
LOCK WAIT 1 lock struct(s), heap size 320
MySQL thread id 1471, query id 24507 localhost bob update
INSERT INTO quicktable (quicktext) VALUES ('Bob 6817 item 38')
--- 

Re: Full Innodb Table Locks deadlocking with AUTO_INC locks.

[William Newton]

Thanks for the quick reply Barron, but doesn't SET AUTOCOMMIT = 0;  disable 
AUTOCOMMIT ?

from http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html :


The correct way to use LOCK TABLES and
UNLOCK TABLES with transactional tables,
such as InnoDB tables, is to set
AUTOCOMMIT = 0 and not to call
UNLOCK TABLES until you commit the
transaction explicitly. When you call LOCK
TABLES, InnoDB internally takes
its own table lock, and MySQL takes its own table lock.
InnoDB releases its table lock at the
next commit, but for MySQL to release its table lock, you
have to call UNLOCK TABLES. You should
not have AUTOCOMMIT = 1, because then
InnoDB releases its table lock
immediately after the call of LOCK
TABLES, and deadlocks can very easily happen. Note
that we do not acquire the InnoDB table
lock at all if AUTOCOMMIT=1, to help old
applications avoid unnecessary deadlocks.

So it says deadlocks can happen very easily if AUTOCOMMIT=1, but  we do not 
acquire the InnoDB table
lock at all if AUTOCOMMIT=1, to help old
applications avoid unnecessary deadlocks.

In this particular situation the deadlock does not happen if AUTO COMMIT =1, 
but that could cause other deadlocks. 

Am I confused, or is it that really unclear?

- Original Message 
From: Baron Schwartz [EMAIL PROTECTED]
To: William Newton [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Tuesday, October 2, 2007 12:05:41 PM
Subject: Re: Full Innodb Table Locks  deadlocking with AUTO_INC  locks.

Hi William,

William Newton wrote:
 Hello List,
 
 I discovered an unusual problem with the way Innodb handles the AUTO_INC lock 
 with a full table lock. I was wondering if this is a known issue, or I'm 
 doing something completely wrong. I'm working with MYSQL Server version: 
 5.0.42-debug-log  on Gentoo Linux.
 
 So lets say I have this table:
 
 CREATE TABLE `quicktable` (
   `x` int(11) NOT NULL auto_increment,
   `quicktext` varchar(50) default NULL,
   PRIMARY KEY  (`x`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
 
 
 Several connections are inserting concurrently to the table with normal 
 single statements such as:
 
 INSERT INTO quicktable (quicktext) VALUES ('Bob 25  item 5');
 
 The value inserted into quicktable changes for every insert to indicate which 
 connection and which insert its doing
 
 Now in a unique connection is locking the table using the INNODB suggested 
 method:
 
 SET AUTOCOMMIT = 0;
 LOCK TABLES quicktable WRITE;
 
 For demonstration purposes this thread sleeps for a second to simulate 
 processing that might be going on in the application.
 After 1 Second:
 
 COMMIT;
 UNLOCK TABLES;
 SET AUTOCOMMIT =1;
 
 
 
 The result is a dead lock where all queries wait until one of the INSERT's 
 times out then the LOCK statement manages to get the table lock. But it 
 happens repeatedly with as few as two connections sending inserts. The server 
 can handle many many more concurrent inserts if the lock is removed , with 
 out resulting in any  locks.
 
 Here is the output of show processlist:
 SHOW PROCESSLIST;
 
 +--+--+---++-+--+++
 | Id   | User | Host  | db   | Command | Time | State  | Info 
 | 
 +--+--+---++-+--+++
 | 1470 | bob | localhost | test | Query   |   19 | update | INSERT INTO 
 quicktable (quicktext) VALUES ('Bob 26816  item 5') |
 | 1471 | bob | localhost | test | Query   |   19 | Locked | INSERT INTO 
 quicktable (quicktext) VALUES ('Bob 6817  item 2') |
 | 1472 | bob | localhost | test | Query   |   19 | Locked | LOCK TABLES 
 quicktable WRITE |
 +--+--+---++-+--++--+
 
 SHOW INNODB STATUS:
 =
 071002 16:51:55 INNODB MONITOR OUTPUT
 =
 Per second averages calculated from the last 9 seconds
 --
 SEMAPHORES
 --
 OS WAIT ARRAY INFO: reservation count 91, signal count 91
 Mutex spin waits 0, rounds 0, OS waits 0
 RW-shared spins 170, OS waits 85; RW-excl spins 7, OS waits 6
 
 LATEST DETECTED DEADLOCK
 
 071002 16:51:37
 *** (1) TRANSACTION:
 TRANSACTION 0 26382727, ACTIVE 0 sec, process no 6820, OS thread id 24264721 
 setting table lock
 mysql tables in use 1, locked 0
 LOCK WAIT 1 lock struct(s), heap size 320
 MySQL thread id 1472, query id 24493 localhost bob System lock
 LOCK TABLES quicktable WRITE
 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
 TABLE LOCK table `test

Re: Full Innodb Table Locks deadlocking with AUTO_INC locks.

[Baron Schwartz]

Yes.  Sorry, I wasn't verbose enough.  I agree with you.  And I also 
agree that this part of the manual is kind of nonsensical.  I have never 
understood it fully.  Part of what I was saying is I wonder whether the 
manual is wrong and you are getting a deadlock anyway.


William Newton wrote:

Thanks for the quick reply Barron, but doesn't SET AUTOCOMMIT = 0;  disable 
AUTOCOMMIT ?

from http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html :


The correct way to use LOCK TABLES and
UNLOCK TABLES with transactional tables,
such as InnoDB tables, is to set
AUTOCOMMIT = 0 and not to call
UNLOCK TABLES until you commit the
transaction explicitly. When you call LOCK
TABLES, InnoDB internally takes
its own table lock, and MySQL takes its own table lock.
InnoDB releases its table lock at the
next commit, but for MySQL to release its table lock, you
have to call UNLOCK TABLES. You should
not have AUTOCOMMIT = 1, because then
InnoDB releases its table lock
immediately after the call of LOCK
TABLES, and deadlocks can very easily happen. Note
that we do not acquire the InnoDB table
lock at all if AUTOCOMMIT=1, to help old
applications avoid unnecessary deadlocks.

So it says deadlocks can happen very easily if AUTOCOMMIT=1, but  we do not 
acquire the InnoDB table
lock at all if AUTOCOMMIT=1, to help old
applications avoid unnecessary deadlocks.

In this particular situation the deadlock does not happen if AUTO COMMIT =1, but that could cause other deadlocks. 


Am I confused, or is it that really unclear?

- Original Message 
From: Baron Schwartz [EMAIL PROTECTED]
To: William Newton [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Tuesday, October 2, 2007 12:05:41 PM
Subject: Re: Full Innodb Table Locks  deadlocking with AUTO_INC  locks.

Hi William,

William Newton wrote:

Hello List,

I discovered an unusual problem with the way Innodb handles the AUTO_INC lock 
with a full table lock. I was wondering if this is a known issue, or I'm doing 
something completely wrong. I'm working with MYSQL Server version: 
5.0.42-debug-log  on Gentoo Linux.

So lets say I have this table:

CREATE TABLE `quicktable` (
  `x` int(11) NOT NULL auto_increment,
  `quicktext` varchar(50) default NULL,
  PRIMARY KEY  (`x`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1



Several connections are inserting concurrently to the table with normal single 
statements such as:

INSERT INTO quicktable (quicktext) VALUES ('Bob 25  item 5');

The value inserted into quicktable changes for every insert to indicate which 
connection and which insert its doing

Now in a unique connection is locking the table using the INNODB suggested 
method:

SET AUTOCOMMIT = 0;
LOCK TABLES quicktable WRITE;

For demonstration purposes this thread sleeps for a second to simulate 
processing that might be going on in the application.
After 1 Second:

COMMIT;
UNLOCK TABLES;
SET AUTOCOMMIT =1;



The result is a dead lock where all queries wait until one of the INSERT's 
times out then the LOCK statement manages to get the table lock. But it happens 
repeatedly with as few as two connections sending inserts. The server can 
handle many many more concurrent inserts if the lock is removed , with out 
resulting in any  locks.

Here is the output of show processlist:
SHOW PROCESSLIST;

+--+--+---++-+--+++
| Id   | User | Host  | db   | Command | Time | State  | Info | 
+--+--+---++-+--+++

| 1470 | bob | localhost | test | Query   |   19 | update | INSERT INTO 
quicktable (quicktext) VALUES ('Bob 26816  item 5') |
| 1471 | bob | localhost | test | Query   |   19 | Locked | INSERT INTO 
quicktable (quicktext) VALUES ('Bob 6817  item 2') |
| 1472 | bob | localhost | test | Query   |   19 | Locked | LOCK TABLES 
quicktable WRITE |
+--+--+---++-+--++--+

SHOW INNODB STATUS:
=
071002 16:51:55 INNODB MONITOR OUTPUT
=
Per second averages calculated from the last 9 seconds
--
SEMAPHORES
--
OS WAIT ARRAY INFO: reservation count 91, signal count 91
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 170, OS waits 85; RW-excl spins 7, OS waits 6

LATEST DETECTED DEADLOCK

071002 16:51:37
*** (1) TRANSACTION:
TRANSACTION 0 26382727, ACTIVE 0 sec, process no 6820, OS thread id 24264721 
setting table lock
mysql tables in use 1

Too many table-locks

[Marco Simon]

Hi everybody,

I've got a little problem with a web and mysql based
bulleting-board-application.
The board is quite well visited and users are writing aprox. 1 new post
per second. In total the db gets aprox. 250 queries/sec.

The webserver and mysql-server are running on different hosts, the db server
is running on

Linux db 2.6.14.2 #4 SMP Thu Nov 17 09:54:44 CET 2005 x86_64 GNU/Linux

model name  :   Intel(R) Pentium(R) D CPU 3.00GHz
stepping: 4
cpu MHz : 3000.229
cache size  : 1024 KB

4GB RAM.

My problem is that I get a lot and very long locks (30 sec and more) on
the table which is holding
the user's posts. There are aprox. 3.3 Mio records in that table which
consumes
a size of 1.5 GB. The table-format is myisam.

So far switching to innodb mostly soluted my problems with table locks.
But because of the table
size and the limited Memory I can't switch to inno-db in this case.

Are there any other conceptional or technical ideas how to reduce the
(long lasting) table locks ?

Thanks for any idea in advance !

Best regards,
  Marco


smime.p7s
Description: S/MIME Cryptographic Signature

Re: Too many table-locks

[Adrian Bruce]

Assuming that the locking issues occur mainly when an insert is being 
performed (i.e. replying to a post) then what about using read local 
locks for selects so that you can perform con-current inserts?


If you have a lot of old threads that are no longer updated but viewed 
regularly then you could consider splitting the posts table and 
compressing the old threads, how you could implement something like this 
would depend greatly on your forums application however.


Hope this helps

Adrian

Marco Simon wrote:

Hi everybody,

I've got a little problem with a web and mysql based
bulleting-board-application.
The board is quite well visited and users are writing aprox. 1 new post
per second. In total the db gets aprox. 250 queries/sec.

The webserver and mysql-server are running on different hosts, the db server
is running on

Linux db 2.6.14.2 #4 SMP Thu Nov 17 09:54:44 CET 2005 x86_64 GNU/Linux

model name  :   Intel(R) Pentium(R) D CPU 3.00GHz
stepping: 4
cpu MHz : 3000.229
cache size  : 1024 KB

4GB RAM.

My problem is that I get a lot and very long locks (30 sec and more) on
the table which is holding
the user's posts. There are aprox. 3.3 Mio records in that table which
consumes
a size of 1.5 GB. The table-format is myisam.

So far switching to innodb mostly soluted my problems with table locks.
But because of the table
size and the limited Memory I can't switch to inno-db in this case.

Are there any other conceptional or technical ideas how to reduce the
(long lasting) table locks ?

Thanks for any idea in advance !

Best regards,
  Marco
  


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

Re: Too many table-locks

[Dan Buettner]

Marco, it would be more helpful if you could post the table structures
involved (SHOW CREATE TABLE tablename) as well as the SQL query
that is giving you this problem.

Off the top of my head:
- InnoDB is designed to solve this exact problem; maybe re-visit why
you cannot use it, given that it solves your problem.
- Increase your MySQL cache settings to allow holding more of your data in RAM
- Move older posts into an archive table to keep your live or more
recent posts more responsive.  The MERGE feature may be very helpful
in this area.

Dan

On 8/21/06, Marco Simon [EMAIL PROTECTED] wrote:

Hi everybody,

I've got a little problem with a web and mysql based
bulleting-board-application.
The board is quite well visited and users are writing aprox. 1 new post
per second. In total the db gets aprox. 250 queries/sec.

The webserver and mysql-server are running on different hosts, the db server
is running on

Linux db 2.6.14.2 #4 SMP Thu Nov 17 09:54:44 CET 2005 x86_64 GNU/Linux

model name  :   Intel(R) Pentium(R) D CPU 3.00GHz
stepping: 4
cpu MHz : 3000.229
cache size  : 1024 KB

4GB RAM.

My problem is that I get a lot and very long locks (30 sec and more) on
the table which is holding
the user's posts. There are aprox. 3.3 Mio records in that table which
consumes
a size of 1.5 GB. The table-format is myisam.

So far switching to innodb mostly soluted my problems with table locks.
But because of the table
size and the limited Memory I can't switch to inno-db in this case.

Are there any other conceptional or technical ideas how to reduce the
(long lasting) table locks ?

Thanks for any idea in advance !

Best regards,
  Marco





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

Re: Why do these transactions show table locks?

[Heikki Tuuri]

Robert,

- Original Message - 
From: Robert DiFalco [EMAIL PROTECTED]

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 [mailto:[EMAIL PROTECTED]
Sent: Sunday, February 26, 2006 9:33 AM
To: mysql@lists.mysql.com; [EMAIL PROTECTED]
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_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,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_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,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_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,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_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,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/[EMAIL PROTECTED]




--
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]

Why do these transactions show table locks?

[Robert DiFalco]

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_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,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_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,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_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,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_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,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/[EMAIL PROTECTED]

RE: Why do these transactions show table locks?

[Robert DiFalco]

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=16229 

-Original Message-
From: Robert DiFalco [mailto:[EMAIL PROTECTED] 
Sent: Sunday, February 26, 2006 9:33 AM
To: mysql@lists.mysql.com; [EMAIL PROTECTED]
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_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,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_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,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_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,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_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,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/[EMAIL PROTECTED]




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

Re: MySQL 4 goes to sleep with table locks?

[Alfredo Cole]

El Sáb 07 Feb 2004 02:28, escribió:
 Mr. Alfredo
 Pls, will you give the complete structure as 'create query' of your
 database? that will help us to solve your problem.
 Pradap

This is the structure of the table that holds the sequential numbers for 
various documents that need them:

CREATE TABLE `invcorr` (
  `empresa` tinyint(2) unsigned zerofill NOT NULL default '00',
  `tienda` tinyint(2) unsigned zerofill NOT NULL default '00',
  `ultfac` bigint(9) unsigned zerofill NOT NULL default '0',
  `ultfaccred` bigint(9) unsigned zerofill NOT NULL default '0',
  `ultconsig` bigint(9) unsigned zerofill NOT NULL default '0',
  `ultdev` bigint(9) unsigned zerofill NOT NULL default '0',
  `ultped` bigint(9) unsigned zerofill NOT NULL default '0',
  `ulting` bigint(9) unsigned zerofill NOT NULL default '0',
  `ultapar` bigint(9) unsigned zerofill NOT NULL default '0',
  `ultcoti` bigint(9) unsigned zerofill NOT NULL default '0',
  `ulttras` bigint(9) unsigned zerofill NOT NULL default '0',
  `ultreq` bigint(9) unsigned zerofill NOT NULL default '0',
  `ultajus` bigint(9) unsigned zerofill NOT NULL default '0',
  `timestamp` timestamp(14) NOT NULL,
  `usuario` varchar(20) default NULL,
  PRIMARY KEY  (`empresa`,`tienda`)
) TYPE=MyISAM; 

The condensed extract of the C program tha does the locking is as follows:

SQLinst = lock tables invcorr write, invfacturas write;
state = mysql_query(connection, SQLinst);
if(state != 0)
{
wxMessageBox(mysql_error(mysql), Error:, wxOK | 
wxICON_EXCLAMATION, 
this);
wxMessageBox(_(Could not lock table!), _(Error:), wxOK | 
wxICON_EXCLAMATION, this);
return;
}
SQLinst = select ultfac from invcorr where empresa='
+ sCodigoEmp
+ ' and tienda='
+ sFacTienda
+ ' order by tienda;
state = mysql_query(connection, SQLinst);
result = mysql_store_result(connection);
if(mysql_num_rows(result) != 0) // Tiene registros
{
while((row = mysql_fetch_row(result)) != NULL)
{
// Asigno valores
sFacDocu = row[0] ? row[0] : ;
}
}
mysql_free_result(result);
sFacDocu.ToDouble(dFacDocu);
dFacDocu++;
sFacDocu.Printf(%09.0f, dFacDocu);
SQLinst = update invcorr set ultfac='
+ sFacDocu
+ ' where empresa='
+ sCodigoEmp
+ ' and tienda='
+ sFacTienda + ';
state = mysql_query(connection, SQLinst);
SQLinst = unlock tables;
state = mysql_query(connection, SQLinst);

This final update is what seems to sleep and not react inmediately. The 
application resides in one central server with dual Xeon 2.8 Ghz CPU's and 6 
Gb RAM. All users run the application at the server connecting either via 
ssh, VNC or Linux Terminal Server Project.

During the day, I will get 3 or 4 duplicate numbers per store. Given the fact 
that reporting sales tax received, depends on a correct sequence of invoices, 
my company could get into big trouble if authorities think we are trying to 
avoid sales tax reporting.

Thank you for your help.

-- 
Alfredo J. Cole
[EMAIL PROTECTED]
[EMAIL PROTECTED]


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

MySQL 4 goes to sleep with table locks?

[Alfredo Cole]

I am using Mandrake 9.1 and MySQL 4.0.11 from the LM CD's.

In my application, I have a table that stores the sequential numbers of 
invoices prepared by several stores. Every time a salesman prepares an 
invoice for a customer, the system goes to this table, locks it with lock 
tables table write, reads the number of the last invoice made for that 
store, adds one to that number, updates the field, and unlocks the table. In 
theory, I should never get a duplicate invoice, but in practice, I do. So, it 
seems that MySQL maintains, under some special circumstances, the same number 
and does not update it. Maybe there is a parameter in my.cnf I could change 
to make sure all updates are processed inmediately?

Auto increment field would not apply in this case, since there is only one 
record per store that gets updated for every invoice. I would appreciate any 
advise. Thank you.

-- 
Alfredo J. Cole
[EMAIL PROTECTED]
[EMAIL PROTECTED]


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

query re: Innodb Table Locks

[Erik DeBattista]

to whom it may concern,

I need some help regarding transactions using innodb tables.

I am running MySQL 3.23.51-Max as database server and developing a web
front-end using ASP. Many of the tables used are of innodb format since it
is imperative for me to use transactions in most of my scripts.

In 95% of the cases the scripts work fine but at times this error pops up

'Lock Wait Timeout Try Restarting Transaction'

This leaves certain tables locked for a quite a number of hours and the
only possibilty to recover the table is to restart mysql. Now my software
is still at the testing stage and so i'm the only user. But when going live,
15 people will be using it concurrently and restarting my sql each time to
avoid the table lock will not be possible.

How can the deadlock be avoided?

I have set innodb_lock_wait as 60 in my.cnf file, is this enough?

as for the transaction syntax i am using the following

Begin;

on error resume next

sql statements

If err then
rollback;
else
commit;
end if

on error goto 0


am i doing something wrong? will set autocommit=0; be useful?
or is there any way to recover from the table lock?

i would really appreciate a reply

Best Regards
Erik DeBattista
Systems Developer
Webcraft Ltd.

--CONTACT DETAILS---
www.webcraft.com.mt
Email: [EMAIL PROTECTED]
Tel: +356 21421540  Fax: +356 21419300





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: bug? - FLUSH PRIVILEGES releases table locks

[Donal Diamond]

On Thu, Oct 17, 2002 at 05:47:02PM +0200, [EMAIL PROTECTED] wrote:
 Your message cannot be posted because it appears to be either spam or
 simply off topic to our filter. To bypass the filter you must include
 one of the following words in your message:
 
 sql,query
 
 If you just reply to this message, and include the entire text of it in the
 reply, your reply will go through. However, you should
 first review the text of the message to make sure it has something to do
 with MySQL. Just typing the word MySQL once will be sufficient, for example.
 
 You have written the following:
 
 I've come across some weird behavior with active table locks 
 and flushing privileges - is this a bug?
 
 If you lock any table using LOCK TABLES test.locktest READ and later
 issue a FLUSH PRIVILEGES in the same client, then the lock is released.
 
 If you use FLUSH TABLES WITH READ LOCK problem does not occur.
 
 I have tested this in 3.23.47 and 3.23.52 on FreeBSD4.6
 
 TEST:
 
 CREATE DATABASE test;
 USE test;
 CREATE TABLE locktest ( foo VARCHAR(50) );
 
 1. Client a:
 USE test
 LOCK TABLES locktest READ;
 
 2. Client b:
 USE test
 INSERT locktest VALUES (lock_damn_it);
 ..Client b waits
 
 3. Client a: 
 FLUSH PRIVILEGES
 
 client b:
 .Client b insert in processed
 
 Regards,
 Donal
 
 -- 
 
 Donal Diamond
 Technical Planning  Support
 Eircom Multimedia Infrastructure
 
 

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Table Locks...

[Gelu]

Hi,
You can use show full processlist to see what table is locked and unlock
tables;
Regards,
Gelu
_
G.NET SOFTWARE COMPANY

Permanent e-mail address : [EMAIL PROTECTED]
  [EMAIL PROTECTED]
- Original Message -
From: Sukhdev Sethi [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, April 20, 2002 9:44 PM
Subject: Table Locks...


 Hi,

 Lets say that I am the admin on mysql server, how can
 i know which tables currently are locked? And if
 they are locked by someone else, how to do I remove
 the locks.

 Thank you.
 Rajan.


 __
 Do You Yahoo!?
 Yahoo! Games - play chess, backgammon, pool and more
 http://games.yahoo.com/

 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Table Locks...

[Sukhdev Sethi]

Hi,

Lets say that I am the admin on mysql server, how can
i know which tables currently are locked? And if
they are locked by someone else, how to do I remove
the locks.

Thank you.
Rajan.


__
Do You Yahoo!?
Yahoo! Games - play chess, backgammon, pool and more
http://games.yahoo.com/

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php