Am 15.08.2011 21:08, schrieb Teddy Zeenny:
> Oh you are definitely right. I made up the example as I was writing
> the post, this is not a real case in my application.. Besides, I use
> ACL for privileges :)
>
> But such cases do happen (inserting or updating based on a SELECT
> result) and it can't always be solved with 1 save as it might depend
> on results from multiple queries from multiple tables (Hence the need
> for locking).
>
> GET_LOCK almost always solves it. (I had never used it before. I
> researched it when you mentioned it). Correct me if I'm wrong, but I
> noticed that the drawback of GET_LOCK is that you need to know which
> scripts might have common results in order to make them require the
> same lock.
>
> example:
>
> script 1: SELECT * FROM employees WHERE position='manager';
> if (condition)
> UPDATE employees SET ...
>
> script 2: SELECT * FROM employees WHERE salary>= 2000;
> if(condition)
> UPDATE employees SET ...
>
>
> Here one would probably make them both require the same lock ( like
> GET_LOCK('employee_update')) to ensure they don't change each others'
> results if run at the same time, and therefore make them wait for each
> other every time they run concurrently. SELECT FOR UPDATE on the other
> hand would only make them wait for each other if, in that particular
> instance, they had common records.
Yup, thats the difference between adivsory and explicit locking.
Personally, I used GET_LOCK when doing cache updates to prevent
stampeding (e.g. multiple clients start refreshing an expensive cache
entry, your server ends up doing the same multiple times, then after
waiting a while the users start to press F5 :-) ).
As I mentioned before, GET_LOCK returns immediately so all following
processes could be served the old content while only 1 process builds up
the new one.
In this case the choice on the lock key is easy as it is the equivalent
of the cache key itself. In other cases it might be possible to generate
a key for the lock
that would do the locking the way you want.
If you are working on a site thats used by a lot of users then SELECT
FOR UPDATE is probably a showstopper.
Anyway, to get back to your original question:
Although FOR UPDATE is in the SQL standard, it might not be supported by
all datasources. I think its a good case
for the usage of a custom query() (this method might only exist for a
case like this).
--
Our newest site for the community: CakePHP Video Tutorials
http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others
with their CakePHP related questions.
To unsubscribe from this group, send email to
[email protected] For more options, visit this group at
http://groups.google.com/group/cake-php
