View previous topic :: View next topic
|
Author |
Message |
rajandhla
Active User
Joined: 18 Oct 2005 Posts: 182 Location: Luton UK
|
|
|
|
Hi,
Contention is going on for of one my job and it is critical and the business has approved and fine to get exclusive locks.
I got a doubt if some body is using the resource (table) then how to obtion the lock by forcibily and disconnecting the current user.
Then i will release the locks at the end of the job.
Any ideas how to get exclusive locks by force
Thanks
Jai |
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
In your application program code
Code: |
LOCK TABLE <TABLENAME> IN EXCLUSIVE MODE
|
|
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Typical hammer solution, instead of determining the problem. |
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
Contention in Jai's system is something which we cannot look into and figure out what the problem is... and more over he has mentioned that business has already approved to get exclusive locks , now what is left is the solution for this .... |
|
Back to top |
|
|
ascelepius
New User
Joined: 16 Jun 2008 Posts: 35 Location: bangalore
|
|
|
|
I agree with ashimer, there cases when long running threads maybe accessing Tables. In such cases, when batch programs may need to insert/update, these programs will have to ATTEMPT to lock the Table or Partition in exclusive mode and then continue with DML operations. I must emphasize here that you cannot disconnect the user, the program will have to wait(which can be handled within the program) to gain exclusive access to the table/partition.
regards,
ASCI(I) |
|
Back to top |
|
|
rajandhla
Active User
Joined: 18 Oct 2005 Posts: 182 Location: Luton UK
|
|
|
|
I did tried and identified the problem and the resource that is causing the problem....
this jobs runs in a month and considering the cost benfit business is fine to run the job by obtaining exclusive locks.
But still is there any way to avoid contention for a table by doing some thing at database level.
Table space is a segmented table space.
plz let me know ur thoughts.
Thanks,
jai |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Why do application programs need to have exclusive control over a table, when reorg utilities can reorg a db2 table without shutting out users?
I really don't care if management has accepted the advice of someone who suggested A. I am not here to be a 'yes man'.
Now, you can give him all the code in the world, but if the OP is so inexperienced that he can not find the 'DB2 Table lock command and syntax' in the manual, why do you assume that everything is hunky doory and on with the rookie solution?
But I have answered my question. If the OP can't use the manual, why expect him to determine his contention issues? |
|
Back to top |
|
|
ascelepius
New User
Joined: 16 Jun 2008 Posts: 35 Location: bangalore
|
|
|
|
Yup DBZ is correct...
But as always "It depends"!
Enforcing a exclusive access on tables is not your best solution. Locks maybe acquired on Tables due to other batch/online programs that may access your Table for DML operations.
You could therefore,
a) change the locksize of your table to lower granularity(row maybe) after you have assessed the maximum locks that can be aquired on your tablespace. This would reduce some amount of contention.
b) Partition your table after analyzing your data and allow applications to access only partitions they need.
c) Setup a semaphore system that does not allow other applications to access the table or partition so that your can weed out contention once in for all.
If you feel these maybe too much of an overhead, I guess you can code the exclusive lock and gain exclusive access if your batch job can execute without shutting out users for too long...
regards,
asci(i) |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
And what happens when the next 20 "most important" or "critical" processes are identified. . . ?
It won't be too long until there is no reason to have a multi-user database system. Everything will be so important that it must have exclussive control of the table(s) it uses. And let us not forget the "special" online users - they too should not have to share. When they want to run their transactions, all other use should be prevented. I've actually been to a couple of sites that tried to implement this way and fortunately, the management that insisted on such bad practices were excused from further employment.
A far better solution is to properly manage the data resource. |
|
Back to top |
|
|
ascelepius
New User
Joined: 16 Jun 2008 Posts: 35 Location: bangalore
|
|
|
|
Quote: |
A far better solution is to properly manage the data resource.
|
It depends...(and how is another topic of debate) |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Quote: |
and how is another topic of debate |
Yup, it surely is. . .
d |
|
Back to top |
|
|
|