Anyone know what might cause a dbExpress TSQLQuery with a simple "select * from sometable where pkey = :pkey" statement to hold a row lock on the selected row until the TSQLQuery is closed?
Anyone know what might cause a dbExpress TSQLQuery with a simple "select * from sometable where pkey = :pkey" statement to hold a row lock on the selected row until the TSQLQuery is closed?
Normally I've see it hold a schema lock, but in this one instance I'm seeing it holding a row lock as well. I can't seem to figure out why my one query does this and not others though...
Normally I've see it hold a schema lock, but in this one instance I'm seeing it holding a row lock as well. I can't seem to figure out why my one query does this and not others though...
Any differences in indexes that contain pkey?
ReplyDeleteLars Fosdal Hmm, good question... I'll have to check.
ReplyDeleteLars Fosdal No difference that I can see. I have a table, and selecting based on column A, B or C does not cause a lock, but selecting on column D does. The indexes on all of them are similar: that column only, uniques allowed, nulls indistinct. Column defs are similar too (char(N) where N 10..50).
ReplyDeleteAha... Three issues at work:
ReplyDelete1. The query which locked had "top 1", the others did not. If i use anything larger than one, the row is not locked. If I remove the "top" the row isn't locked.
2. Even with the "top 1", if I drop the index on that column the row isn't locked.
3. It seems the insolation level on the connection is being reset at some point. DBX sets it to 1 (prevent dirty reads, allow phantom rows and non-repeatable reads) by default. If I set it to "snapshot-isolation" (which is what we should be using in our app) the row isn't locked, even with "top 1". We set the isolation level after connect, seems something resets it...
Tested on XE3 and XE6 with Sybase 16.
The devil invented SQL :)
ReplyDeleteWe get some real mysteries from time to time like the type: "Why does query X take zero time for item x, but 20 seconds for item Y?"