i'm using Sybase SQL Anywhere 12. Let me say I have a Table user with the following connections to it:
conn_name conn_id user_id table_type creator table_name index_id lock_class lock_duration lock_type row_identifier
SQL_DBC_a2a1060 3193 DBA BASE DBA user (NULL) Row Transaction WriteNoPK 37431476262
SQL_DBC_a2a1060 3193 DBA BASE DBA user (NULL) Row Transaction Intent 45309427737
SQL_DBC_a2a1060 3193 DBA BASE DBA user (NULL) Row Transaction WriteNoPK 45309427737
SQL_DBC_a2a1060 3193 DBA BASE DBA user (NULL) Row Transaction Intent 37399035938
SQL_DBC_a2a1060 3193 DBA BASE DBA user (NULL) Row Transaction WriteNoPK 37399035938
SQL_DBC_a2a1060 3193 DBA BASE DBA user (NULL) Row Transaction Intent 37399035939
If I'm now going to SELECT * FROM user from within Sybase SQL Anywhere I'll get all rows and data as expected.
But if i'm going to fire the same SQL-Statement using the DBA-User in iSQL on OpenSuse 11.4, the Statement runs and I receive results till the row which is going to be selected has the WriteNoPK-Lockflag set. The Statement than states the follwoing error:
Server message number=8405 severity=21 state=0 line=0 text=SQL Anywhere Error -210: User 'XYZ' has the row in 'user' locked , SQL: 'SELECT * FROM user'
Is there a possibility to read/select even if the row is WriteNoPK-Locked?
Many thanks, Max
This is a result of the isolation level of your connection. The row is update locked, which prevents other processes from getting a possible 'dirty read' (e.g. reading an updated value that has not been committed, and could be rolled back)
To view your current isolation level:
SELECT CONNECTION_PROPERTY('isolation_level');
This section in the SQLAnywhere docs talks about the different isolation level settings, and how to change them. Isolation level 0, or 1 would probably allow your read to continue, but with some risk. You may also want to check out the snapshot isolation setting to see if it might suit your needs.