|
|
Posted by David Haynes on 10/15/62 11:39
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-
Navigation:
[Reply to this message]
|