DB Connections vs. Users

[rhinoboy]

I hope this isn't too basic of a question. I am using Fabrik to put an application together for a client. I have a difficult problem (I'll spare you the details) and one solution seems to be using GET_LOCK() in MySQL. My hosting provider uses MySQL v5.5, in which "only a single simultaneous lock can be acquired". And that is either great or it's a showstopper, depending on my question:

My question is this: If two users are accessing my Joomla site concurrently and they both cause the server to execute code that uses GET_LOCK(), will they each be able to get and hold a lock? (If you don't want to research GET_LOCK(), keep reading!)

Or, to state the question more simply, does the Joomla installation have a single, persistent connection to the database or is a connection established each time a Joomla user executes a FabrikWorker::getDbo() call?

 

[cheesegrits]

It's a single persistent connection.

Well, define "single". We create a connection on the first call to FabrikWorker::getDbo() for a specific connection (remembering that Fabrik supports multiple databases), and keep that persistent across all calls of FabrikWorker::getDbo() for that connection. So with no args, we create a connection to the default site database, and use that. But that is separate from whatever connections J! itself or other extensions / plugins may have open at any time.

-- hugh

 

[cheesegrits]

Also note that we extend the J! database drivers. Doesn't affect how we handle connections, but just something to be aware of. So we never share the same connection as J! (except for some backend house keeping stuff), as the connection signature is always different to J!'s (driver name is different).

-- hugh

 

[rhinoboy]

Thanks very much for getting back to me (twice!).

I'm realizing that this is not so much a Fabrik question as it is a Joomla (or even just a MySQL or web server) question. But I came across this while trying to build a Fabrik app, so I thought someone here might be more able to answer my question than on a generic forum.

Let me try it this way: Suppose I am logged into my Joomla website and I use FabrikWorker::getDbo() to access a database table and at the same time, you log into my site and do the same thing. Does MySQL see us as two different sessions/connections?

As simple as it sounds, that is all I really need to know. ;-)


Terry

 

[cheesegrits]

Yes, absolutely, two separate connections.

So yes, in theory you could use named locks to create a mutex lock between different sessions.

However - note that any named locks will get released as soon as a page load finishes, because PHP always closes open connections, which automatically releases locks.

So, for instance, if you just wanted to guard against the small window where two people submit the same form at exactly the same time, that would work - you could use a pair of Fabrik form submission plugins to check / wait / set the lock "onBeforeProcess", and release it "onAfterProcess".

However, what you can't do is, say, set a lock when someone loads a page with a form on it, and release it when they submit the form. That's two separate server sessions, and the lock won't persists across them.

And really, it's the second case which is the one that can cause problems:

User 1 loads form.
User 2 loads form.
User 1 changes element X and submits form.
User 2 changes same element Y and submits form.

Form loses change to X made by user 1, because it gets overwritten with the original value loaded by user2.

That has to be handled by rolling your own locking system, involving creating a table to store the lock in:

user 1 loads form.
form plugin 'onLoad' checks and sets lock in my_locks table.
user 2 loads form
form plugin checks my_locks, lock exists, aborts form load with informative message.
User 1 submits form, form plugin 'onAfterProcess' releases lock.

And of course ...

Cron job runs every X minutes to clear any timed out locks 9where user loaded form but never submitted it).

Or you can work around needing a garbage collection cron job by handling timeouts in the lock/unlock code. When user2 loads and checks the lock, if it is older than your timeout, remove that lock and create user2 lock. Then when submitting, check that this user's lock still exists and hasn't been removed.

-- hugh

 

[cheesegrits]

I have thought about writing a lock plugin for forms. Maybe I'll get a wild hair and do it some time.

-- hugh

 

[rhinoboy]

Perfect.

So I actually encapsulated the critical code in a stored function on the MySQL server. The function is called as part of a Fabrik element validation php plugin. The form is just user entry--nothing is stored to the database.

Specifically the problem to be solved was that I have a table of "access codes" that get assigned to users upon request. The challenge is that 40 or more people could request a code at roughly the same time. If I used record locking, the first person would lock the first unassigned record. The second person could be waiting on the first person's lock to clear, but when the lock was released, the record wouldn't be unassigned any more ...

So by using locks named using that table's primary key, a user can select an unassigned, not-locked record, lock it, update it and then release it. No other user would be waiting on that lock, so they are free to find the next unassigned, not-locked record and so on.

I haven't tested it beyond syntax yet, but I think it's a sound solution--especially now that you gave me exactly what I need.

Thanks again,


Terry

 

[cheesegrits]

Cool. Let me know how it goes.

-- hugh