JDBC Template SELECT for UPDATE PostgreSql (lock row for read)

[rguinko]

Hi everybody,
i would like to select a table row and lock it in same time. I read on some post that this can be done by executing a SELECT ... FOR UPDATE query. I tried this but it does not work with SimpleJDBCTemplate. Here is what i did :

@Override
	@Transactional(isolation=Isolation.REPEATABLE_READ )
	public Delivery findForUpdateByDeliveryStatus(DeliveryStatus statusToFind, DeliveryStatus newStatus) {
		String sql = "select * from delivery where status = ? order by creation_date desc limit 1 FOR UPDATE NOWAIT";
		List<Delivery> deliveries = 
			super.getSimpleJdbcTemplate().query(sql, new DeliveryParameterizedRowMapper(), statusToFind.value());
		Delivery del = deliveries.get(0);
		this.updateStatus(del, newStatus);
		return del;
	}

public int[] updateStatus(Delivery delivery, DeliveryStatus status) {
		String sql = "UPDATE "+Delivery.TABLE_NAME+" SET "+ColumnName.STATUS.value()+" ='"+status.value()+"' " +
		"WHERE "+ColumnName.DELIVERY_ID+" = "+delivery.getDeliveryId();
		return this.insertBatchSQL(sql);
	}

	private int[] insertBatchSQL(final String sql){
		return super.getJdbcTemplate().batchUpdate(new String[]{sql});
	}

Does anyone has had this problem? may i have an example?

Thanks for your help,

[dr_pompeii]

Hello

I read on some post that this can be done by executing a SELECT ... FOR UPDATE query

Could you provide such link?

I tried this but it does not work with SimpleJDBCTemplate

How you got such conclusion?

[dlmiles]

And which SQL server do you have ? As the syntax of this operation can be slightly different for different SQL servers, I don't think the feature is mandated.

The mechanism is called "pessimistic locking".

It is useful for example when you have a web application that registers an email address into your user account table and this field needs to be unique, however you wish to preserve the case of the email address the user gives you (a personal choice) but make sure you lock out a normalize all-lowercased form. So you SELECT .. FOR UPDATE on your normalized value, this in effect reserves the phantom key space for an as yet not inserted record. You can if you need perform a bunch of queries (possibly to make sure that email address is not already taken in mixed/alternative case forms), then once you are happy, you can insert the new row there yourself, commit the transaction which causes unlock. If this operation is repeated concurrently your user account table will never have duplicates no-MaTTer.what.case@email-ADDRESS.is.used.

Now in order to use this feature there are some rules in place and those rules maybe different for different SQL implementations. One rule maybe that only key space is locked, meaning you must include in your SELECT columns at least one column that is fully or partially covered by the keyspace of an index.

I do not believe this feature is well specified by international/multi-vendor agreement (like ANSI SQL specs) so this is why the rules can be SQL server specific.

I would guess from the "LIMIT 1" syntax it is MySQL. If your 'status' column is not covered by index/key-space and you research and find MySQL only locks things that are, you can try adding an index or there is also the LOCK TABLES command you can look at. [I just re-read the thread subject, so Postgres, maybe it had equivalent command]


I also note you have "NOWAIT" keyword. Try removing this keyword and see if your concurrent testing starts to work. You normally want your application to wait to get the lock (at least for a limited amount of time). Again different implementations might do different things regarding how to handle NOWAIT when it was already locked. The SQL server might throw an error, or it might not throw an error but instread indicate lock sucess/failure in "Row Count" meta information in the reply.


Also I'd like to know myself what happens when Spring has an already open transaction that is a lower isolationLevel and meets your @Transactional, I did not think SQL servers allowed you to upgrade your isolationlevel on the fly in the middle of an already open JDBC transaction. The isolationLevel is something you decide and set before you begin your transaction. Maybe Spring will verify the isolatation is already set (or throw an error) if a transaction is open and attemp to set isolationLevel is not transaction is open.