The
Ultimate
Business Machine

Technology, business
and innovation.

And, not least, about
the Mac.

Weblog Archive Cutedge

by: Bernard Teo








Creative Commons License

Copyright © 2003-2012
Bernard Teo
Some Rights Reserved.

Thu 04 Aug 2011

Transactions and Locks in SQL

Category : Technology/TransactionProcessing.txt

I'm done comparing the SQL calls I need to do across MySQL and PostgreSQL in order to support transaction processing (i.e., Locks, Begin, Commit or Rollback) in a multi-user version of Luca that I'm working on.

I'm also going to do versions of Luca and Liya for the iPad. I've got a new iPad 2 that I'm looking forward to working on.

Fortunately, the SQL calls for transaction processing are consistent across MySQL and PostgreSQL. They're the same - BEGIN, COMMIT or ROLLBACK - for each platform.

There is a more Oracle-like START TRANSACTION in place of BEGIN in MySQL but BEGIN will do. Why add complexity? Always simplify.

While PostgreSQL has always supported transactions for as long as I can remember, MySQL with MyISAM tables do not. We'll need Innodb, which is the default setting for newly created tables in MySQL 5.5.

Once we can get our Lion installer for MySQL 5.5 done, Hai Hwee has a functionality built into her installer such that it'll migrate forward all the data in your current databases to the new version of MySQL that you are installing. I'm hoping that when that kicks in, all the data in the MySQL tables will then automatically support transaction processing, and then we'll be able to do BEGIN, COMMIT or ROLLBACK from applications like Luca, knowing that we'll be accessing databases that support it.

We also need advisory locks - putting a lock on database updates among consenting apps (I almost said adults). This is a simple form of record locking, to prevent the database from being written to the same place at the same time by different people, using Luca on different client machines. I don't think Luca needs the more sophisticated form of record-locking at the moment, so advisory locks will do for now.

On MySQL it's very easy. SELECT GET_LOCK('Lock1', 10); To test for the lock, it is SELECT IS_FREE_LOCK('lock1'); To release the lock, just do SELECT RELEASE_LOCK('lock1'); I just tested it, accessing MySQL from two client instances. It works great.

On Postgres, the equivalents are SELECT pg_advisory_lock(5), SELECT pg_try_advisory_lock(5), and SELECT pg_advisory_unlock(5).

So two differences - one is the name. The other is that MySQL uses a string for the semaphore while Postgres uses a big integer.

I can hide these differences in my database frameworks so that an app like Luca calls the same code, no matter which type of database it is accessing. So we're good to go.

When I had the idea for Liya and my database access frameworks, I never expected to be able to go so far creating a unifying API that will work for both MySQL and PostgreSQL - mainly because there are so many differences between the two dialects. It's been a long slog. But I'll happily take what I can get.

Posted at 4:01AM UTC | permalink

Mac@Work
Put your Mac to Work

Sivasothi.com? Now how would you do something like that?

Weblogs. Download and start a weblog of your own.

A Mac Business Toolbox
A survey of the possibilities

A Business Scenario
How we could use Macs in businesses

VPN Enabler for Mavericks

MailServe for Mavericks

DNS Enabler for Mavericks

DNS Agent for Mavericks

WebMon for Mavericks

Luca for Mavericks

Liya for Mountain Lion & Mavericks

Postfix Enabler for Tiger and Panther

Sendmail Enabler for Jaguar

Services running on this server, a Mac Mini running Mac OS X 10.9.2 Mavericks:

  • Apache 2 Web Server
  • Postfix Mail Server
  • Dovecot IMAP Server
  • Fetchmail
  • SpamBayes Spam Filter
  • Procmail
  • BIND DNS Server
  • DNS Agent
  • WebDAV Server
  • VPN Server
  • PHP-based weblog
  • MySQL database
  • PostgreSQL database

all set up using MailServe, WebMon, DNS Enabler, DNS Agent, VPN Enabler, Liya and our SQL installers, all on Mavericks.