Will a cursor fetch lock the table

[peppercrab]

Open a cursor , Fetch the record ,
during this kind of operation , will the specific table be locked and fail
to be updated or select by another session ?

[skhanal]

Depends on the type of cursor created. At the minimum the row you are fetching is shared locked during select.

[peppercrab]

I think shared lock will not affect
any updation or selection triggered by another session ?

[skhanal]

shared lock will block update but not select. Because update requires an exclusive lock.

[peppercrab]

Do you mean that while fetching a record in the cursor , the specific row will locked and the updation will be failed or have to wait !

it surprise me ! because this never happens in oracle !

[rmiao]

You can add nolock hint in your select statement.

[YuckFou]

NOLOCK hint shows all data also uncommited. Oracle shows only commited. So in MSSQL you can use READPAST hint to read only commited transaction.

[skhanal]

Yes it is not same as Oracle, update is blocked by select for the duration of select.