LOCK TABLES using Perl in MySQL

[sdyates]

Well, I made this great little application that writes to and updates a table in MySQL, but now I have more than one user making updates, and while I feel that the chance of a corruption is slim, I want to be able to lock the table for all other users(threads), write the information to the DB, then unlock the tables.

I am using DBD::mysql. Below is an sample of the code I used to write to the DB:

my $sth = $dbh->prepare("$SQLCommand") or err_trap("Cannot prepare the
+ load_category_names query");
$sth->execute or err_trap("Cannot execute the SQL query to SQL COMMAND
+");

Simple stuff! Yet if I copy these lines above and use LOCK TABLES table read, I cannot write as I am locking all data.

I understand I have a two part issue here:
1) proper perl syntax to write to the database three times
a. lock tables
b. execute my sql command
c. unlock talbes

2) Determining the proper syntax of the lock and unlock to get me where I need to be.

For Perl, do I just copy the two lines of code, replace $SQLCommand with appropriate lock statement. Use the same two lines of code to write my SQLCommand. Then, use the same two lines of code replacing $SQLCommand again with UNLOCK TABLES?

Also if any one has some sample SQL lines on locking procedures *I know this is not a MySQ site, but what the heck, it is related. Thanks folks.

Edited: Sun Oct 14 07:11:57 2001, footpad - Added/Corrected <P> tags and removed repeated paragraphs.

[greywolf]

Here is something that should work.

$sth = $dbh->prepare("LOCK TABLES tablename WRITE");
unless ($sth->execute()) { die $dbh->errstr() }

#sql queries here

$sth = $dbh->prepare("UNLOCK TABLES");
unless ($sth->execute()) { die $dbh->errstr() }


mr greywolf

[merlyn]

Well, I made this great little application that writes to and updates a table in MySQL, but now I have more than one user making updates, and while I feel that the chance of a corruption is slim, I want to be able to lock the table for all other users(threads), write the information to the DB, then unlock the tables.

I am using DBD::mysql.

If you're just starting the design from scratch, it's probably easier to switch to PostgreSQL and get real transactions, and a lot more flexibility besides. Faster, Better, Cheaper. Pick all three.

For your application, there'd be no locking out other users while you're updating your values. Just a simple $dbh->begin_work, do your job, and then $dbh->commit. You could scribble all over many tables, and yet the other readers (not blocked) see the database just as if you didn't exist yet.

Real transactions are cool. Upgrade to PostgreSQL. MySQL is now pale in comparison.

-- Randal L. Schwartz, Perl hacker

[mischief]

How are PostgreSQL transactions more real than MySQL using the BerkeleyDB or InnoDB? I have to admit I'm not familiar with the differences, but I was under the impression that they were the same.

Also, I think some people might not agree that PostgreSQL is faster than MySQL. And how is it cheaper? They're both free!

I do agree that Postgres has more features though (missing subselects is almost unforgivable), but MySQL is catching up - apparently version 4.1 (which is scheduled to be out in a couple of months) will have subselects along with a load more features (secure connections! at last!).

[merlyn]

How are PostgreSQL transactions more real than MySQL using the BerkeleyDB or InnoDB? I have to admit I'm not familiar with the differences, but I was under the impression that they were the same.

After perusing the InnoDB/MySQL docs, I can see that MySQL is finally catching up. So I'll grant you that one, although it requires "strapping on" a completely separate solution from an independent vendor underneath, and that scares me.

Also, I think some people might not agree that PostgreSQL is faster than MySQL.

Did you notice the date on that benchmark? It's using Pg 7.0.2. I wouldn't have used anything prior to 7.1 either. Some more recent benchmarks put them in the same ballpark, with various tests showing one or the other ahead.

And how is it cheaper? They're both free!

Purchase price = $0. Installation time costs "money" though. I've always had to wrestle to install a MySQL distro from source. Pg installed trivially the first time. I was really amazed. And development time: with true views and subselects, you can do some amazing things, including putting business rules directly in the database, rather than having to code them in every application (and forgetting one, oops). Subselects, views, triggers, stored procedures. You don't miss them until you miss them, and then you wonder how you ever got along without them.

So let me say it this way. MySQL is fine for people who are moving up from DBM. But once you've played with Oracle, Pg is the way to go. You sacrifice far too much of "typical" database coding with MySQL. Maybe someday MySQL will have everything in SQL 92. But Pg has that today.

-- Randal L. Schwartz, Perl hacker

[George_Sherston]

I grappled with a similar problem, helped by monks. The first thread of interesting advice is here. At the end of that my conclusion was to use the following sledgehammer:

$dbh->do("LOCK TABLES tbl WRITE");
$dbh->do("$various_sql_stuff_without_getting_tangled");
$dbh->do("UNLOCK TABLES");

to crack a nut. Which I found works.

Then I mentioned this in another thread and htoug said some very interesting things, which to my shame I have not yet followed up; but when I revisit the MySQL aspects of my current project I shall try to slim them down, using his advice.

When I do that, I shall of course return to this thread too, where I confidently expect to find the optimal synthesis of former wisdom.

§ George Sherston

[sdyates]

Thanks for the info. I am only using one sql command other than the LOCK and UNLOCK. Do you have the exact article in question, I was amazed at how much htoug has actually written and found myself slipping off into other areas :) Thanks for the info though.

[George_Sherston]

I'd just like to agree (overtly, rather than silently as all the other times) with Merlyn - I wish I'd started out with PostGreSQL, but find I'm now in MySQLso deep it were as tedious to go back as to go o'er. If you have the option, I'd say take it.

§ George Sherston