I have a problem that I can't understand readily. I have a database that has a
couple of tables that lock for a recognizable period of time. The reason I
know is because during the lock the application stops responding totally. The
storage engine is MyIsam. I have reread everything about loc
On Sat, Feb 6, 2010 at 12:11 PM, D. Dante Lorenso wrote:
> I have a system that imports about 40 million records every 2 days into a
> single table in MySQL. I was having problems with LOAD DATA CONCURRENT
> LOCAL INFILE where the table I was importing into would lock until the
> import was compl
I have a system that imports about 40 million records every 2 days into
a single table in MySQL. I was having problems with LOAD DATA
CONCURRENT LOCAL INFILE where the table I was importing into would lock
until the import was complete. Locks would prevent SELECTs also.
I converted the table
Hi,
I'm having a strange problem with table locking. I have written a stored
procedure that only accesses a single table. Before executing the
procedure, I'm trying to lock the table in question for writing. The
LOCK TABLE command succeeds, but when I execute the stored procedure it
(31) 3272 - 0226 / 9114 - 7695
- Mensagem original
De: Rob Wultsch <[EMAIL PROTECTED]>
Para: JW <[EMAIL PROTECTED]>
Cc: mysql@lists.mysql.com
Enviadas: Domingo, 11 de Maio de 2008 0:04:17
Assunto: Re: Table Locking (Was: Best CPU config for a busy DB server)
On Sat, May
On Sat, May 10, 2008 at 4:24 PM, JW <[EMAIL PROTECTED]> wrote:
>> Table locking will occur with MyISAM tables when any row(s) of the table is
>> being updated (Update,Delete,Insert,Load Data etc).
>> If you are only executing Select statements, then they can be executed in
> Table locking will occur with MyISAM tables when any row(s) of the table is
> being updated (Update,Delete,Insert,Load Data etc).
> If you are only executing Select statements, then they can be executed in
> parallel and won't be blocked.
Just curious: you say "with MyIS
g to such implicit
lock such as when you you read from a table (SELECT) or only explicit table
locking, which we don't (currently) use in any of our code.
Does anyone know?
JW
Table locking will occur with MyISAM tables when any row(s) of the table is
being updated (Update,Dele
locks a table, then no other threads that need that table
can execute until the locking thread/query is complete. Short answer: MySQL
works well on multi-core machines until you lock a table."
One of our programmers was wondering if this is referring to such implicit
lock such as when
Hi,
From the MySQL manual @
http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html
The MyISAM storage engine supports concurrent inserts to reduce
contention between readers and writers for a given table: If a MyISAM
table has no free blocks in the middle of the data file, rows are al
My problem is the following and I thank you in advance on any help
offered. Show Innodb Status shows that my DB server has no free
buffers. What is the significance of this, what causes this, and how
bad is it?
Here is the relevant part of the output of that command:
BUFFER POOL AND MEMORY
--
Hello.
Every thing depends on logic of your application. But I don't
see a big gain of using table-level locks on InnoDB. Among others
this might be helpful:
http://dev.mysql.com/doc/mysql/en/internal-locking.html
Scott Klarenbach <[EMAIL PROTECTED]> wrote:
> I've got a good
I've got a good deal of experience using mysql, but never in a large
production environment with many concurrent users.
Using the InnoDB engine, what is the general practice for ensuring
data integrity when multiple users are writing to the same table?
Should I explicitly lock the table before I
TECTED]
> Sent: Wednesday, December 01, 2004 11:22 PM
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: Re: Table Locking Problem? Very Slow MyISAM DB - PLS HELP!
>
>
> >The reason I ask is because eight select statements should not bog down a
> >production serve
When the server begins to slow down, what does top reveal?
Andrew Nelson wrote:
The reason I ask is because eight select statements should not bog
down a production server. On the MySQL side, is anything being
written to the slow query log? On the application side is there any
virus scanning or
The reason I ask is because eight select statements should not bog down a
production server. On the MySQL side, is anything being written to the slow
query log? On the application side is there any virus scanning or similar
activity being performed? Does iostat show any heavy reading or writing
The reason I ask is because eight select statements should not bog down
a production server. On the MySQL side, is anything being written to the
slow query log? On the application side is there any virus scanning or
similar activity being performed? Does iostat show any heavy reading or
writing
Hi Victor,
How did you deduce that the database server is the bottleneck? Are all your
processes running on the same machine?
Because 'ps -aux' shows it running at 94% of the CPU and when I
stop/start the mysql server, it seems to be ok again for another
hour.
Any ideas?
Andrew Nelson wrote:
Hi
Hi,
I have a MySQL 3.23.55 server managing accounts on my exim mail server..
The table type on all tables MyISAM.. I have the MTA performing various
queries
for each incoming email - determining mail aliases, vacation messages and
filtering rules etc but they're all pretty much SELECT statements
How did you deduce that the database server is the bottleneck? Are all
your processes running on the same machine?
Andrew Nelson wrote:
Hi,
I have a MySQL 3.23.55 server managing accounts on my exim mail server..
The table type on all tables MyISAM.. I have the MTA performing
various queries
fo
I recently migrated a myisam database to a dual processor AMD Opteron
(see specs below) host and found that tables would sporadically lock and
not release and so all subsequent inserts would be blocked. In this
state, I was unable to nicely restart the server. It would simply give
up. Repairing
Hi!
On Oct 05, Ville Mattila wrote:
> Hi there,
>
> I have noticed a few things that cause problems when using table aliases
> (SELECT ... FROM table1 t1, table2 t2):
>
> 1) Fulltext index queries don't work. I tried to complete a following query:
>
> a)
> SELECT p.*, c.name AS categoryname FR
Hi there,
I have noticed a few things that cause problems when using table aliases
(SELECT ... FROM table1 t1, table2 t2):
1) Fulltext index queries don't work. I tried to complete a following query:
a)
SELECT p.*, c.name AS categoryname FROM products p, categories c WHERE
MATCH(p.name,p.descrip
Thanks for the message. I should rephrase - the data set is millions of
rows, but the tables are indexed, and an EXPLAIN looks like it is using
indexes effectively. The query produces the exact same results both
times (with and without LOCKing). Is there a reason that by calling the
query vi
On Tue, 27 Apr 2004, Scott Switzer wrote:
> Hi,
>
> I am having a difficult time with a query. My environment is MySQL
> v4.0.16 (InnoDB tables) running on Linux (latest 2.4 kernel).
> Basically, I am running a query of the form:
>
> INSERT INTO temp_tbl
> SELECT c1,c2...
> FROM t1,t2,t3,t4
> WHE
connections might be an option, but you can't guarantee
your CGI will get the same connection and be able to release the lock!
Hope this helps,
Ken
- Original Message -
From: "Andy Ford" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, April 26, 2
Andy Ford wrote:
I have a perl cgi script that needs to lock the tables. Unfortunately,
as HTTP is a one shot protocol, once the cgi script completes execution,
the tables are unlocked.
I need it to stay locked until another cgi script unlocks them!
Is there any other way of achieving this!?
You c
I have a perl cgi script that needs to lock the tables. Unfortunately,
as HTTP is a one shot protocol, once the cgi script completes execution,
the tables are unlocked.
I need it to stay locked until another cgi script unlocks them!
Is there any other way of achieving this!?
Thanks
Andy
--
Hi,
I am having a difficult time with a query. My environment is MySQL
v4.0.16 (InnoDB tables) running on Linux (latest 2.4 kernel).
Basically, I am running a query of the form:
INSERT INTO temp_tbl
SELECT c1,c2...
FROM t1,t2,t3,t4
WHERE ...
It is a very complex query, which looks at millions
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Thursday, April 22, 2004 8:09 PM
> To: [EMAIL PROTECTED]
> Subject: mySQL: Table locking problems when non-index keys used
>
>
> Friends,
>
> Sorry to post this question again. I got a message saying that the
> server could
Friends,
Sorry to post this question again. I got a message saying that the server couldn't
transfer this message to some groups. Also I didn't get any response to this
question.
We are using mysql 4.0.17 with innodb option. In a query, when a WHERE clause
contains a non-indexed colu
Hi Friends,
We are using mysql 4.0.17 with innodb option. In a query, when a WHERE clause
contains a non-indexed columns, it locks the entire table instead of row lock. Is
there any solution apart from building index on each query key ? Is there a solution
in any of the later versions ?
[EMAIL PROTECTED] wrote:
Here is the problem. I am getting random table locks in my databases on a new
server running Fedora 0.96 and Mysql 3.23.58-7.
It seems that different tables will "lock" -- what I mean by that is when I try
to execute a query on them, mysql just freezes up -- it doesn't
If you've read my last email, you know what the problem is, but I have now
pinpointed it a bit further using the processlist feature.
[EMAIL PROTECTED] MBPlatforms]# mysqladmin -v processlist -p
Enter password:
+-+--+---+-+-+--++-
Here is the problem. I am getting random table locks in my databases on a new
server running Fedora 0.96 and Mysql 3.23.58-7.
It seems that different tables will "lock" -- what I mean by that is when I try
to execute a query on them, mysql just freezes up -- it doesn't crash or return
errors it
On Mon, Aug 18, 2003 at 11:15:31AM -0700, [EMAIL PROTECTED] wrote:
> I could fix this if I changed the table type to InnoDB, correct?
Yes.
InnoDB uses a very different locking model. You'll get very good
read/write concurrency with InnoDB.
Jeremy
--
Jeremy D. Zawodny | Perl, Web, MySQL, L
I could fix this if I changed the table type to InnoDB, correct?
-James
Quoting Jeremy Zawodny <[EMAIL PROTECTED]>:
> On Mon, Aug 18, 2003 at 10:41:55AM -0700, [EMAIL PROTECTED] wrote:
> > Hello,
> >
> > I just have a question about the table locking in 3.23.56. If
On Mon, Aug 18, 2003 at 10:41:55AM -0700, [EMAIL PROTECTED] wrote:
> Hello,
>
> I just have a question about the table locking in 3.23.56. If I am inserting a
> row into a table that is MyISAM type, is the entire table locked?
Yes.
> And, if so, what does this mean for concurre
In the last episode (Aug 18), [EMAIL PROTECTED] said:
> I just have a question about the table locking in 3.23.56. If I am
> inserting a row into a table that is MyISAM type, is the entire table
> locked? And, if so, what does this mean for concurrent selects?
Inserting a row into a f
-->-Original Message-
-->From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
-->Sent: Monday, August 18, 2003 10:42 AM
-->To: [EMAIL PROTECTED]
-->Subject: Table locking...
-->
-->Hello,
-->
-->I just have a question about the table locking in 3.23.56. If I am
-
Hello,
I just have a question about the table locking in 3.23.56. If I am inserting a
row into a table that is MyISAM type, is the entire table locked? And, if so,
what does this mean for concurrent selects?
-James
--
MySQL General Mailing List
For list archives: http://lists.mysql.com
It would appear that MySql does table locking. Then I would like to ask
the
following:
> From what I've read,
> it would seem that SELECTS get a lower priority than INSERTS and UPDATES,
> meaning that if all 30 users
> insert into that table at the same time (given the way t
On Mon, Feb 17, 2003 at 11:22:27AM +0200, Rob wrote:
> Thanks
>
> It would appear that MySql does table locking. Then I would like to
> ask the following:
Hi Rob,
I think that you missed some important details.
Taken from the manual:
"MySQL only supports table
Thanks
It would appear that MySql does table locking. Then I would like to ask the
following:
We have a documents table in our Document Management System, which obviously
gets used quite a
lot :-). We're running stress tests on our application (which is a PHP,
Apache, MySql combo)
and
> Can I just check, does MySql v.3.23.52 do table locking or row locking?
If
> it only does table locking, are there any versions of mysql that do row
> locking?
Always Read The Fine Manual:
>From http://www.mysql.com/doc/en/Locking_methods.html:
"Currently MySQL only suppo
On Mon, Feb 17, 2003 at 11:00:37AM +0200, Rob wrote:
> Hi all,
>
> Can I just check, does MySql v.3.23.52 do table locking or row locking? If
> it only does table locking, are there any versions of mysql that do row
> locking?
Detailed in the fine manual:
http://www.m
Hi all,
Can I just check, does MySql v.3.23.52 do table locking or row locking? If
it only does table locking, are there any versions of mysql that do row
locking?
Thanks
---
Rob
**
Rob Cherry
mailto:[EMAIL PROTECTED]
+27 21 447 7440
Jam Warehouse RSA
Smart Business
Hi all,
Can I just check, does MySql v.3.23.52 do table locking or row locking? If
it only does table locking, are there any versions of mysql that do row
locking?
Thanks
---
Rob
**
Rob Cherry
mailto:[EMAIL PROTECTED]
+27 21 447 7440
Jam Warehouse RSA
Smart Business
Hello, list,
I'm having a weird issue with table locking. I'm running MySQL 3.23 on a
Win2k server, used for eCommerce applications.
All settings appear to be in order, that is, KeepAlive is 30, connection
pooling is on, with a timeout of 30.
The tables in any given database seem
(Perl Script mysqlbug not used as Perl is not installed)
SUBJECT:Table locking under ISAM can fail
ORGANIZATION: Owens Services B.O.P. Ltd, New Zealand
LICENCE:None (expired support license)
SYNOPSIS: User can cause table locking to fail by USE'ing the same databas
At 8/15/2002, you wrote:
>Hi everyone!
>
>I'm fairly familiar with the table locking methods in MySQL but I have a
>problem I can't solve.
>
>I have two main tables which are read simultaneously by many clients. The
>clients only do reads, but they do it very e
Hi everyone!
I'm fairly familiar with the table locking methods in MySQL but I have a
problem I can't solve.
I have two main tables which are read simultaneously by many clients. The
clients only do reads, but they do it very extensively and for 30 secs each.
The two main tables nee
I had put a similar question (that unfortunately never got answered) on
this mailing list and so I have pondered about this problem also. The
way I see it, delayed_insert_timeout doesn't do any good here because it
only determines how long an idle (with no pending jobs) insert delayed
thread will
lorenzo.kh,
Monday, July 29, 2002, 11:59:49 AM, you wrote:
lk> I have a question regard the table locking.
lk> Let say there is a table ,mytablename type=MyISAM in my database.
lk> And lets say there are 2 users who are currently log in.
lk> First user locks a table write:
lk&
if you need table or datarow locking , why don't you use innodb table type
with transactions ?
bye, thomas
> First user locks a table write:
> mysql> lock tables mytablename write;
> Query OK, 0 rows affected (0.00 sec)
>
> Then,second user wishes to query something from mytablename.
> mysql>sele
Hi,
I have a question regard the table locking.
Let say there is a table ,mytablename type=MyISAM in my database.
And lets say there are 2 users who are currently log in.
First user locks a table write:
mysql> lock tables mytablename write;
Query OK, 0 rows affected (0.00 sec)
Then,second u
On Wed, Dec 26, 2001 at 04:23:46PM -0600, Philip Molter wrote:
> Are there guides out there for configuring these things? What is
> a "big enough" log file? Honestly, on a lot of stuff, I'm just
> guessing, but it takes a lot of time to fiddle with values, clean
> out the database, and then shov
Weaver
Bozeman, Montana
-Original Message-
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 26, 2001 3:19 PM
To: [EMAIL PROTECTED]
Subject: Re: MyISAM/InnoDB/Oracle comparison, and a InnoDB table locking
question
Walt,
thank you for the test!
>This post
Philip,
>On Thu, Dec 27, 2001 at 12:18:38AM +0200, Heikki Tuuri wrote:
>: The MyISAM table obviously fit in the OS file cache, otherwise 1750 inserts
>: per second would not be possible. Did the table fit in the buffer pool of
>: InnoDB or the SGA of Oracle? Did you commit each insert individuall
On Thu, Dec 27, 2001 at 12:18:38AM +0200, Heikki Tuuri wrote:
: The MyISAM table obviously fit in the OS file cache, otherwise 1750 inserts
: per second would not be possible. Did the table fit in the buffer pool of
: InnoDB or the SGA of Oracle? Did you commit each insert individually in
: InnoDB
on testing MyISAM, InnoDB, and Oracle for the past two
>weeks or so. We're looking to replace MyISAM with InnoDB to eliminate table
>locking problems we're experiencing on databases with high levels of
>activity (dozens of queries a second). Currently we migrate these
>high-a
two
weeks or so. We're looking to replace MyISAM with InnoDB to eliminate table
locking problems we're experiencing on databases with high levels of
activity (dozens of queries a second). Currently we migrate these
high-activity databases to Oracle, where they perform well and very
relia
On Fri, Dec 21, 2001 at 12:45:16PM -0800, Shannon Kendrick wrote:
: Whats the drawback of using InnoDB instead of MySAM
: tables?
Tables aren't kept in separate files, disk space preallocated,
little less mature (but no less stable, I've found). For most
people, those aren't drawbacks, just diff
Whats the drawback of using InnoDB instead of MySAM
tables?
--- Carl Troein <[EMAIL PROTECTED]> wrote:
>
> Shannon Kendrick writes:
>
> > Does anyone know of a way to lock at the row level
> > instead of table level using MySQL 3.23.46
>
> Yep, use InnoDB instead of MyISAM tables.
>
> //C - p
Whats the drawback of using InnoDB instead of MySAM
tables?
--- Carl Troein <[EMAIL PROTECTED]> wrote:
>
> Shannon Kendrick writes:
>
> > Does anyone know of a way to lock at the row level
> > instead of table level using MySQL 3.23.46
>
> Yep, use InnoDB instead of MyISAM tables.
>
> //C - p
Shannon Kendrick writes:
> Does anyone know of a way to lock at the row level
> instead of table level using MySQL 3.23.46
Yep, use InnoDB instead of MyISAM tables.
//C - person of few words. But surprisingly seldom.
--
Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280
[EMAIL PROTECT
Does anyone know of a way to lock at the row level
instead of table level using MySQL 3.23.46
Thanks
Shannon
__
Do You Yahoo!?
Send your FREE holiday greetings online!
http://greetings.yahoo.com
--
Hi.
Please stop cross-posting and use the appropriate forum.
On Wed, Nov 21, 2001 at 03:27:23PM -0700, [EMAIL PROTECTED] wrote:
>
> (PHP-DB folks, ignore this top question - unless you want to answer)
>
> Which type of DB/Table provides table locking while a process is
>
(PHP-DB folks, ignore this top question - unless you want to answer)
Which type of DB/Table provides table locking while a process is
altering data? I don't care for locking while using SELECTs, but I do
need locking when something's being INSERTed or UPDATEd, so I won
Hello all,
We are doing a lot of data warehousing type work. The catch is we
need to get data back out in seconds rather than minutes. To accomplish this
we generate summary tables in real time. The problem we run into is that
generating the summary tables becomes very costly and our sele
Hi.
On Sat, Sep 22, 2001 at 03:38:45PM +0200, [EMAIL PROTECTED] wrote:
>
> I have some questions about MySQL replication and table locking:
> 1. If I lock a table on the master server, the table will be locked on
> the slave server?
It will be locked whenever the slave get
Hello
I have some questions about MySQL replication and table locking:
1. If I lock a table on the master server, the table will be locked on
the slave server?
2. If I use replication, the locking queues are the same on both servers?
3. If my master fails (or I turn off the server) and
Thanks Gerald,
I finally figured that out, from what I understand, when locking and the given
table is set to 'WRITE' this includes 'READ' too. so I only needed to use the
table once :)
Cheers,
>>On Fri, 03 Aug 2001 14:31:00 -0500, Gerald Clark <[EMAIL PROTECTED]>
> wrote:
>>
>>> Hi All,
>>
Hi All,
Was hoping some one maybe able to explain this error when attempting to LOCK
MySQL tables for READ and WRITE:
Error Encountered:
Unable to LOCK tables.
Error: Not unique table/alias: 'members'
The correct database has been connected to, and the members table is a valid
table. It's exe
ame field is text and number so you I can't autoincrement a
> username like "abc003434" ...
>
>
> - Original Message -
> From: "Mohammad Shoja" <[EMAIL PROTECTED]>
> To: "anna soobrattee" <[EMAIL PROTECTED]>
> Sent:
Hi,
The username field is text and number so you I can't autoincrement a
username like "abc003434" ...
- Original Message -
From: "Mohammad Shoja" <[EMAIL PROTECTED]>
To: "anna soobrattee" <[EMAIL PROTECTED]>
Sent: Wednesday, June 1
Hi All,
Can anyone help me...
My registration form looks up the last username in the members table (select
distinct username from members order by uid desc limit 1), and increments
this username (e.g. from abc2 abc3) which will be used as the
username for the next user to sign up...
the
http://www.mysql.com/doc/I/n/Internal_locking.html
Hope this helps.
-- Rodney
Zach Hollandsworth wrote:
> Is there an explanation somewhere about the rules mysql uses when lock
> tables?
>
> -
> Before posting, please check:
>
Is there an explanation somewhere about the rules mysql uses when lock
tables?
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request
My set-up:
MySQL server 3.23.30 with MSAccess97 via MyODBC 2.50.36 (all latest patches
to everything)
I have no trouble running this set up, BUT...
Even when I am the only person connected (after flush and restart of
server) and I open my Access database and go directly to edit one
particular t
My set-up:
MySQL server 3.23.30 with MSAccess97 via MyODBC 2.50.36 (all latest patches
to everything)
I have no trouble running this set up, BUT...
Even when I am the only person connected (after flush and restart of
server) and I open my Access database and go directly to edit one
particular t
My set-up:
MySQL server 3.23.30 with MSAccess97 via MyODBC 2.50.36 (all latest patches
to everything)
I have no trouble running this set up, BUT...
Even when I am the only person connected (after flush and restart of
server) and I open my Access database and go directly to edit one
particular t
On Tue, Jan 16, 2001 at 08:30:01AM -0500, Jeff Lewis wrote:
> I have always used flat file text files for my database purposes but
> this has always led to problems when the number of users gets to be
> too many.
Tell me about it. I converted a system like that to MySQL a while
back. It's s
I have always used flat file text files for my database purposes but this has always
led to problems when the number of users gets to be too many. I am now using mySQL
and want to know how many people can use the db at the same time without causing
problems.
As for table locking, should it
Steven Roussey writes:
> Hi!
>
> Do HEAP tables use table locking?
>
> Sincerely,
>
> Steven Roussey
> Network54.com
> http://network54.com/?pp=e
>
>
Hi!
Yes, they do.
Regards,
Sinisa
__ __
Hi!
Do HEAP tables use table locking?
Sincerely,
Steven Roussey
Network54.com
http://network54.com/?pp=e
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com
86 matches
Mail list logo
