Re: OT: help with MySQL questions

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.

Comment on Re: OT: help with MySQL questions Select or Download Code

Replies are listed 'Best First'.
Re: OT: help with MySQL questions by Abigail-II (Bishop) on Nov 25, 2003 at 09:06 UTC
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]
Re: Re: OT: help with MySQL questions by kiat (Vicar) on Nov 25, 2003 at 03:14 UTC
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]
Re: Re: Re: OT: help with MySQL questions by Roger (Parson) on Nov 25, 2003 at 03:24 UTC
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]