Bug #3720 READLOCK
Submitted: 11 May 2004 22:23 Modified: 29 Mar 2011 18:23
Reporter: Roberto Spadim (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S1 (Critical)
Assigned to: Timothy Smith CPU Architecture:Any

[11 May 2004 22:23] Roberto Spadim
Many Process make server crazy on windows and linux, the release of readlock never happen, the program don't use LOCK TABLE, and use BEGIN and COMMIT at the end of every refresh on the page.....
all others databases (Diferent from spd, dev, and sps) are locked when the Waiting for release of readlock status happen... 

How to repeat:
UPDATE server_2_http_session SET value="1084306734" WHERE sid="10843066474153136880183475059323566" 
from http session class (made in php) it's update the time when session will expire

UPDATE server_2_http_session SET value="1084306734" WHERE sid="10843066474153136880183475059323566" => IS THE [.....] in process list result
1  rspadim  localhost:2217  NULL  Sleep  4    NULL  
3  rspadim  badwolf:2219  spd  Sleep  3    NULL  
5  rspadim  DB1:33862  spd  Query  9  Waiting for release of readlock  UPDATE ...
6  rspadim  DB1:33863  spd  Sleep  119    NULL  
8  rspadim  DB1:33864  dev  Sleep  125    NULL  
9  rspadim  DB1:33865  dev  Sleep  121    NULL  
11  rspadim  DB1:33866  spd  Query  119  Waiting for release of readlock  UPDATE .....
16  rspadim  badwolf:2232  spd  Query  0  NULL  SHOW PROCESSLIST  
20  rspadim  DB1:33868  spd  Query  66  Waiting for release of readlock  ......
21  rspadim  DB1:33869  dev  Query  61  Waiting for release of readlock  .........
23  rspadim  DB1:33870  spd  Query  58  Waiting for release of readlock  ....
26  rspadim  DB1:33871  dev  Query  39  Waiting for release of readlock  ...

Suggested fix:
KILL PROCESS, but i can't stop the rest of process... :( !
[17 May 2004 17:27] Timothy Smith
Hello, Roberto.  Can you please give me some more information on this bug?

First, are you using MyISAM tables or InnoDB tables?  (It sounds like InnoDB, but I want to check.)  Are you perhaps using a mixture of MyISAM and InnoDB?

How large are these tables?  How many rows?  How are they defined?  (Especially, knowing what indexes there are on the tables.)

There is not enough information in your bug report for me to repeat the problem.

Thank you,

[18 May 2004 4:09] Roberto Spadim
CREATE TABLE `$table_name` (
`sid` varchar(100) binary NOT NULL default '',
`var` varchar(125) binary NOT NULL default '',
`value` varchar(255) binary NOT NULL default '',
PRIMARY KEY  (`sid`,`var`)

I was using HEAP type, and changed to MYISAM but the problem persists....
it's a session controler... about 12 lines per user, 
12 * 15 users = 180 lines... it's have a 15 queries per seconds on this table... i only use this queries:
SELECT SQL_CACHE value FROM $table_name WHERE sid="$sid" AND var="$var"
DELETE FROM $table_name WHERE sid="$sid"
UPDATE $table_name SET value="$value" WHERE sid="$sid" AND var="$var"
INSERT INTO $table_name SET sid="$sid", var="$var", value="$value";

Servers fasters  than pentium 3 433 migth not crash under 15 queries per seconds... but with many users it's wait a long time to unlock tables.... and on pentium 3 433 the table don't get unlocked... the swap partition on linux is not in use, the server have 192 mb and about 16 mb free.... (pentium 3 433), others machines (pentium 4 1.8) that don't see this problem have 256 mb, 30 mb on swap partition in use...
[18 May 2004 4:11] Roberto Spadim
when it's crash all server still locked! doesn't matther if the table with lock is on database A and table of the others queries are on database B C D or E....  the top on linux seeing to have a 99% of cpu some times in mysqld process but it's stop on the next refresh... the memory don't grow up, it's seeing to be stable without malloc or free error...
[18 May 2004 4:14] Roberto Spadim
on linux the server run with nice value of -15, on windows it's run on normal priority
[18 May 2004 4:36] Roberto Spadim
some times server stop responding and show "unknown mysql error" or something like this (i'm don't remmenbernow...) and then i make
FLUSH TABLES and it's return to normal operation.... butt.!!!
the fields from tables are showed with wrong names!
SELECT a,b FROM teste WHERE a>100 AND b<100
it's show (a single example...)
@Q#$ ~!
100   3
200  43
300  12
400   2
500  44
For me i don't see problem cause i'm using mysql_result (php function) using number of the field 0 or 1 in this case... and not the name...
[25 May 2004 7:55] Roberto Spadim
Mysql Server Showed error: 1223 -  Can't execute the query because you have a conflicting read lock, When i hadn't any read lock!
[25 May 2004 8:12] Roberto Spadim
INNODB don't show this problem! Just Heap AND MyIsam!!!!
[25 May 2004 8:13] Roberto Spadim
I changed the table to innodb and no readlock was showed
[2 Jun 2004 20:39] Timothy Smith
sorry for the delay, and thanks for the extra info.  I will try to reproduce this problem.
[28 Jun 2004 20:50] Timothy Smith

Hi, I tried again to repeat this and can't.  Does it still happen for you if you use MySQL 4.1.2?

I'm attaching a test case written in Perl - can you see if you can modify the test case so that it can repeat the problem on your system?

Can you please attach the output of 'mysqladmin var ext' to this bug?

If you turn off the query cache (set global query_cache_type = 0), does everything work fine then?

Thank you,

[28 Jun 2004 20:51] Timothy Smith
test program - please modify it so that it causes the problem

Attachment: bug3720.pl (application/octet-stream, text), 1.73 KiB.

[28 Jun 2004 20:53] Timothy Smith
By the way, I ran that test program like this:

for i in 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1; do ./t/bug3720.pl &; done

I did that a few times - so there were about 50 different processes all randomly selecting, inserting updating and deleting.
[1 Jul 2004 2:07] Roberto Spadim
Hello tim, sorry for the delay too :), i changed to 4.1.2 and the problem appears to be cleaned, but i still with problems like 
and the result shows
| |ñöW®    | ê&#9660;5a4      |
| 1            | 2             |
| 3            | 2             |
i didn't turn off the query cache... i will try now to do this...

this problem stops when i execute
and then...
and the result shows
| a  | b  |
| 1  | 2  |
| 3  | 2  |
wait some days to the result, thanks!
[1 Jul 2004 2:14] Roberto Spadim
try to make you tests with myisam, and 
lock table "table" write, "table2" write
update table ....
insert into table2.....
unlock tables
select .....
the program make this...

LOCK table1 READ
SELECT * FROM table1

LOCK table2 WRITE, table3 WRITE
for { (php source (for ($i=0;$i<mysql_numrows($table1);$i++))
INSERT INTO table2 (fields from table1);
UPDATE table3 SET saldo=saldo-valor WHERE primary_key='key_value'
free $table1
[4 Jul 2004 7:44] Roberto Spadim
Hello, I get a error in mysql-front when doing the tests...
Windows Xp:
Access Violation at address 77f52216 in module 'ntdll.dll'. Read Of Address fffffff8

I hadn't visual studio to get memory informations, the query cache was turned on!

I will Try to Restart the Server and Continue the test...
[29 Jul 2004 1:54] Roberto Spadim
The bug was found...
The problems occur when I use old client library... (MySQL Front-2.5) on windows, i changed to mysqlcc from last mensage, and the bug don't occurred again, maybe old protocol is changing locking the server?!
The query cache was turned on again, and no problems...
On next report I will call if mysqlfront is craching...
I will use it again, thanks

Maybe a SHOW READLOCKS could help us...
[14 Feb 2005 22:54] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[29 Mar 2011 18:23] Roberto Spadim
flush table with readlock 
worked here, feature request/bug closed.