|
|
Posted by Jim Michaels on 10/16/51 11:39
"Jerry Stuckle" <jstucklex@attglobal.net> wrote in message
news:ffKdnW8QTLUSZ0reRVn-qw@comcast.com...
> Jim Michaels wrote:
>> I found this in the MySQL manual under row-level locking:
>> If you want to perform many INSERT and SELECT operations on a table when
>> concurrent inserts are not possible, you can insert rows in a temporary
>> table and update the real table with the records from the temporary table
>> once in a while. This can be done with the following code:
>>
>> mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
>> mysql> INSERT INTO real_table SELECT * FROM insert_table;
>> mysql> TRUNCATE TABLE insert_table;
>> mysql> UNLOCK TABLES;
>>
>
> Jim,
>
> Yes, I've seen this and disagree to some extent with this statement.
>
> First of all, it just moves the problem from the real table to the insert
> table. Holding a lock on the insert table will still lock out other
> people.
I guess if you have the need to then lock them out, but if you really don't
have to avoid it then. Odds are not.
>
> Secondly, you still need to be able to lock the real table. If you can't
> do inserts on the fly, when will you be able to lock the entire table?
> Are you going to stop all usage of the database every 5 minutes (or
> whatever) so you can do the inserts?
>
> And if you need current information, you need to now search two tables, in
> case something has been added to the temporary table.
>
> But inserts have never been a major cause of the problem - usually any
> locks which affect an insert are short-lived (if you have appropriate
> indicies, etc.).
>
> The REAL problem comes from SELECT/UPDATE statement combination,
> especially if you're waiting for user input between the SELECT and the
> UPDATE (and are still holding the locks).
great. I see an example of how to lock an INSERT table, but not how to lock
rows I wanted to SELECT/UPDATE (is that possible?). How do I? (why lock the
whole table out if I don't have to?)
I mean, if I do a LOCK TABLES, how do I know MySQL is going to do a row lock
and not a table lock? It looks like from the manual, that row-locking is a
hidden thing and not a command.
from what I've seen, I have to lock tables like this:
mysql> LOCK TABLES real_table WRITE;
mysql> SELECT * FROM real_table WHERE id=5;
do stuff?
mysql> UPDATE real_table SET columnname=value WHERE id=5;
mysql> UNLOCK TABLES;
and from the manual, "When you use LOCK TABLES, you must lock all tables
that you are going to use in your queries." not nice...
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstucklex@attglobal.net
> ==================
Navigation:
[Reply to this message]
|