Is this a good idea?

[sam.s.kong]

Hi!

I've been programming ASP for 5 years and am now learning PHP.
In ASP, you can use GetRows function which returns 2 by 2 array of
Recordset.
Actually, it's a recommended way in ASP when you access DB as it
disconnects the DB earlier.
Also, it's handy as you can directly access any data in the array
without looping.

As far as I know, there's no such function in PHP and I can make one.
My question is whether it's good in PHP.

pseudo-code:

$data = get_data("select * from table1");
$var = $data[3][2]; //value at 4th row, 3rd column

This way, I can wrap db connection, data retrieval, and error handling
with one function (or maybe a class).
Is the idea workable?

TIA.
Sam

[Jim Michaels]

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;
"Jerry Stuckle" <js*******@attglobal.net> wrote in message
news:Yo********************@comcast.com...

Geoff Berrow wrote:

Message-ID: <cu********************@comcast.com> from Jerry Stuckle
contained the following:

So the system recomputes the balance. If there are sufficient funds in
the account, it processes the withdrawal.

Isn't a lock required for the very short period of time in between
recomputing the balance and processing the withdrawal?

Yes, but that's a very short time - on a mainframe you're talking
microseconds; milliseconds at the worst.

You can never eliminate all locks. The secret is to minimize the number
and length of the locks.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Jerry Stuckle]

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.
js*******@attglobal.net
==================

[Jim Michaels]

"Jerry Stuckle" <js*******@attglobal.net> wrote in message
news:ff********************@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.
js*******@attglobal.net
==================

[Jerry Stuckle]

Jim Michaels wrote:

"Jerry Stuckle" <js*******@attglobal.net> wrote in message
news:ff********************@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.
js*******@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.
js*******@attglobal.net
==================

[David Haynes]

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/...l-locking.html

Other databases (postgres, Oracle, etc.) have more explicit row level
locking support (e.g. select for update in Oracle)

-david-