|
View previous topic :: View next topic
|
| Author |
Message |
pkmurali
Warnings : 1
Active User
Joined: 15 Dec 2005
Posts: 271
|
|
|
|
This was a question asked in a interview,
Interviewer:You have to design for an application where the application is accessed by online and batch program simultaneously. Assume your online application needs to update 5 rows in Table-A simultaneously your batch program needs to update 5 rows in Table-A. how you will design this application?
I said CS(Cursor Stability) will be used to maintain the concurrency.
Interviewer: If both application is trying to access the same resource at same time,how the application should be designed? Even using CS level the lock will be held in page level,after anyone of the application release the lock or move from the current page the other gets the opportunity to work on it. How this application should be designed??
Please let me know your suggestions.
Thanks,
Murali. |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| Even using CS level the lock will be held in page level, |
This is a holdover from long ago. . . Good database systems support row level locking (DB2 does now also) and have for decades. . .
Many places keep page level locking because they don't have the experience or the confidence to install other than the "way it has always been". They also tend to use the old/original subsystem id's . . .
And they have lots of "opportunities" that should not be. . .  |
|
| Back to top |
|
|
pkmurali
Warnings : 1
Active User
Joined: 15 Dec 2005
Posts: 271
|
|
|
|
| Where we have to specify the row-level lock? in Bind card? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
On the CREATE TABLESPACE statement is where the systems i support do this. |
|
| Back to top |
|
|
pkmurali
Warnings : 1
Active User
Joined: 15 Dec 2005
Posts: 271
|
|
|
|
| Interesting. If the lock mechanism is fixed then, how 'lock escalation' concept will be implemented? For example, if my application is switched from 'update' to 'browse' in such case tablespace needs to be changed? Do application dynamically won't change the lock level with the help of 'lock escalation'? Please let me know,whether 'lock escalation' is applicable. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Suggest you spend some time with your dba and in the many discussions available in the IBM material as well as other papers that have been published. Some of the very basic concepts of multi-user access/update are not yet clear to you.
How do you "switch an application from update to browse"?
How did lock escalation become an item of concern? On properly implemented systems this rarely or never occurs. . . |
|
| Back to top |
|
|
pkmurali
Warnings : 1
Active User
Joined: 15 Dec 2005
Posts: 271
|
|
|
|
Dick,
It's not lock escalation,apologies. It's 'lock promotion'.
From ibm site
| Quote: |
Lock promotion
Lock promotion is the action of exchanging one lock on a resource for a more restrictive lock on the same resource, held by the same application process.
Example
An application reads data, which requires an IS lock on a table space. Based on further calculation, the application updates the same data, which requires an IX lock on the table space. The application is said to promote the table space lock from mode IS to mode IX.
|
How to acheive the above example if we define IS in tablespace? If DB2 itself does the job of lock promotion or it can be done manually? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
Lock promotion is a change on restrictness on the same granularity :
ex.: From Intend Share to Intend Exclusive
Lock Escalation is a change of granularity on the same restrictness :
ex.: From page lock to Tablespace lock |
|
| Back to top |
|
|
|
|
