SQL Azure Application locks

I'm working with a complex database and seeing how easy it is to port the application to SQL Azure. My initial impressions of the platform are positive. But complex and "interesting" SQL Server applications that leverage advanced features require some close scruity and care when operating in the Cloud world.

Microsoft makes it very clear that connections to the Azure database are not 100% continous. And this can have a subtle effect on transaction and locking features of traditional SQL 2008 applications.

Take for example the use of the SQL Server application lock facility. Look at this sequence:

----begin----
exec sp_getapplock @Resource='ALockTest',@LockMode='Exclusive',@LockOwner ='session'

SELECT APPLOCK_MODE('public', 'ALockTest', 'session');

exec sp_releaseapplock 'ALockTest', 'session'
----end----

If done "quickly" this will work as expected. BUT ... SQL Azure doesn't guarantee that the session is continous.

Try the above sequence again and put a long delay(I used 5 minutes) between the 1st step( sp_getapplock ) and the last two steps.

You'll probably get the following message from the sp_releaseapplock procedure.

Msg 1223, Level 16, State 1, Procedure xp_userlock, Line 1
Cannot release the application lock (Database Principal: 'public', Resource: 'ALockTest') because it is not currently held.


As you can guess this is not what is expected.
There are other ways of implementing application locks. More on that later.

So if you are moving to SQL Azure you must take issues like this into consideration for the Cloud environment that is Azure.

Dave W