|
|
Posted by Jerry Stuckle on 01/26/06 00:24
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.
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).
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|