Eventmachine talk - Aug 2006 - using select for update in postgresql

I''m using postgresql and using the following method for creating a
lock in my application to prevent two requests of the same type from
proceeding.  There is also a method to release the lock, but for now
I''m just starting with an entry already created in the locks table, so
it will always return with locked = 1.  What''s happening is that when
I send 10 or so simultaneous requests, invariably the application
locks up after a few requests.  Postgresql shows 2 transactions, one
in SELECT FOR UPDATE and the other idle.  What I think is happening is
that while one request already has an exclusive lock but has not yet
issued the commit, another request issues another SELECT FOR UPDATE,
which blocks ruby and prevents the previous request from issuing the
commit and releasing the lock.

What I''m thinking might be a solution is to also use a global variable
as a lock in front of the database locking.  That way the above
scenario would never happen as the application would return a busy
response to the client if any simultaneous requests happen within that
instance of the application, and it would also take care of locking
across multiple instances.  It would still block if another instance
held a database lock, but it wouldn''t deadlock like it does now
because all instances would be guaranteed to release the database
locks at some point, probably within a second or so.




def getlock
      locked = 0
      begin
        conn = @dbh.get_connection
        conn.do("BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE")
        sth = conn.prepare("SELECT merchant_id from locks where
merchant_id = ? FOR UPDATE")
        sth.execute(@mconf.merchant_id)
        if row = sth.fetch
          locked = 1
        else
          sth2 = conn.prepare("INSERT INTO locks (merchant_id) VALUES
(?)")
          sth2.execute(@mconf.merchant_id)
        end
        conn.do("COMMIT TRANSACTION")
        @dbh.release conn
        @logger.debug("Locked=#{locked}")
      rescue Exception => e
        conn.do("ROLLBACK TRANSACTION")
        @dbh.release conn
      end
      return locked
    end

On 8/27/06, snacktime <snacktime at gmail.com>
wrote:
>
> I''m using postgresql and using the following method for creating a
> lock in my application to prevent two requests of the same type from
> proceeding.  There is also a method to release the lock, but for now
> I''m just starting with an entry already created in the locks
table, so
> it will always return with locked = 1.  What''s happening is that
when
> I send 10 or so simultaneous requests, invariably the application
> locks up after a few requests.  Postgresql shows 2 transactions, one
> in SELECT FOR UPDATE and the other idle.  What I think is happening is
> that while one request already has an exclusive lock but has not yet
> issued the commit, another request issues another SELECT FOR UPDATE,
> which blocks ruby and prevents the previous request from issuing the
> commit and releasing the lock.


Have you considered using an advisory file-lock?
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://rubyforge.org/pipermail/eventmachine-talk/attachments/20060827/ae32141a/attachment.html

> Have you considered using an advisory file-lock?
Different instances would be running on different servers.  I got it
working using a global variable to create a lock before calling
getlock.

On 8/27/06, snacktime <snacktime at gmail.com>
wrote:
>
> > Have you considered using an advisory file-lock?
>
> Different instances would be running on different servers.  I got it
> working using a global variable to create a lock before calling
> getlock.
> _______________________________________________


Ok, good that you have a solution. As a conceptual exercise, would it have
helped you if you had a way to send the database requests asynchronously
through a queueing system, that would apply them all in sequence?
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://rubyforge.org/pipermail/eventmachine-talk/attachments/20060827/aee865ec/attachment.html

>
> Ok, good that you have a solution. As a conceptual exercise, would it have
> helped you if you had a way to send the database requests asynchronously
> through a queueing system, that would apply them all in sequence?
Probably not in this case.  Just seems like too much added complexity
for what is basically a workaround.  If I didn''t have a solution,
I''d
probably sooner pay someone to write a non blocking postgresql
adapter.