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.

    In reply to Re: OT: help with MySQL questions by Roger
    in thread OT: help with MySQL questions by kiat

    Title:
    Use:  <p> text here (a paragraph) </p>
    and:  <code> code here </code>
    to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.