SQL Anywhere database - How to determine the related locked table-rows with the result from function sa

Author

How to determine the related locked table-rows with the result from function sa_locks ?

2005-08-23, 7:24 am

We use the function sa_locks to get all database-locks. This functions
returns the
connection_id, the user_id the lock_type and the lock_name (which contains a
number or null) of all current locks.

Our problem is, that we need to know which row in the table is locked.
The ASA-help says that the lock_name contains the row id, but we don't know
how
we can determine the related locked-row from the table with the given
row-id.
Is there any function which can get back the row-id of a given row ?

Can anybody help us on this ?

Thanks in Advance !

Jens

Breck Carter [TeamSybase]

2005-08-24, 9:23 am

AFAIK there is no way to determine which row is locked, and there is
no function that returns the row-id of a given row.

Breck

On 23 Aug 2005 03:22:58 -0700, "JN" <pjtaker@web.de> wrote:

>We use the function sa_locks to get all database-locks. This functions
>returns the
>connection_id, the user_id the lock_type and the lock_name (which contains a
>number or null) of all current locks.
>
>Our problem is, that we need to know which row in the table is locked.
>The ASA-help says that the lock_name contains the row id, but we don't know
>how
>we can determine the related locked-row from the table with the given
>row-id.
>Is there any function which can get back the row-id of a given row ?
>
>Can anybody help us on this ?
>
>Thanks in Advance !
>
>Jens
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

2005-08-24, 11:24 am

Hi Breck,

not nice to hear that. As a workaround we use now our own locking-table so
we can
determine which rows are locked ...

Anyway thanks for your answer !!!

Jens

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> schrieb im
Newsbeitrag news:8dsog1dk7fpcicj
0sit25n2malqgmiojg2@
4ax.com...
> AFAIK there is no way to determine which row is locked, and there is
> no function that returns the row-id of a given row.
>
>
> Breck
>
> On 23 Aug 2005 03:22:58 -0700, "JN" <pjtaker@web.de> wrote:
>
contains a[color=darkred]
know[color=darkred]
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

2005-08-25, 3:31 am

Hi Greg,

> Can you describe this?
> Are you planning on using this is a production system or just in
> development/testing?

We use pessimistic locking in our application. To achieve this we lock the
rows
before the user starts editing by calling "UPDATE Table SET Col1 = Col1
WHERE (Condition)".
The problem here is, that in our application-design it is possible to open
and edit the same
record several times by the same user. Since all our edit-windows use the
same connection
(session) to ASA the locks wont work for this, only for edit-calls on other
connections.
So for the same user our pessimistic-locking strategy dont work !

To prevent this, we need a way to check if a record is already locked by the
same user.
The function sa_locks shows all locks, but is not usable for us because we
can't identify
the related records.
To solve this we use a table to store all current locks (Columns: TableName,
RowID,
LockedBy, LockTimestamp > PK: TableName, RowID). If a user wants to edit a
record, we now insert a record in this table and if we get an insert-error
(PK not unique)
we know that this record is already locked and can show the user a message.
After the user saves or rejects the record, we simple delete the related
lock-record.
With this method we even don't need to lock the record with "UPDATE Table
SET
Col1 = Col1 WHERE (Condition)" anymore.
In the OnDisconnect-event from the application we delete all lock-records in
the
locking table from the current user to prevent locked-records after the
log-off.

Jens

2005-08-25, 3:31 am

Hi Greg,

answered on your other entry ...

Jens

"Greg Fenton" <greg. fenton_NOSPAM_@ianyw
here.com> schrieb im Newsbeitrag
news:430d357c@forums
-1-dub...
> JN wrote:
>
> I wonder why it is you feel that you need to know the particular row.
>
> Have you enabled the RememberLastStatemen
t option (or "-zl" on the
> engine start up) to see if the blocking connection's last statement
> might be the culprit?
>
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/

Sponsored Links