| The Ultimate Business Machine Technology, business And, not least, about by: Bernard Teo
|
The Ultimate Business Machine - ArchivesList of Categories : Database * Technology * Commentary * Singapore * Travel * Thu 04 Aug 2011 Transactions and Locks in SQLCategory : 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. |
Mac@Work
Services running on this server, a Mac Mini running Mac OS X 10.9.2 Mavericks:
all set up using MailServe, WebMon, DNS Enabler, DNS Agent, VPN Enabler, Liya and our SQL installers, all on Mavericks. |
||||||||||||||||||||||||||
