|
View previous topic :: View next topic
|
| Author |
Message |
deepananda
New User
.jpg)
Joined: 27 Mar 2008
Posts: 8
Location: INDIA
|
|
|
|
I have coded a simple cursor which can extract millions of rows.
And the isolation was specified as CS.
Query1 : When the locks would be applied ?
a) During Open
b) During Fetch
Query2 : What is the Size of the lock?
a) All quallified millions of rows ( Which can be escalated to Table Space Lock)
b) Only a Page (4K) will be locked. |
|
| Back to top |
|
 |
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
| Back to top |
|
|
deepananda
New User
Joined: 27 Mar 2008
Posts: 8
Location: INDIA
|
|
|
|
Actually, the topic was started by one of my colleague. Still it wasn't clear on the Lock Size and we have some problem in production regading this.
Your feedback would be appreciated. |
|
| Back to top |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
If you have seen at manual...
Cursor Stability (CS) locks any row accessed by a transaction of an application while the cursor is positioned on the row. This lock remains in effect until the next row is fetched or the transaction is terminated. However, if any data on a row is changed, the lock must be held until the change is committed to the database.
Recalling the example of scanning 10 000 rows, if you use Cursor Stability, you will only have a lock on the row under your current cursor position. The lock is removed when you move off that row (unless you update that row).
If anything else, please be specific with your question so that someone would reply you clearly... |
|
| Back to top |
|
|
deepananda
New User
Joined: 27 Mar 2008
Posts: 8
Location: INDIA
|
|
|
|
| Thanks |
|
| Back to top |
|
|
Prajesh_v_p
Active User
Joined: 24 May 2006
Posts: 133
Location: India
|
|
|
|
Query1 : When the locks would be applied ?
a) During Open -: If your cursor query causes a datasort and fetch reads records from sort out file. It wont cause a row level lock even if you bind with CS, during a fetch
b) During Fetch -: If your SQL is not causing any sorts, then things will be exactly as what Gnanas said, unless and untill you specify the isolation level in your cursor.
This is what my understanding is. Please correct me if I am wrong.
Thanks,
Prajesh |
|
| Back to top |
|
|
deepananda
New User
Joined: 27 Mar 2008
Posts: 8
Location: INDIA
|
|
|
|
| Thanks a lot |
|
| Back to top |
|
|
|
|
