| 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) |
| Version: | 4.1.1 | OS: | Linux (LINUX AND WINDOWS XP PROFESSIONA) |
| Assigned to: | Timothy Smith | CPU Architecture: | Any |
[11 May 2004 22:23]
Roberto Spadim
[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, Timothy
[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! like 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
Roberto, 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, Timothy
[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 SELECT a,b FROM c and the result shows +----------+-----------+ | |ñöW® | ê▼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 FLUSH TABLES and then... SELECT a,b FROM c 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'
}
UNLOCK TABLES
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... Thanks
[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.
