select for update

HI, Coud I use 'select for update' clause to prevent others read the same record? the default isoaltion level is 'committed read', ids version is 11.5. in windows one: I excute： dbaccess stores_demo; begin work; set isolation committed read retain update locks; select * from customer where customer_num=101 for update in windows two: 'onstat -k ' display one record in customer table being locked. then I execute: dbaccess stores_demo; select * from customer where customer_num=101 for update The query return the result. It isn't my expection, How to implement it in IDS? thanks for your time.

AFAIK, using select with 'for update' at committed read isolation doesn't prevent other people from reading a record. You would prevent other people from modifying it while you have the cursor on the record — but until you actually update it, you won't stop people reading it. You can't stop people running at dirty read isolation level from reading the data; you also wouldn't stop people running at last committed isolation level from reading the last committed value. On Fri, Apr 26, 2013 at 8:46 PM, CHUAN LU <luchuan@cn.ibm.com> wrote: > HI, > > Coud I use 'select for update' clause to prevent others read the same > record? > > the default isoaltion level is 'committed read', ids version is 11.5. > > in windows one: > > I excute&#65306; > > dbaccess stores_demo; > > begin work; > > set isolation committed read retain update locks;> > select * from customer where customer_num=101 for update > in windows two:> > 'onstat -k ' display one record in customer table being locked. > > then I execute: > > dbaccess stores_demo; > > select * from customer where customer_num=101 for update> > The query return the result. > > It isn't my expection, How to implement it in IDS? > > thanks for your time. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2013.0118 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --089e01634f7012fc6c04db50fd5a

so if the database level can't provent others read the same record, it might has problem in the high concurrent environment. For example, in the core banking system, I withdraw in the ATM using my card ,at the same time, my family do money transfer for me with the same card. It's really that we can't avoid this in database level?

Updates are wholly different from reading. Updates are locked, regardless of isolation level (even if you're running at dirty read isolation, your updates are locked until the action is committed). If you're careless with how you write your updates, then you can get into difficulties. However, the select for update will stop other people also selecting the same row *for update* at the same time, but you're no longer 'just reading' the data — you are reading with intent to write, which is a very different operation. Repeatable read isolation can be affected by intent to update; repeatable read has to ensure that the same data will be seen again, and there are intent locks. But that doesn't stop people from reading the unaltered data — it just stops them from reading with intent to alter or actually altering the data. On Sat, Apr 27, 2013 at 4:42 AM, CHUAN LU <luchuan@cn.ibm.com> wrote: > so if the database level can't provent others read the same record, it > might > has problem in the high concurrent environment. > For example, in the core banking system, I withdraw in the ATM using my > card > ,at the same time, my family do money transfer for me with the same card. > > It's really that we can't avoid this in database level? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2013.0118 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --089e0163449898a43304db5b203f

So, you do not need to prevent users from reading the data row you are about to update. Why? What if you and your wife both go to different cash machines at the same time. Do you want one of you to have to wait until the other one is finished before being able to see the account balance? No. What you want is to make sure that no user updates a row that has been updated or deleted by another user since the first user looked at the row. This is handled by using what is known as Optimistic Locking Protocol. Look it up online or download my presentation from this year's IIUG Conference entitled "Best Practices for Informix Developers" which covers this exact question (among others) from the members' pages section of the IIUG Websites. Informix has several features to better support Optimistic Locking. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sat, Apr 27, 2013 at 7:42 AM, CHUAN LU <luchuan@cn.ibm.com> wrote: > so if the database level can't provent others read the same record, it > might > has problem in the high concurrent environment. > For example, in the core banking system, I withdraw in the ATM using my > card > ,at the same time, my family do money transfer for me with the same card. > > It's really that we can't avoid this in database level? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3b9c29b7a10604db64d57b

Hi Chuan, I had much the same thoughts/misunderstandings about 'select for update' and blogged about it here: http://informixdba.wordpress.com/2013/02/25/select-for-update/ Others also contributed their comments on the same blog post. The only way to exclusively lock a single row is to actually update it. So if you just want to lock it to prevent others reading it and don't want to damage concurrency by placing an exclusive table lock, you will need to start with some kind of dummy update within a transaction. Ben.