Locks for select clause

[Former Member]

Hi,

I am looking for the lock clause to be used with "SELECT" statement for the following scenarios:

Read Lock:

Update Lock:

To make things easy to illustrate, this is what is used for 2 different databases to achieve the same.

MySQL

======

Read Lock: default

Update Lock: "LOCK IN SHARE MODE"

Oracle:

=====

Read Lock: default

Update Lock: "FOR UPDATE"

Regards

Raja

[Former Member · Accepted solution]

Hi,

perhaps you may want to read a little bit more details about the different options of the lock-option:

http://maxdb.sap.com/doc/7_6/da/ed9036dfe4b903e10000009b38f889/frameset.htm

And please see the difference between optimistic and exclusive lock.

Elke

[Former Member]

From documentation what i learn is i should be using the [<lock option>] after the select clause

SELECT [<distinct_spec>] <select_column>,... INTO <parameter_spec>,... FROM <from_table_spec>,... [<where_clause>] [<group_clause>] [<having_clause>] [<lock_option>]

<lock_option> ::=

WITH LOCK [(IGNORE)|(NOWAIT)] [EXCLUSIVE|OPTIMISTIC] [ISOLATION LEVEL <unsigned_integer>]

So, for update if i use "WITH LOCK" after the select clause it should be fine i guess, since the SHARED MODE is taken as default if EXCLUSIVE|OPTIMISTIC is not mentioned.

Please correct me if i am wrong.

Regards

Raj

Edited by: Raja Panda on Sep 16, 2008 9:01 AM