How to lock a table row use JDBC?

[icey]

I want to lock a record when I update or use it.
How to use JDBC to do this?
Thanks a lot!

[gong1]

agree with upstairs.
^_^

[bdsc]

Using the setTransactionIsolation() Method

TRANSACTION_SERIALIZABLE
specifies that dirty reads, nonrepeatable reads,
and phantom reads are prevented.

This is the compatible way.

[icey]

But I am use weblogic's connection pool,
and my jdbc invoke by ejb, its join ejb's transaction, I think it couldn't set transaction level for self connection.

Add "for update" at the end of that sql can lock that record? if lock how to open that lock? if update fail in later, will it rollback and auto open this lock?

[bdsc]

Ok then, where is your ejb deployer?

<transaction-isolation>
<isolation-level>TransactionSerializable</isolation-level>
<method>
<description>...</description>
<ejb-name>...</ejb-name>
<method-intf>...</method-intf>
<method-name>...</method-name>
<method-params>...</method-params>
</method>
</transaction-isolation>

Deployment file: weblogic-ejb-jar.xml

[icey]

We use <isolation-level>Required</isolation-level>
If I use TransactionSerializable, it will some error occured.
I have not investigate this problem.

[hiking]

Why do you want to lock the record when you use it?

In ejb, the container will automaticly reload the record wherever you want to read it.

<isolation-level> got lots types. you may try other type

[bdsc]

It looks like you could not master your whole life.
try this, you send some vendor specific sql query to DB and lock the tables, then do what ever you want, then unlock them,

MySQL:
String sql = "xxxxxxxxxxxxxxx";
stat.execute("lock tables tableName WRITE"); //shold check the result
rs = stat.executeQuery(sql);
while(rs.next()) {
String sql_in = "update tableName set value=xxx";
another_stat.execute(sql_in);
}
stat.execute("unlock tables");

[icey]

you tell me to lock whole table, I don't want to do in this way.
I just want to lock the records that I selected.

Because between I select this record and modify its value on page and update this, other user couldn't modify same record.

[youthy_yy]

you can try this:
SELECT * FROM table_name FOR UPDATE;

[icey]

Who know this?