zaterdag 7 juni 2014

Why I like PostgreSQL more than MySQL: GET LOCK works.

MySQL uses GET_LOCK('lock_name'); to create an advisory lock. However, if you successfully get a lock, lets say 'A', and you then try to get lock 'B' during the same session, you will silently release lock 'A'.

Not only does this mean that you can never use more than one lock at a time, which is just silly, it also means that you cannot trust any lock you create to actually stay in place until you release it. Why? Because you have no way of doing that, that's why you are using the database in the first place.

But perhaps most worryingly, MySQL does not even issue a warning about releasing the first lock. You have no way of ever detecting that any lock is released implicitly. You don't notice this problem untill your data is corrupted to the point where users start to complain, and then you'll have a lovely time debugging this.

In PostgreSQL you can use pg_advisory_lock() as often as you like to make any number of locks without ever accidently releasing any of them.