KB Article Q201909 Re: Locking Behavior in Transactions Is Incorrect

KB Article Q201909 Re: Locking Behavior in Transactions Is Incorrect

Post by Morga » Sat, 23 Feb 2002 13:04:04

Machine specs:
MDAC 2.6




1. locking behavior in serial transactions

I am trying to serialize access to a table so that a SQL update
statement will either complete successfully, or if another user has the
row locked, fail immediately. I open a SQL Plus window and execute the
first statement "alter session set isolation_level=serializable". This
is supposed to cause DML statements to fail immdtly if the target row is
locked. Then I execute the update statement which updates one row. I
haven't committed this yet. Now I open another SQL Plus window and type
"alter session..."  followed by the same update statement. It hangs. Not
until I type rollback in the first window does the other statement
complete. If I commit in the first window, the second returns with error
"ORA-08177: can't serialize access for this transaction" According to
the messages reference this error means data was changed after the start
of a serial transaction. But how would that happen if the other
transaction started from a SQL Plus window that wasn't even opened until
after the update statement in the first one completed?

The startup parameter COMPATIBLE=7.3.2 is in the database initialization
file. This supposedly enables serial transactions. But it seems not to
be having an effect.

I don't care so much whether the update statement succeeds or fails, as
long as I can get it to return immdtly.

-R. Sood

2. Number of Server error logs

3. Posting of SqlServer KB article

4. First CFP: ADVIS 2002 Second Biennial International Conference on Advances in Information Systems

5. SMP & KB article Q254321

6. seeking Mark AuClair

7. INSTCAT.SQL; KB article Q104601

8. 13547-OR-Greater Eugene Area-SYBASE-ORACLE-Informix-Database architect-Ingrea-Data Architect

9. Question regarding KB article #320499

10. MS KB Article 298897 Not Available

11. kb article Q269587, how to fix for msde?

12. DTS KB Article, includes best practices

13. Server 7.0 Performance and Activity -- KB Article - 286191...ATTN MS