Hi,

How do I get more information about the record associated with the Number
column in the ServerSessionLocks table? Or in other words, once I have the
row number, how do I locate the actual record in the table to see what data
that particular record contains?

I thought that I could use a range on a query to retrieve just that one
record, but since there is a primary key on the table it always orders by
that column which doesn't lead me to the actual locked record.  Is there a
way to select based upon the row number or a way to order the query results
by the raw data order in the table?

--

---------------------------------------
 Terry Swiers
 Millennium Software, Inc.
 http://www.1000years.com
 http://www.atrex.com

 The Atrex 13 beta is now available.
 Visit http://v13beta.atrex.com for more information.

Atrex Electronic Support Options:
 Atrex Knowledgebase: http://www.atrex.com/atrexkb.asp
 Email: mailto:support@atrex.com
 Newsgroup: news://news.1000years.com/millennium.atrex
 Fax: 1-925-829-1851
 Phone: 1-925-828-5892 (M-F, 9a-5p Pacific)
 ---------------------------------------

Terry,

<< How do I get more information about the record associated with the Number
column in the ServerSessionLocks table? Or in other words, once I have the
row number, how do I locate the actual record in the table to see what data
that particular record contains? >>

Unfortunately we don't surface that row information for public usage.
Perhaps you could give me an idea of what you're trying to do, and I could
offer an alternative suggestion ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

> Unfortunately we don't surface that row information for public usage.
> Perhaps you could give me an idea of what you're trying to do, and I could
> offer an alternative suggestion ?

We have a customer with a large number of users and every once in a while we
have a user who starts editing a record and then walks away from the system
for whatever reason.  So when another user attempts to get into the same
record they get locked out.

It's not unusual to have dozens of record locks on the same table so it
makes it a bit difficult to determine which lock is associated with which
record.  By being able to locate the actual record associated with the
record lock, we can track down which system has the specific record locked
and get them to either close the edit session or finish it up if they are
actually working on it.

It's nothing critical, just looking to be able to use the additional
information that we can get from the server session locks table.

--

---------------------------------------
 Terry Swiers
 Millennium Software, Inc.
 http://www.1000years.com
 http://www.atrex.com

 The Atrex 13 beta is now available.
 Visit http://v13beta.atrex.com for more information.

Atrex Electronic Support Options:
 Atrex Knowledgebase: http://www.atrex.com/atrexkb.asp
 Email: mailto:support@atrex.com
 Newsgroup: news://news.1000years.com/millennium.atrex
 Fax: 1-925-829-1851
 Phone: 1-925-828-5892 (M-F, 9a-5p Pacific)
 ---------------------------------------

Terry,

<< It's not unusual to have dozens of record locks on the same table so it
makes it a bit difficult to determine which lock is associated with which
record.  By being able to locate the actual record associated with the
record lock, we can track down which system has the specific record locked
and get them to either close the edit session or finish it up if they are
actually working on it. >>

Use this query:

SELECT *,
(SELECT Process FROM ServerSessions WHERE
ServerSessions.ID=ServerSessionLocks.SessionID) AS Process
FROM ServerSessionLocks

The Process column will contain the computer name+process ID for the
computer that has the row locked.  The computer name should get you right to
the culprit.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

> Use this query:
>
> SELECT *,
> (SELECT Process FROM ServerSessions WHERE
> ServerSessions.ID=ServerSessionLocks.SessionID) AS Process
> FROM ServerSessionLocks
>
> The Process column will contain the computer name+process ID for the
> computer that has the row locked.  The computer name should get you right
> to the culprit.

That I got.  But there may be 50 locks on the same table (yes this is
actually possible) on 20 different systems.  The problem is determining
which record lock corresponds to which record in the table.  In this
situation, we are talking about product orders.  So I need to be able to
determine which record lock corresponds to which product order in the table,
then I can determine which system has it locked and deal with it.  It's the
differentiation between the multiple locks that is the hard part.

It's not a big deal at this point, but it would be nice to be able to be
able to use the information in the table locks list and be able to determine
the underlying row information associated with the record lock.

--

---------------------------------------
 Terry Swiers
 Millennium Software, Inc.
 http://www.1000years.com
 http://www.atrex.com

 The Atrex 13 beta is now available.
 Visit http://v13beta.atrex.com for more information.

Atrex Electronic Support Options:
 Atrex Knowledgebase: http://www.atrex.com/atrexkb.asp
 Email: mailto:support@atrex.com
 Newsgroup: news://news.1000years.com/millennium.atrex
 Fax: 1-925-829-1851
 Phone: 1-925-828-5892 (M-F, 9a-5p Pacific)
 ---------------------------------------

Terry,

<< That I got.  But there may be 50 locks on the same table (yes this is
actually possible) on 20 different systems. >>

I'm obviously missing something.  What constitutes a "system" ?  Is it a
different machine, or are there multiple processes being run on one machine
?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

> I'm obviously missing something.  What constitutes a "system" ?  Is it a
> different machine, or are there multiple processes being run on one
> machine ?

I'm quite low on sleep so I'm most likely communicating very poorly.  It's
clear as rain in my head but gets very muddy by the time it gets to they
keyboard.

Large installation with a good number of users hitting our database working
on customer orders.  When each order is being worked on, we edit lock it in
order to prevent muliple users from editing the same record at the same time
using pessimistic locking and it is acceptable for them to have orders open
for extended periods of time because it can take a couple of hours to
process an order.  So at any moment, there could be upwards of 50 edit locks
on the order table.

User Bob opens up order 200 for editing, forgets to close it, and goes home
for the day.  User Frank on the second shift is told to finish up order 200
since Bob is now off the clock.  When Frank tries to edit order 200 it's
locked on one of the other computer systems, but they don't know which one
since they are scattered throughout the 4 physically separate warehouses
that this client has.  The uses are not assigned any particular computer to
work on, so Bobs edit lock could be on any of the computers.

So at this point I look at the session locks table, I can see that there are
numerous locks on the order table, the session/computer information, and the
row number.  But of the 50 or so locks on the order table, I have no clue
don't know which one of those locks corresponds to order 200. The User ID of
the session doesn't buy me anything because all of the users use a single
user ID to connect to the server.

The goal was to be able to retrieve the row number and then somehow be able
to retrieve the information from that physical row number to be able to
determine which lock is associated with that specific order.

As I indicated before, it's not a big issue so don't spend any time on it
unless others indicate that they would like those capabilties as well.  I
was just inquiring because my client asked if it was possible.


--

---------------------------------------
 Terry Swiers
 Millennium Software, Inc.
 http://www.1000years.com
 http://www.atrex.com

 The Atrex 13 beta is now available.
 Visit http://v13beta.atrex.com for more information.

Atrex Electronic Support Options:
 Atrex Knowledgebase: http://www.atrex.com/atrexkb.asp
 Email: mailto:support@atrex.com
 Newsgroup: news://news.1000years.com/millennium.atrex
 Fax: 1-925-829-1851
 Phone: 1-925-828-5892 (M-F, 9a-5p Pacific)
 ---------------------------------------

Terry,

<< When Frank tries to edit order 200 it's locked on one of the other
computer systems, but they don't know which one since they are scattered
throughout the 4 physically separate warehouses that this client has.  The
uses are not assigned any particular computer to work on, so Bobs edit lock
could be on any of the computers. >>

Yes, but if they are networked then they must have unique computer names,
therefore knowing the computer name will tell you exactly which computer has
the lock.

--
Tim Young
Elevate Software
www.elevatesoft.com