problem disappeared and the
selects behave as one would expect.
Many thanks to all who offered advice.
Carl
- Original Message -
From: "Perrin Harkins"
To: "Carl"
Cc:
Sent: Friday, March 13, 2009 1:40 PM
Subject: Re: Select query locks tables in Innodb
2009/3/
2009/3/12 Carl :
> I am still a little puzzled about how we could have a relatively large set
> of records (100,000+) and yet not cause any table to be locked as the server
> has only 8GB of memory.
What's the relationship you're implying between memory and locking?
Multi-version concurrency doesn
's suggestions also.
>
> Carl
>
>
>
>
>
> - Original Message - From: "Brent Baisley"
> To: "Carl"
> Sent: Thursday, March 05, 2009 1:12 PM
> Subject: Re: Select query locks tables in Innodb
>
>
> Ok, so you have 687 unique or
elp and Baron's suggestions also.
Carl
- Original Message -
From: "Brent Baisley"
To: "Carl"
Sent: Thursday, March 05, 2009 1:12 PM
Subject: Re: Select query locks tables in Innodb
Ok, so you have 687 unique organization serial numbers. That's not
ver
on Schwartz"
To: "Carl"
Cc:
Sent: Wednesday, March 04, 2009 8:11 PM
Subject: Re: Select query locks tables in Innodb
I don't think it locks the tables. The behavior may be similar, but I
seriously doubt that's what's happening. Take a snapshot of SHOW
INNODB STAT
gt; Since the report query is only reading data, I am puzzled why it locks the
> tables. Any ideas?
>
> TIA,
>
> Carl
>
>
> ----- Original Message - From: "Baron Schwartz"
> To: "Carl"
> Cc:
> Sent: Wednesday, March 04, 2009 2:29 PM
> Subject: Re:
--
From: "Baron Schwartz"
To: "Carl"
Cc:
Sent: Wednesday, March 04, 2009 2:29 PM
Subject: Re: Select query locks tables in Innodb
Carl,
Locked status in SHOW PROCESSLIST and a table being locked are
different. There is a bug in MySQL that shows Locked status for
queries
Carl,
Locked status in SHOW PROCESSLIST and a table being locked are
different. There is a bug in MySQL that shows Locked status for
queries accessing InnoDB tables in some cases. What version of MySQL
are you using?
The table is not really locked, you're just seeing that as a side
effect of wh
Carl,
Locked status in SHOW PROCESSLIST and a table being locked are
different. There is a bug in MySQL that shows Locked status for
queries accessing InnoDB tables in some cases. What version of MySQL
are you using?
The table is not really locked, you're just seeing that as a side
effect of wh
the isolation level but I believe it is whatever was set out of
the box (five years ago.)
Thanks,
Carl
- Original Message -
From: "Perrin Harkins"
To: "Carl"
Cc:
Sent: Wednesday, March 04, 2009 1:49 PM
Subject: Re: Select query locks tables in Innodb
2009
report query. It is a foreign key on one of the files that is used.
TIA,
Carl
- Original Message -
From: "Baron Schwartz"
To: "Brent Baisley"
Cc: "Carl" ;
Sent: Tuesday, March 03, 2009 5:50 PM
Subject: Re: Select query locks tables in Innodb
On Tue,
2009/3/4 Carl :
> However, when I had all the pieces in the query
> (copy attached), I could easily see it was locking tables using the Server
> Monitor in Navicat.
I don't know what that is, but I think you'd better look at something
closer to the bone, like SHOW INNODB STATUS.
> Explain (copy a
Message -
From: "Baron Schwartz"
To: "Brent Baisley"
Cc: "Carl" ;
Sent: Tuesday, March 03, 2009 5:50 PM
Subject: Re: Select query locks tables in Innodb
On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley
wrote:
A SELECT will/can lock a table. It almost al
om: "Baron Schwartz"
To: "Brent Baisley"
Cc: "Carl" ;
Sent: Tuesday, March 03, 2009 5:50 PM
Subject: Re: Select query locks tables in Innodb
On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley
wrote:
A SELECT will/can lock a table. It almost always does in MyISAM (no
inse
On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley wrote:
> A SELECT will/can lock a table. It almost always does in MyISAM (no
> insert/updates), almost never does in InnoDB. There is an exception to
> every rule. The problem is most likely in the 107488 rows part of the
> query. That's too many rows
On Tue, Mar 3, 2009 at 10:53 AM, Carl wrote:
> A query that is selecting data for a report locks the files that it accesses
> forcing users who are attempting to enter transactions to wait until the
> select query is finished.
Is it an INSERT INTO...SELECT FROM? Those lock. Also, have you
ver
A SELECT will/can lock a table. It almost always does in MyISAM (no
insert/updates), almost never does in InnoDB. There is an exception to
every rule. The problem is most likely in the 107488 rows part of the
query. That's too many rows for InnoDB to keep a version history on so
it's likely just lo
I have been wrestling with this problem for a couple of weeks and have been
unable to find a solution.
The MySQL version is 5.0.37 and it is running on a Slackware Linux 11 box.
The problem:
A query that is selecting data for a report locks the files that it accesses
forcing users who are att
18 matches
Mail list logo
