Re: Is this a good idea?
- From: David Haynes <david.haynes2@xxxxxxxxxxxx>
- Date: Fri, 10 Feb 2006 09:12:42 -0500
Jim Michaels wrote:
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...
If you need row level locking, you need to use a database that supports row level locking. MySQL supports this with the InnoDB tables and (from the manual) it appears that the table locks are mutated into row-level locks in InnoDB tables.
see http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html
Other databases (postgres, Oracle, etc.) have more explicit row level locking support (e.g. select for update in Oracle)
-david-
.
- References:
- Re: Is this a good idea?
- From: Jim Michaels
- Re: Is this a good idea?
- Prev by Date: Re: Is this a good idea?
- Next by Date: Re: online editor / highlighter
- Previous by thread: Re: Is this a good idea?
- Next by thread: How to set variables 'lang' and 'pma_lang' to iso-8859-1 ?
- Index(es):
Relevant Pages
|
