1) It works only if the locking user is dbadmin.
2) No special pervilages to dbadmin except that is the owner of the database.
3) Our DBA has no clue. I took a snap shot of the database log file which may help identify the reason.
You are right the issue has now morphed. Originally when I posted I did not know that was the case.
a) I mean both unix and connection to db.
b) HP Unix 11.11 - Progress 10.1C
c) DataBase started by dbadmin.
Thank you.
Follows my test as user jamaly locking, after that as dbadmin locking.
You are still only showing one side of the problem (as originally stated, it may be that this has now morphed into such a different problem that that no longer matters).
Because this has been portrayed as a lock conflict issue (original post: "We need to disconnet a user that is locking record(s) needed by a batch process") I'm trying to follow along on my system -- I have 3 windows open. One for "session 1", another for "session 2" and a 3rd for PROMON. But I see nothing here to put into session 2. And no evidence of any lock conflict.
Notice the content of databse log. They are different in these 2 cases.
You lost me. I don't know what is different or what the 2 cases are.
find first gen_loc
where gen_loc.entity = ""
and gen_loc.loc = "01"
exclusive-lock no-error.
if available gen_loc then do:
update gen_loc.add1.
end.
[While the update is waiting for go]:
What does "waiting for the go" mean?
I see a program which is either going to get an exclusive lock and update, or which will block with a "gen_loc in use by..." message if someone else has a lock on gen_loc...
So what happens? Are you prompted to update? Or do you get the "in use by" message?
[2011/11/14@12:33:28.425-0500] P-15480 T-1 I Usr 11: (708) Userid is now EWRAPPER.
[2011/11/14@12:33:28.509-0500] P-15480 T-1 I Usr 11: (12699) Database ltrack Options:
[2011/11/14@12:33:35.061-0500] P-15498 T-1 I Usr 12: (452) Login by jamaly on /dev/pts/tc.
[2011/11/14@12:33:35.117-0500] P-15498 T-1 I Usr 12: (12699) Database ltrack Options:
[2011/11/14@12:33:36.982-0500] P-15498 T-1 I Usr 12: (708) Userid is now jamaly.
One of your sessions has logged in. Is it the one running the code above? Or something else?
dbadmin@hpdev:/u1$
***************************************************************************************
dbadmin@hpdev:/u1$cd /u/dlc101/bin
dbadmin@hpdev:/u/dlc101/bin$promon /u1/ltrack
OpenEdge MONITOR Release 10
Database: /u1/ltrack
1. User Control
2. Locking and Waiting Statistics
3. Block Access
4. Record Locking Table
5. Activity
6. Shared Resources
7. Database Status
8. Shut Down Database
R&D. Advanced options
T. 2PC Transactions Control
L. Resolve 2PC Limbo Transactions
C. 2PC Coordinator Information
J. Resolve JTA Transactions
M. Modify Defaults
Q. Quit
Enter your selection: 4
1. Display all entries
2. Match a user number
3. Match a range of user numbers
4. Match a record id
Q. Return to main menu
Enter your selection: 1
Record Locking Table:
Usr Name Chain # Rec-id Table Lock Flags Tran State Tran ID
12 jamaly REC 3608 15242337 15 EXCL Begin 124170
9637
RETURN - repeat, U - continue uninterrupted, Q - quit:
You are logged in and you have a lock.
Nobody else has a lock nor is anyone waiting on one so I'm not sure what you could be complaining about.
***********************************************************************************
Enter your selection: t
1. Display all entries
2. Match a user number
3. Match a range of user numbers
Q. Return to main menu
Enter your selection: 1
Transaction Control:
Usr Name Trans Login Time R-comm? Limbo? Crd? Coord Crd-task
12 jamaly 1241709637 11/14/11 12:33 no no no 0
RETURN - repeat, U - continue uninterrupted, Q - quit:
I'm not sure how I missed this before but... why are you looking in the Two-phase commit menu? Are you
using 2PC??? Do you have more than 1 database? The sample code only involves one table so 2PC doesn't apply.
**********************************************************************************
Enter your selection: 8
usr pid time of login user id tty Limbo?
5 15325 Mon Nov 14 12:33:20 2011 APW no
6 15330 Mon Nov 14 12:33:21 2011 APW no
7 15338 Mon Nov 14 12:33:23 2011 BIW no
8 15342 Mon Nov 14 12:33:25 2011 WDOG no
9 15384 Mon Nov 14 12:33:26 2011 BATCH batch no
10 15412 Mon Nov 14 12:33:27 2011 FINISHER batch no
11 15480 Mon Nov 14 12:33:28 2011 EWRAPPER batch no
12 15498 Mon Nov 14 12:33:35 2011 jamaly /dev/pts/tc no
1 Disconnect a User
2 Unconditional Shutdown
3 Emergency Shutdown (Kill All)
x Exit
Enter choice>
***********************************************************************************
Enter choice> 1
Enter the user number which is to be disconnected: 12
User 12 disconnect initiated. (6796)
usr pid time of login user id tty Limbo?
5 15325 Mon Nov 14 12:33:20 2011 APW no
6 15330 Mon Nov 14 12:33:21 2011 APW no
7 15338 Mon Nov 14 12:33:23 2011 BIW no
8 15342 Mon Nov 14 12:33:25 2011 WDOG no
9 15384 Mon Nov 14 12:33:26 2011 BATCH batch no
10 15412 Mon Nov 14 12:33:27 2011 FINISHER batch no
11 15480 Mon Nov 14 12:33:28 2011 EWRAPPER batch no
1 Disconnect a User
2 Unconditional Shutdown
3 Emergency Shutdown (Kill All)
x Exit
Enter choice>
*********************************************************************************
Before backuout from session of jamaly:
Before? That makes no sense. Above you disconnected the user. Below the .lg file shows that occurring.
Does "backout" mean something other than "disconnect"?
[2011/11/14@12:33:35.061-0500] P-15498 T-1 I Usr 12: (452) Login by jamaly on /dev/pts/tc.
[2011/11/14@12:33:35.117-0500] P-15498 T-1 I Usr 12: (12699) Database ltrack Options:
[2011/11/14@12:33:36.982-0500] P-15498 T-1 I Usr 12: (708) Userid is now jamaly.
[2011/11/14@12:41:08.437-0500] P-16037 T-1 I SHUT 13: (-----) User 12 disconnect initiated
[2011/11/14@12:41:08.437-0500] P-15317 T-1 I BROKER 0: (-----) Sending signal 7 to user 12
dbadmin@hpdev:/u1$
*********************************************************************************
No messages like this?
[2011/11/14@14:40:59.913-0500] P-24460 T--809879808 I ABL 12: (2252) Begin transaction backout.
[2011/11/14@14:40:59.913-0500] P-24460 T--809879808 I ABL 12: (2253) Transaction backout completed.
The lack of these messages is saying that something has gone horribly wrong.
Is Progress installed setuid to root?
Use this command to see:
ls -l $DLC/bin/_progres
Has process 15498 really died:
ps -ef | grep 15498
Record Locking Table:
Usr Name Chain # Rec-id Table Lock Flags Tran State Tran ID
12 jamaly REC 3608 15242337 15 EXCL Begin 124170
9637
RETURN - repeat, U - continue uninterrupted, Q - quit:
********************************************************************************
So this is a new jamaly login?
Transaction Control:
Usr Name Trans Login Time R-comm? Limbo? Crd? Coord Crd-task
12 jamaly 1241709637 11/14/11 12:33 no no no 0
RETURN - repeat, U - continue uninterrupted, Q - quit:
********************************************************************************
[now, in my session waiting for update of add1 I will backout (F4), only then the
lock is released and disconnect is completed]
What is the point of this F4 action?
*******************************************************************************
Transaction Control:
Usr Name Trans Login Time R-comm? Limbo? Crd? Coord Crd-task
RETURN - repeat, U - continue uninterrupted, Q - quit:
********************************************************************************
After F4 in the session of jamaly:
[2011/11/14@12:33:36.982-0500] P-15498 T-1 I Usr 12: (708) Userid is now jamaly.
[2011/11/14@12:41:08.437-0500] P-16037 T-1 I SHUT 13: (-----) User 12 disconnect initiated
[2011/11/14@12:41:08.437-0500] P-15317 T-1 I BROKER 0: (-----) Sending signal 7 to user 12
[2011/11/14@12:48:58.310-0500] P-15498 T-1 I Usr 12: (453) Logout by jamaly on /dev/pts/tc.
********************************************************************************
[Now, I'm going to repeat this test but instead of using my signon to update add1 of gen_loc, I'll use dbadmin].
[2011/11/14@12:49:29.466-0500] P-17218 T-1 I Usr 12: (452) Login by dbadmin on /dev/pts/tc.
[2011/11/14@12:49:29.520-0500] P-17218 T-1 I Usr 12: (12699) Database ltrack Options:
[2011/11/14@12:49:33.790-0500] P-17218 T-1 I Usr 12: (708) Userid is now dbadmin.
dbadmin@hpdev:/u1$
*******************************************************************************
Record Locking Table:
Usr Name Chain # Rec-id Table Lock Flags Tran State Tran ID
12 dbadmin REC 3608 15242337 15 EXCL Begin 124172
6328
RETURN - repeat, U - continue uninterrupted, Q - quit:
********************************************************************************
Enter your selection: 8
usr pid time of login user id tty Limbo?
5 15325 Mon Nov 14 12:33:20 2011 APW no
6 15330 Mon Nov 14 12:33:21 2011 APW no
7 15338 Mon Nov 14 12:33:23 2011 BIW no
8 15342 Mon Nov 14 12:33:25 2011 WDOG no
9 15384 Mon Nov 14 12:33:26 2011 BATCH batch no
10 15412 Mon Nov 14 12:33:27 2011 FINISHER batch no
11 15480 Mon Nov 14 12:33:28 2011 EWRAPPER batch no
12 17218 Mon Nov 14 12:49:29 2011 dbadmin /dev/pts/tc no
1 Disconnect a User
2 Unconditional Shutdown
3 Emergency Shutdown (Kill All)
x Exit
Enter choice> 1
Enter the user number which is to be disconnected: 12
User 12 disconnect initiated. (6796)
usr pid time of login user id tty Limbo?
5 15325 Mon Nov 14 12:33:20 2011 APW no
6 15330 Mon Nov 14 12:33:21 2011 APW no
7 15338 Mon Nov 14 12:33:23 2011 BIW no
8 15342 Mon Nov 14 12:33:25 2011 WDOG no
9 15384 Mon Nov 14 12:33:26 2011 BATCH batch no
10 15412 Mon Nov 14 12:33:27 2011 FINISHER batch no
11 15480 Mon Nov 14 12:33:28 2011 EWRAPPER batch no
1 Disconnect a User
2 Unconditional Shutdown
3 Emergency Shutdown (Kill All)
x Exit
Enter choice>
****************************************************************************
I do not have to backout (F4) for the connection to get terminated.
[2011/11/14@12:49:33.790-0500] P-17218 T-1 I Usr 12: (708) Userid is now dbadmin.
[2011/11/14@12:57:07.545-0500] P-17955 T-1 I SHUT 13: (-----) User 12 disconnect initiated
[2011/11/14@12:57:07.545-0500] P-15317 T-1 I BROKER 0: (-----) Sending signal 7 to user 12
[2011/11/14@12:57:07.546-0500] P-17218 T-1 I Usr 12: (2252) Begin transaction backout.
[2011/11/14@12:57:07.547-0500] P-17218 T-1 I Usr 12: (2253) Transaction backout completed.
[2011/11/14@12:57:07.549-0500] P-17218 T-1 I Usr 12: (453) Logout by dbadmin on /dev/pts/tc.
****************************************************************************
And in this case you have proper transaction backout.
My question is why when the user locking is dbadmin is behaves as expected and when
the user is NOT dbadmin it does not backout the transaction and logout?
I don't see any lock conflicts anywhere in any of this. In all of your examples there is only ever the one lock.
You are now describing a problem with "disconnect" terminating a user session. That is quite different from where this all started and it is still difficult to say what is really going on, there are still a lot of unclear procedures taking place and the timeline seems to be highly fluid.
My best wild guess, at the moment, is that you have setup permissions so that dbadmin can disconnect users but other users cannot. This may be because you have installed Progress without the executables being setuid to root --which prevents signals from being delivered properly. Or it might be something totally different.