473,898 Members | 2,558 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

InnoDB, 4.1.20, INS/DEL/TX -- Why table-level locking?

Just started investigating InnoDB after having used MyISAM tables in
the 4.0.12 version, almost immediately ran into a locking issue with
INSERTs, DELETEs, and transactions.

Given the following table:
CREATE TABLE test1 (
f1 varchar(32) default NULL
)

and a steady stream of INSERTs from a command-line shell:
jc@soyuz:~$ while true; do /usr/local/mysql/bin/mysql -S
/tmp/mysql-4.1.sock -u tester -D jlb -A -N -s -q -e "INSERT INTO test1
VALUES('`date`' )"; done

why is it that the following session blocks the INSERTs due to locking?
jc@soyuz:~/tmp$ /usr/local/mysql/bin/mysql -S /tmp/mysql-4.1.sock -u
tester -p -D jlb

mysqlBEGIN ;
Query OK, 0 rows affected (0.00 sec)

mysqlDELETE FROM test1 WHERE f1 LIKE 'asdf;ljkas;fjd ;adsfads%' ;
Query OK, 0 rows affected (0.05 sec)

mysqlshow innodb status \G
*************** ************ 1. row *************** ************
Status:
=============== =============== =======
060707 9:34:36 INNODB MONITOR OUTPUT
=============== =============== =======
Per second averages calculated from the last 38 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 251, signal count 249
Mutex spin waits 343, rounds 2273, OS waits 72
RW-shared spins 156, OS waits 77; RW-excl spins 100, OS waits 95
------------
TRANSACTIONS
------------
Trx id counter 0 10526
Purge done for trx's n:o < 0 7801 undo n:o < 0 0
History list length 0
Total number of lock structs in row lock hash table 11
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 10525, ACTIVE 7 sec, OS thread id 15 inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320
MySQL thread id 9256, query id 18746 localhost root update
INSERT INTO test1 VALUES('Fri Jul 7 09:34:29 EDT 2006')
------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1 page no 13 n bits 336 index `GEN_CLUST_INDE X`
of table `jlb/test1` trx id 0 10525 lock_mode X in
sert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; 1-byte offs TRUE;
info bits 0
0: len 9; hex 73757072656d756 d00; asc supremum ;;

------------------
---TRANSACTION 0 10524, ACTIVE 7 sec, OS thread id 13
11 lock struct(s), heap size 1024
MySQL thread id 9150, query id 18747 localhost root
show innodb status
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
26 OS file reads, 9848 OS file writes, 9604 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 18.76 writes/s, 18.39 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 415141, used cells 1, node heap has 1 buffer(s)
17.66 hash searches/s, 0.34 non-hash searches/s
---
LOG
---
Log sequence number 0 1673501
Log flushed up to 0 1673501
Last checkpoint at 0 1673501
0 pending log writes, 0 pending chkp writes
9283 log i/o's done, 17.97 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 141111128; in additional pool allocated 1403776
Buffer pool size 6400
Free buffers 6321
Database pages 78
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 64, created 14, written 642
0.00 reads/s, 0.05 creates/s, 0.82 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread id 10, state: waiting for server activity
Number of rows inserted 9168, updated 1062, deleted 8865, read 66649
17.87 inserts/s, 0.00 updates/s, 0.00 deletes/s, 71.42 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
=============== =============

1 row in set (0.03 sec)

mysql>

Jul 7 '06 #1
0 2315

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
6435
by: azamka | last post by:
Hi everyone, I am trying to create tables with type innodb. I created a database and all the tables of type ISAM. Now I am at the point of making relations and establish refrential integrity. For that ofcourse I have to change the tables in the Innodb type. I did that with the commad "alter table tablename type = innodb". Command runs fine but the table type doesnt change. I tried to create a new table of type innodb but its not working...
0
1750
by: Morten Gulbrandsen | last post by:
Dear MySQL developers, Could some experienced Database developer please take a look at this ? It is supposed to be plain SQL2. How can it be coded under MySQL Especially all referential triggered actions.
0
650
by: Morten Gulbrandsen | last post by:
Hi Programmers, after this legal statement ALTER TABLE EMPLOYEE TYPE =3D InnoDB; I get=20 Warnings: 0 =20 and still MySQL claims to have to use Type =3D MyISAM,
3
3153
by: Vanchau Nguyen | last post by:
Hi all, Hoping someone can help us with this strange Innodb problem. At about 5:15 AM 2/2/04 (this morning), our slave server stopped accepting connections. The machine was pingable, but you could not SSH into the machine. We had to manually reboot the machine, and restart the slave. Looking in the error file we found this:
2
2419
by: skidvd | last post by:
Hello: I have just recently converted to using the InnoDB table type so that I can enforce FOREIGN key constraints. I have been using MyISAM tables (accessed via JDBC) successfully for some time. However, I have just come across a problem with the new configuration that boggles my mind.... First some configuration data:
0
1322
by: Mike Chirico | last post by:
Hopefully the following will be useful or interesting: TIP 29: An example of using referential integrity with InnoDB tables. Referential Integrity with InnoDB tables. STEP 1 (First create the tables as InnoDB) CREATE TABLE agents (
1
3573
by: Beyonder | last post by:
I'm using InnoDB with Mysql and I've used delete to delete over 10 million records, but the InnoDB database is still the same size on the disk (over 100 gigs) is there a way to get Mysql/InnoDB to shrink or trim this size? and also, can I set a maximum size and make it so itll rotate itself, as in purge out old entries as new ones come in when it hits the maximum? I'm not used to InnoDBs... so I could use some help with this...
1
2362
by: LRW | last post by:
I'm using mySQL 4.0.20, interfacing it with phpMyAdmin 2.5.6. I usually set tables up with a "default" type, which I believe is myISAM (or whatever that one is.) But I decided to go with InnoDB for the stability and accuracy. Noticed something on tables in that format I've never noticed in the other format: tbl_product 1 InnoDB - Master Product Table; InnoDB free: 4096 kB tbl_users 1 InnoDB - User Management...
3
2731
by: siliconmike | last post by:
We have developed a site where users can post blogs. Now, each blog is stored in a separate row, in a table called blog_table. This single table would be the most active one and would grow to large size in the coming years. What is the most advisable engine for this table - Innodb or MyISAM ?
1
5155
by: Gh! | last post by:
How can I see how much disk space is being occupied by an InnoDB table? Looking into /var/lib/mysql doesn't seem to work anymore for InnoDB tables. Please tell also if there is a way to see how much space is occupied by a particular row or column of a table. Thanks
0
9993
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
1
10954
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10487
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9662
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8036
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6078
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4708
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4297
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3308
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.