Java select for update

select «for update» with JDBC?

I want to create a for update select statement in Java using JDBC, but not sure how it would be done. If you are unfamiliar with for update you can read about it here https://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-FOR-UPDATE-SHARE For example, I have the following select statements My select statement

select email from email_accounts where already_linked = false order by random() limit 1 

UPDATE email_accounts set already_linked = true, account_link_timestamp = now() where email = ? 

1 Answer 1

You first add for update to your select (and your other columns you want to update), and then you update them. Also, as noted in the comments, make sure your getConnection returns a Connection without autocommit. And you need to set a Statement type for scrolling and CONCUR_UPDATABLE . Something like,

String[] colNames = < "email", "already_linked", "account_link_timestamp" >; String query = "select " + Stream.of(colNames).collect(Collectors.joining(", ")) + "from email_accounts where already_linked = false for update"; try (Connection conn = getConnection(); // Make sure conn.setAutoCommit(false); Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = stmt.executeQuery(query)) < while (rs.next()) < // Get the current values, if you need them. String email = rs.getString(colNames[0]); boolean linked = rs.getBoolean(colNames[1]); Timestamp time = rs.getTimestamp(colNames[2]); // . rs.updateBoolean(colNames[1], true); rs.updateTimestamp(colNames[2], // new Timestamp(System.currentTimeMillis())); rs.updateRow(); >> catch (SQLException e)

Источник

Select for update skip locked from JPA level

Using JPA and Hibernate, to produce a «SKIP_LOCKED» as per Hibernate LockMode documentation, you have to combine the PESSIMISTIC_WRITE JPA LockModeType: and the lock timeout setting, like for example in persistence.xml for your persistence unit: (Note that you can configure this LockMode for complex query as well) SKIP LOCKED is not part of ANSI SQL. Question: In my application — Oracle with JPA (EclipseLink), I’m using the following expression to lock the subset of the records in some tables: I run it throughout native query, but I have to write that query for all required entities.

Select for update skip locked from JPA level

In my application — Oracle with JPA (EclipseLink), I’m using the following expression to lock the subset of the records in some tables:

select * from MY_TABLE where MY_CONDITIONS for update skip locked 

I run it throughout native query, but I have to write that query for all required entities.

Is there any way to skip locked records using pure JPA? Can I implement my own locking policy?

I don’t mind changing JPA provider but I want to use JPA API.

Hibernate provides the UPGRADE_SKIPLOCKED Lock mode.

Using JPA and Hibernate, to produce a «SKIP_LOCKED» as per Hibernate LockMode documentation, you have to combine the PESSIMISTIC_WRITE JPA LockModeType:

entityManager.find(Department.class, 1, LockModeType.PESSIMISTIC_WRITE); 

and the lock timeout setting, like for example in persistence.xml for your persistence unit:

(Note that you can configure this LockMode for complex query as well)

SKIP LOCKED is not part of ANSI SQL. Some RDBMS such the following provide this as a specific feature:

So with pure JPA, it is not possible to specify a «SKIP LOCKED» in queries. Indeed, as documented in LockModeType, JPA 2.1 only supports the following:

• NONE
• OPTIMISTIC
• OPTIMISTIC_FORCE_INCREMENT
• PESSIMISTIC_FORCE_INCREMENT
• PESSIMISTIC_READ
• PESSIMISTIC_WRITE
• READ
• WRITE

However, to enable SKIP LOCKED in your query you can use these alternatives:

• Use specific JPA implementation feature, such as Hibernate LockMode which allows to specify the SKIP LOCKED via a JPA query, thanks to a combination of PESSIMISTIC_WRITE LockModeType Lock Timeout specific setting as described above
• Create a native SQL query as you did

I know this post is a bit old, but for the record, just as the accepted answer stated, «javax.persistence.lock.timeout» ( org.hibernate.cfg.AvailableSettings#JPA_LOCK_TIMEOUT ) set to «-2» ( org.hibernate.LockOptions#SKIP_LOCKED ) with Hibernate results in «SKIP LOCKED». However, this can be done at run-time without having to set any global settings.

Since 2.0 JPA allows to pass hints along like so

entityManager.find(MyType.class, id, LockModeType.PESSIMISTIC_WRITE, new HashMap() >); 

For spring JpaRepository, this can be used:

String SKIP_LOCKED = "-2"; @QueryHints(@QueryHint(name = AvailableSettings.JPA_LOCK_TIMEOUT, value = SKIP_LOCKED)) @Lock(LockModeType.PESSIMISTIC_WRITE) List fooBar(); 

works well on Oracle, not sure about others

Oracle doesn’t provide a read lock, since it doesn’t need one; undo logs make it unnecessary. So, SELECT. FOR UPDATE is really just a WRITE lock for Oracle.

Using JPA, you want to set the LockModeType as a PESSIMISTIC_WRITE.

Spring JPA: avoid select before update, Spring JPA: avoid select before update. Ask Question Asked 2 years, 1 month ago. Modified 2 years, 1 month ago. (entity) method of JPA. However, whenever we are calling this api, select statement is getting executed before update query. can you please suggest how to avoid such select …

Spring Data JPA — Select row for update

I have a requirement to read the first enabled account from DB2 database table and immediately update the column to disable it. While server 1 is reading and updating the column, no other server should be able to read the same row since I want one account to be used by only one server at a time.

This is what I have so far..

public interface AccountRepository extends JpaRepository

 @Service public class AccountServiceImpl < @Autowrired private AccountRepository accntRepository; @Transactional public Account findFirstAvaialbleAccount()< Account account = accntRepository.findFirstByEnabled(new Character('Y')); if(account != null) < account.setEnabled(new Character('N')); //put debug point here account.save(proxyAccount); >return account; > > 

But this isn’t working.. I’ve put a debug pointer in the findFirstAvaialbleAccount() method. What I was expecting is, if the debug pointer reaches that line and waiting for me to resume execution, if I run select query directly on the database, the sql shouldn’t execute. It should only execute after I resume the execution on the server so that transaction is completed. But instead, running the select query directly on the database gave me the complete result set immediately. What am I missing here? I’m using DB2 if it matters.

Answering my own question. I had incorrect Select SQL running against the database. If I run the select sql with «select.. for update», then the execution waits until I hit resume on the server and transaction is complete.

SQL 1 — this executes immediately even though the transaction from server isn’t complete.

select * from MYTABLE where ENABLED = ‘Y’;

SQL 2- this waits until the transaction from server is complete (it will probably timeout if I don’t hit resume quick enough)

select * from MYTABLE where ENABLED = ‘Y’ fetch first 1 rows only with rs use and keep update locks;

Spring data jpa SELECT FOR UPDATE Query not, Take a look at the following answer for a question similar to yours: Select for update not working. Here is what it says: For Update basically puts a lock on rows, to achieve the same using JPA you need to use Lock Modes. To set the lock, you can use EntityManager or TypeQuery and use …

JPA: Pessimistic locking of a row with select for update in Oracle

I got an oracle table with data like the following:

create table myobjects(id int, objname varchar(20), state int); 

id / objname / state 1 / 'object no. 1' / 2 2 / 'object no. 2' / 1 3 / 'object no. 3' / 1 4 / 'object no. 4' / 1 5 / 'object no. 5' / 1 6 / 'object no. 6' / 1 7 / 'object no. 7' / 1 

Now I have multiple batch instances which should

1. lock one object row of the table with state = 1 (the statement(s) should return maximum one object row, the first available/unlocked object row that can be found or no row, if none is available)
2. set the state of the object row to 2, commit,
3. do some work for that row, set state to 3 and commit.

To generate the statements, I use openjpa. but if necessary, oracle native statements would be possible, too.

For step 1., I tried this statement:

select * from myobjects where state = 1 and rownum  

select * from myobjects where state = 1 and rownum  

declare id int; begin update myobjects set state=2 where state = 1 and rownum = 1 returning id into id; commit; dbms_output.put_line(id); end; 

Query createNativeQuery = entityManager.createNativeQuery( "select id from cscache where for update "); 

Cscache cscache = (Cscache )session.get( Cscache.class, id, LockOptions.UPGRADE ); 

EntityManager em; //Step 1: SELECT FOR UPDATE AccountBalance ab = em.find(AccountBalance.class, accountId, LockModeType.PESSIMISTIC_WRITE); //Step 2: Validation check if (ab.getBalance() - withdrawAmount < 0.00) < //Error handling. Abort transaction //. throw . ; //Get out of here >//Step 3: Proceed with operation ab.setBalance(ab.getBalance() - withdrawAmount);

TypedQuery q = em.createQuery("select c from Cart c where . ", Cart.class); q.setLockMode(LockModeType.PESSIMISTIC_WRITE);