How lock a database register

[mamntc02]

Hi all,
That's what I need:
Select an unique register and lock it, both read and write.
On demand, unlock register and update its value.

Thats' what I did:
I use Spring's JdbcTemplate to access data. To lock this register I use 'for update' statement. In other words:

private final String query = "select TIPO_CONTADOR, ID_CONTADOR, VALOR_CONTADOR, FECHA " + 
	"from T_CONTADORES where TIPO_CONTADOR = ? AND ID_CONTADOR = ? for update";
private final String update = "update T_CONTADORES set VALOR_CONTADOR = ? " + 
	"where TIPO_CONTADOR = ? AND ID_CONTADOR = ?";
[...]
ContadorData cd = (ContadorData) this.jdbc.queryForObject(query, new Object[]{tipoCont, idCont},
		new RowMapper(){
			public Object mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				[...]}
			});

However this seems to lock only write not read. Is it possible to lock read, too? That is, throw an exception (or sqlcode or whatever which I can realize whether is locked) if I try to read a register, which is locked.

Sorry if this question is already asked and answered. I didn't find it.

Thanks a lot.

Regards.

[satsranchuser]

Why would you want to lock a read only data. Can you try to see if you can put a security on column level so that only selected users can read the data??

[mamntc02]

Why would you want to lock a read only data. Can you try to see if you can put a security on column level so that only selected users can read the data??

Hi satsranchuser,
Thanks for your answer, but that's not enough, because it does not depend on user. The purpose of this method is build a counter. That is:
I have a table with 3 fields. Two of them are the key of the table. The third one is a counter.
I receive a request for a counter (using the key fields), so I look for the required counter. Once I sent that counter I need to lock that register until I receive a confirmation request. Then I add some values to the counter and unlock the register.

I need to lock read register, because someone else could ask for the same counter. Think in this:

• Request from user (or application) App1 for counter AB/AS1 (field1/field2), For example, I get 6 in counter.
• Request from App2 for counter AB/AS1. If the register is unlocked I will return 6 again to user App2.
• Request for confirmation for App1. Then I add 1 to the counter. So counter is 7.
• Request for confirmation for App2. Again counter will be 7.
  So, there are two users (applications) using the same value for counter. It cannot be. That's why I need to lock the register on read/write. The second request must wait until timeout received or until the register is unlocked.

There is another way to perform this application?
Thigs I cannot do, because of workspace:

• Requests are independent, and they cannot be stored in memory. In other words, when I receive a request there's no way to know (by Java code), if this request (for a counter) is already in use (ie, someone is working with that counter).
• I cannot modify the table structure. So, I cannot add a flag field to indicate the register is in use.
• And the most important: I cannot lock the table, just the register required.

In spite of my english, I hope you understand the purpose. I'll appreciate any hint to solve it or how I can lock a database register on read/write.

Regards.

[dejanp]

Execute the counter update first, then the select, then commit.

[mamntc02]

Execute the counter update first, then the select, then commit.

Thanks but I can't, because I don't know the increment of the counter until I receive the confirmation request.

Is there no way lo lock a register for read and write?

Regards.

[dejanp]

Well, if you use Oracle you can do select ... for update, for other databases you can always execute a dummy update before selecting - something like "update id=id from table where id=?".

[mamntc02]

Well, if you use Oracle you can do select ... for update, for other databases you can always execute a dummy update before selecting - something like "update id=id from table where id=?".

Yes, I use Oracle. But as far as I know, "select ... for update" statement only locks on write. Is this right?
I need both read and write.

Thanks a lot.

Regards.

[dejanp]

"select for update" sets a write lock on the record. That means other transactions can still read the record using normal selects, but not using "select for update" (they will block), nor can they update it (will block too).

[mamntc02]

"select for update" sets a write lock on the record. That means other transactions can still read the record using normal selects, but not using "select for update" (they will block), nor can they update it (will block too).

That's what I said... at least that was my intention Maybe my terrible english confuses
I also need to lock the register for reading operations. It means, if I select a locked register it should return nothing or throw an exception, or whatever that make me realize that register is locked. Is it not possible to do?

Regards.

[dejanp]

Can't you make sure that you always read using selectForUpdate? Usually there is exactly one spot in code where such reads take place.