kiat has asked for the wisdom of the Perl Monks concerning the following question:

Hi monks,

I've got a couple of MySQL questions which I hope you can help - pardon me for asking them here.

Say I've got a single database and I set it up with, say, 6 tables - one for storing members' profiles, one for storing members' test scores, one for storing forum data, one for storing the questions, and one for keeping track of online statistics (such as number of members and guests online).

Each time a member logs in, the database will be updated with information on date of log in, etc and each time a member does something (e.g. do a test), the database will be updated with the test result. So there's going to be quite a bit of load on the database. My question is: Would there be problems if, say, 10 members do a test and updates of their test scores are needed concurrently? How does MySQL deal with such problems? Is there any special syntax that needs to be used?

20031125 Edit by BazB: Changed title from 'help with mysql questions'

Replies are listed 'Best First'.
Re: OT: help with MySQL questions
by Roger (Parson) on Nov 25, 2003 at 02:59 UTC
    The database is designed to, and can handle multiple connections at the same time. The database has built-in locking mechanism. I guess you wanted to know how to do atomic updates accross multiple tables instead. With MySQL (and other databases), you could do the transaction approach -
  • SET AUTOCOMMIT = 0
  • update your multiple tables
  • issue a "COMMIT" at the end.

    A transaction is always atomic.

    my $dbh = DBI->connect($dsn, $user, $password,
             { RaiseError => 1, AutoCommit => 0 });

# do SQL's that forms the transaction
my $sth = $dbh->prepare(...)
$sth->execute;
$dbh->do(...);

# commit the transaction
$dbh->do( "COMMIT" );

    [download]
    Now as for the locking part, MySQL can have table level locking and row level locking. If you want to do table level locking exclusively at SQL level -
    LOCK TABLES table1 WRITE, table2 WRITE

INSERT INTO table1 SELECT * FROM table2;

UNLOCK TABLES

    [download]
    But I can't see that being useful to your application. You will best handle this with the default locking mechanism, and design your application carefully to avoid race conditions. You could perhaps draw a time-line diagram, outline what time does the record become available, and what time it is needed by other apps, and so on. Regardless which design you end up with, (atomic) transactions are almost certainly needed.

[reply]
[d/l]
[select]
      Setting auto commit to one means that every modification is a separate transaction. Which means that if you have a relation between two tables, and you need to make an update/insert/delete in both tables, and you have auto commit on, you will be living dangerously, as your relations aren't modified atomically.

      Newer versions of MySQL support transactions, but only if you use a specific kind of table. For a long time, the MySQL documentation said you had to code transaction support in your user code yourself.

      Note that locking tables itself doesn't give you full transaction support - it just prevents someone else from modifying the table as well. You won't get rollbacks, and if you have to modify more than one table, you might get deadlocked, as someone else may lock the tables in the reverse order. Not to mention the potential performance hit if for every insert you need to lock the entire table.

      Abigail

[reply]
      Thanks, Roger!

      The database is designed to, and can handle multiple connections at the same time.
      Does that mean I can just use the normal queries? What does 'AutoCommit => 0' mean? Is it needed? In your example, why do you have two '$dbh->do{...};' statements?
[reply]
          Does that mean I can just use the normal queries

        Of course you can use normal queries.

          What does 'AutoCommit => 0' mean

        If you specify AutoCommit => 0 in your database connection statement, it will tell the database that you want to do transactions. Why it's needed? Because I saw you mentioned that you had multiple tables that you wanted to update. Transactions are best used to preserve the integrity of your data, because the results will only be visible to others after you 'COMMIT' your transaction, in other words, other sessions will not see incomplete/half-updated data.

          why do you have two '$dbh->do{...};' statements

        The first one is just an example. The second one is required, because it does the 'COMMIT' to end the transaction.

        Update: I guess it would be useful to list this link here, an Overview on MySQL Transaction.

[reply]
[d/l]
Re: OT: help with MySQL questions
by RandomWalk (Beadle) on Nov 25, 2003 at 05:36 UTC
    Dear Kiat,

    I have found Paul DuBois' book "MySQL" an excellent book with examples along the line you ask as well as a complete Perl DBI reference. I thought I got a pretty good deal through Amazon.com merchants.

[reply]

Back to Seekers of Perl Wisdom