# Select ...  For Update

**URL:** <https://techcommunity.softwareag.com/t/select-for-update/173652>\
**Category:** Forum\
**Tags:** Adabas-Natural, Adabas, Adabas-Add-on-Products, SQL-Access, Mainframe-Integration, CONNX\
**Created:** [December 10, 2013, 9:54pm UTC](https://techcommunity.softwareag.com/t/select-for-update/173652 "2013-12-10T21:54:09Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Min\_Chai](https://avatars.discourse-cdn.com/v4/letter/m/71c47a/32.png) [@Min\_Chai](https://techcommunity.softwareag.com/u/Min_Chai)\
**Post date:** [December 10, 2013, 9:54pm UTC](https://techcommunity.softwareag.com/t/select-for-update/173652/1 "2013-12-10T21:54:09Z")

</div>

If I want to select a row and lock it for cross-examination before making an update and release, how can I use select for update to achieve what I want to do?

An example would be much apprecaited.

Thanks.

---

<div class="post-metadata">

**Author:** ![Larry\_McGhaw](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/larry_mcghaw/32/562_2.png) [@Larry\_McGhaw](https://techcommunity.softwareag.com/u/Larry_McGhaw)\
**Post date:** [December 10, 2013, 11:26pm UTC](https://techcommunity.softwareag.com/t/select-for-update/173652/2 "2013-12-10T23:26:57Z")

</div>

The technique varies depending on which data access method you are using. For Java, it would be something like this:

```auto
                ps = connectionObj.prepareStatement("SELECT AREA_CODE,PHONE,BIRTH,PERS_ID FROM EMPLOYEES WHERE PERS_ID = ? FOR UPDATE OF AREA_CODE,PHONE,BIRTH" );
                ps.setString(1, "pick an id");
                ResultSet rs2;
                rs2 = ps2.executeQuery();
                while (rs2.next())
                    {
                    ps3 = connectionObj.prepareStatement(" UPDATE EMPLOYEES SET AREA_CODE=?,PHONE=?,BIRTH=? WHERE CURRENT OF " + rs2.getCursorName() );
                    ps3.setInt(1, rs2.getInt(1)+1);
                    ps3.setInt(2, rs2.getInt(2)+1);
                    ps3.setInt(3, rs2.getInt(3)+1);
                    ps3.executeUpdate();
                    }
                rs2.close();
```

---

<div class="post-metadata">

**Author:** ![Min\_Chai](https://avatars.discourse-cdn.com/v4/letter/m/71c47a/32.png) [@Min\_Chai](https://techcommunity.softwareag.com/u/Min_Chai)\
**Post date:** [December 16, 2013, 9:59pm UTC](https://techcommunity.softwareag.com/t/select-for-update/173652/3 "2013-12-16T21:59:40Z")

</div>

Larry,

I realize that I did not ask my questions correctly. I need to be able to hold a series of rows and update the column of each rows. In my case, the select would return about 10 rows and I want to hold all 10 rows, update each row, and do a commit on all 10 rows at once.

Can I do a declare cursor and then do an open cursor with connx/adabas?

Thanks,  
Min

---

<div class="post-metadata">

**Author:** ![Larry\_McGhaw](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/larry_mcghaw/32/562_2.png) [@Larry\_McGhaw](https://techcommunity.softwareag.com/u/Larry_McGhaw)\
**Post date:** [December 16, 2013, 10:25pm UTC](https://techcommunity.softwareag.com/t/select-for-update/173652/4 "2013-12-16T22:25:01Z")

</div>

Yes this is possible using the provided example - just modify the “outer” query to return more than 1 row. You will notice that the inner loop is already designed to process multiple rows allowing you to update them. The last and final step would be a commit - to commit all 10 changes at once.

Please keep in mind however that adabas transactions are not like a typical relational database - meaning that adabas has “dirty reads” and does not support typical isolation levels. As soon as a row is updated, that update is seen by all readers - even before the commit occurs. The commit/rollback just provides a mechanism to rollback a group of changes as a unit of work - it does not provide any isolation.

---

<div class="post-metadata">

**Author:** ![Min\_Chai](https://avatars.discourse-cdn.com/v4/letter/m/71c47a/32.png) [@Min\_Chai](https://techcommunity.softwareag.com/u/Min_Chai)\
**Post date:** [December 17, 2013, 2:07pm UTC](https://techcommunity.softwareag.com/t/select-for-update/173652/5 "2013-12-17T14:07:44Z")

</div>

Larry,

Thanks for clarifying. Stupid me wrote a commit after the 1st update. No wonder my ADABAS REVIEW report shows that all subsequent L6 were no longer held for update.

Thank you for your patient.

Min

---

<div class="post-metadata">

**Author:** ![system](https://global.discourse-cdn.com/techcommunity/original/3X/c/d/cd19967bb605d28a72d77eb83a1cae22f3df2eb8.svg) [@system](https://techcommunity.softwareag.com/u/system)\
**Post date:** [April 2, 2021, 3:29pm UTC](https://techcommunity.softwareag.com/t/select-for-update/173652/6 "2021-04-02T15:29:58Z")

</div>


