|
|
Posted by Jerry Stuckle on 11/17/65 11:39
Jim Michaels wrote:
> "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
>>==================
>
>
>
Jim,
LOCK TABLES will lock the entire table. That's what it's supposed to
do. You don't have any direct control over row locking; the database
manager handles that for you.
The point I was making, however, is - don't hold locks for any length of
time (i.e. > 0.1 sec). It messes up concurrent access to the database.
And never, ever, hold a lock on the database where you're waiting for a
user response. What happens if that user just went to lunch?
LOCK TABLES is often used when you need to do something like back up one
or more tables while the database is active. You don't want data
changing in the middle of the backup. It's not something you generally
use in an application.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|