Re: Using SELECT... FOR UPDATE in cakephp
I totally agree. Thanks a lot for your help :)
On Mon, Aug 15, 2011 at 11:38 PM, 0x20h wrote:
> 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
> cake-php+unsubscr...@googlegroups.com For more options, visit this group
> at http://groups.google.com/group/cake-php
>
--
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
cake-php+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php
Re: Using SELECT... FOR UPDATE in cakephp
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
cake-php+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php
Re: Using SELECT... FOR UPDATE in cakephp
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.
On Mon, Aug 15, 2011 at 9:29 PM, 0x20h wrote:
> Am 14.08.2011 12:46, schrieb Teddy Zeenny:
> > That is an interesting idea. (Although I don't think it would give me
> > the exact SELECT .. FOR UPDATE functionality, but I guess it's as
> > close as I can get).
> It would only allow 1 Process to access the if clause and it would
> return *immediately* on *all* clients while FOR UPDATE blocks all other
> processes that try to
> SELECT the particular id (could be many, could be long). Of course,
> while SELECT...FOR UPDATE explicitly locks the row, the GET_LOCK
> functionality is an advisory technique where all participating sides
> have to obtain the lock (e.g. the code part that takes away the is_admin).
>
> Anyway, I think your concerns are a bit overkill and probably a flaw in
> your application design (e.g. why would you have an is_admin flag and
> then later set privileges => 'all' in another query)
> Would it not solve the problem if you had a save() that does both at once?
>
> --
> 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
> cake-php+unsubscr...@googlegroups.com For more options, visit this group
> at http://groups.google.com/group/cake-php
>
--
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
cake-php+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php
Re: Using SELECT... FOR UPDATE in cakephp
Am 14.08.2011 12:46, schrieb Teddy Zeenny: > That is an interesting idea. (Although I don't think it would give me > the exact SELECT .. FOR UPDATE functionality, but I guess it's as > close as I can get). It would only allow 1 Process to access the if clause and it would return *immediately* on *all* clients while FOR UPDATE blocks all other processes that try to SELECT the particular id (could be many, could be long). Of course, while SELECT...FOR UPDATE explicitly locks the row, the GET_LOCK functionality is an advisory technique where all participating sides have to obtain the lock (e.g. the code part that takes away the is_admin). Anyway, I think your concerns are a bit overkill and probably a flaw in your application design (e.g. why would you have an is_admin flag and then later set privileges => 'all' in another query) Would it not solve the problem if you had a save() that does both at once? -- 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 cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php
Re: Using SELECT... FOR UPDATE in cakephp
That is an interesting idea. (Although I don't think it would give me the
exact SELECT .. FOR UPDATE functionality, but I guess it's as close as I can
get).
Let me give it a try and see if it works the way you suggested and I'll get
back to you.
Thanks
Teddy
On Sat, Aug 13, 2011 at 12:51 PM, 0x20h wrote:
> Maybe an alternative would be to use mysqls GET_LOCK...
>
> e.g.
> $record = find('first', array(...,'fields' => array('GET_LOCK("foo",0)
> AS lock', ...)
>
> if ($record[0]['lock']) {
> ...
> }
>
> didn't try that, just a shot in the blue. Of course the named lock
> should be unique on what you want to lock.
>
>
> Am 10.08.2011 19:10, schrieb Teddy Zeenny:
> > Hi,
> >
> > Is there a way in cakephp to use SELECT .. FOR UPDATE (for InnoDB row
> > level locking) without using the $Model::query function (as this is
> > highly not recommended) ?
> >
> > If not, does anyone know an alternative ?
> >
> > Thanks,
> >
> > Teddy
> >
>
>
> --
> 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
> cake-php+unsubscr...@googlegroups.com For more options, visit this group
> at http://groups.google.com/group/cake-php
>
--
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
cake-php+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php
Re: Using SELECT... FOR UPDATE in cakephp
Model::query lets you run free form but then cakephp automagic may be off kilter so you end up losing more benefits of using cake On 13 August 2011 18:54, Zaky Katalan-Ezra wrote: > >> "without using the $Model::query function (as this is highly not > recommended)" > Why its not recommended? > > -- > 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 > cake-php+unsubscr...@googlegroups.com For more options, visit this group > at http://groups.google.com/group/cake-php > -- 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 cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php
Re: Using SELECT... FOR UPDATE in cakephp
>> "without using the $Model::query function (as this is highly not recommended)" Why its not recommended? -- 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 cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php
Re: Using SELECT... FOR UPDATE in cakephp
Maybe an alternative would be to use mysqls GET_LOCK...
e.g.
$record = find('first', array(...,'fields' => array('GET_LOCK("foo",0)
AS lock', ...)
if ($record[0]['lock']) {
...
}
didn't try that, just a shot in the blue. Of course the named lock
should be unique on what you want to lock.
Am 10.08.2011 19:10, schrieb Teddy Zeenny:
> Hi,
>
> Is there a way in cakephp to use SELECT .. FOR UPDATE (for InnoDB row
> level locking) without using the $Model::query function (as this is
> highly not recommended) ?
>
> If not, does anyone know an alternative ?
>
> Thanks,
>
> Teddy
>
--
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
cake-php+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php
Re: Using SELECT... FOR UPDATE in cakephp
This would allow me to begin and then commit/rollback transactions.
However this does not perform row level locking.
I will show an example to illustrate what I mean.
$DS=$this->getDataSource();
$DS->begin();
$user=$this->findById(1);
// <-- A different process might
update this user here setting "is_admin" to false
if($user['User']['is_admin']) // Therefore I
cannot ensure that this value is still the same
{
if($this->save(array('User'=>array('id'=>1,'privileges'=>'all' //
-- The save will be successful even though the user is no
longer admin
{
$DS->commit(); // I
just committed giving all privileges to a user who is not an admin
}
else $DS->rollback();
}
What I really need is somethign like this:
$user=$this->findById(1,array('lock'=>'for update')); //ps: i know there's
no such thing in cakephp
// this would lock the row with id=1 until I commit or rollback my
transaction
//the actual query would be like this: SELECT * FROM users where id=1 FOR
UPDATE
Thanks :)
On Fri, Aug 12, 2011 at 9:29 PM, Dr. Loboto wrote:
> Start transaction, select, save, close transaction. Something like
> this:
>
> $DS = $this->getDataSource();
> $DS->begin();
> $this->find(...);
> if ($this->save(...)) {
> $DS->commit();
> }
> else {
>$DS->rollback();
> }
>
> On 11 авг, 00:10, Teddy Zeenny wrote:
> > Hi,
> >
> > Is there a way in cakephp to use SELECT .. FOR UPDATE (for InnoDB row
> > level locking) without using the $Model::query function (as this is
> > highly not recommended) ?
> >
> > If not, does anyone know an alternative ?
> >
> > Thanks,
> >
> > Teddy
>
> --
> 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
> cake-php+unsubscr...@googlegroups.com For more options, visit this group
> at http://groups.google.com/group/cake-php
>
--
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
cake-php+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php
Re: Using SELECT... FOR UPDATE in cakephp
This would allow me to begin and then commit/rollback transactions.
However this does not perform row level locking.
I will show an example to illustrate what I mean.
$DS=$tis->getDataSource();
$DS->begin();
$user=$this->findById(1);
if($user['User']['is_admin'])
{
if($this->save(array('User'=>array('id'=>1,'privileges'=>'all'
{
}
}
On Aug 12, 9:29 pm, "Dr. Loboto" wrote:
> Start transaction, select, save, close transaction. Something like
> this:
>
> $DS = $this->getDataSource();
> $DS->begin();
> $this->find(...);
> if ($this->save(...)) {
> $DS->commit();}
>
> else {
> $DS->rollback();
>
> }
>
> On 11 авг, 00:10, Teddy Zeenny wrote:
>
>
>
>
>
>
>
> > Hi,
>
> > Is there a way in cakephp to use SELECT .. FOR UPDATE (for InnoDB row
> > level locking) without using the $Model::query function (as this is
> > highly not recommended) ?
>
> > If not, does anyone know an alternative ?
>
> > Thanks,
>
> > Teddy
--
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
cake-php+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php
Re: Using SELECT... FOR UPDATE in cakephp
Start transaction, select, save, close transaction. Something like
this:
$DS = $this->getDataSource();
$DS->begin();
$this->find(...);
if ($this->save(...)) {
$DS->commit();
}
else {
$DS->rollback();
}
On 11 авг, 00:10, Teddy Zeenny wrote:
> Hi,
>
> Is there a way in cakephp to use SELECT .. FOR UPDATE (for InnoDB row
> level locking) without using the $Model::query function (as this is
> highly not recommended) ?
>
> If not, does anyone know an alternative ?
>
> Thanks,
>
> Teddy
--
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
cake-php+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php
Using SELECT... FOR UPDATE in cakephp
Hi, Is there a way in cakephp to use SELECT .. FOR UPDATE (for InnoDB row level locking) without using the $Model::query function (as this is highly not recommended) ? If not, does anyone know an alternative ? Thanks, Teddy -- 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 cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php
Re: SELECT ... FOR UPDATE
I don't understand why you are using a separated table for the invoices numbers, but anyway 1. Insert a new row to invoicenumbers. 2. get the last autoincrement value by calling $this->getLastInsertID() from the model. The next user will get a different id On Tue, Oct 5, 2010 at 2:40 AM, andres amaya diaz wrote: > Hi, hope you can help me with a SELECT .. FOR UPDATE problem. > > I have a web app that can be accessed by many users at the same time. > I have a table with a field wich has a counter on the next invoice > number. Lets call if invoicenumbers (this field cant be > auto_increment) > And then i have another table where i need to store the invoices data > Lets call it invoices > > So lets see: > A user enters Save Invoice, i need to do a SELECT or FIND to the > invoicenumbers to get the next invoice number and then an UPDATE to > invoicenumbers to set the field counter = counter+1. > But maybe between the select and the update another user Saves another > Invoice wich will do the same (SELECT and the UPDATE) and that is a > remote posibility that they both get the same Invoice Number and then > both UPDATE the table increasing the counter by 2. > > I hope i made myself clear. > > Thanks in advance ... aad > > Check out the new CakePHP Questions site http://cakeqs.org and help others > with their CakePHP related questions. > > You received this message because you are subscribed to the Google Groups > "CakePHP" group. > To post to this group, send email to cake-php@googlegroups.com > To unsubscribe from this group, send email to > cake-php+unsubscr...@googlegroups.comFor > more options, visit this group at > http://groups.google.com/group/cake-php?hl=en > -- Regards, Zaky Katalan-Ezra QA Administrator www.IGeneriX.com Sites.IGeneriX.com 054-7762312 Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
SELECT ... FOR UPDATE
Hi, hope you can help me with a SELECT .. FOR UPDATE problem. I have a web app that can be accessed by many users at the same time. I have a table with a field wich has a counter on the next invoice number. Lets call if invoicenumbers (this field cant be auto_increment) And then i have another table where i need to store the invoices data Lets call it invoices So lets see: A user enters Save Invoice, i need to do a SELECT or FIND to the invoicenumbers to get the next invoice number and then an UPDATE to invoicenumbers to set the field counter = counter+1. But maybe between the select and the update another user Saves another Invoice wich will do the same (SELECT and the UPDATE) and that is a remote posibility that they both get the same Invoice Number and then both UPDATE the table increasing the counter by 2. I hope i made myself clear. Thanks in advance ... aad Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
