Devart Forums

Hello folks,

again I need your help. The UniTable.Post hangs under following circumstances:
- UniTable.SpecificOptions.FetchAll:= 'False'
- UniTable.LockMode:= lmNone
- UniTable.KeyField:= 'ID' // or your name of the key field
- UniTable.OrderFields:= 'AnyField' // any field name except the key field

Open a Table with more than 'FetchRows' records. Edit the first record. Change any field (of course not the key field and not necessarily the order field) and Post.

The SQL server management studio monitor says that the update statement is suspended because of the select statement. The problem does not occur, if the order field is empty or equals the key field.

How can I solve this? I need to use the fetch and order my records and make changes!

Thanks
Ralph

Hello,

This problem is due to the behavior of SQL Server when editing underfetched data. To solve it, you should decrease the LockTimeout option value:

Hi Alex,
Thank you very much for your reply. Unfortunately, your suggestion doesn't solve the problem! The update command is still blocked by the select command. Out of the process view of SSMS:

-- This is the update (post)
Process-ID: 57
Status: Suspended
Command: (@P1 int,@P2 int)UPDATE TEST SET Value = @P1 WHERE ID = @P2
Wait type: LCK_M_IX
Wait resource: pagelock fileid=1 pageid=2136 dbid=8 id=lock... mode=S
Blocked from ID: 55

-- This is the select (fetch)
Process-ID: 55
Status: RUNNING
Command: SELECT * FROM TEST ORDER BY IntField1
Wait type: ASYNC_NETWORK_IO
Wait resource: External ExternalResource=ASYNC_NETWORK_IO

No matter how I configure any timeouts, the update command stays suspended till I kill the select command (what kills the UniTable's fetch of course, but the update gets done).


Diving into the hole stuff I found that this might not be a bug. It seems to be more a "unintended feature" of UniDac. Because suspending the select query (FetchAll=False) the SQL-Server keeps all not-fetched rows locked. This is a typicall "read committed" behaviour of the SQL-Server.

I found, if LockMode=lmNone and FetchAll=False, the SQL-Server's lock mechanisms doesn't apply, if the select query is in a snapshot isolation mode (which I enabled for the test database). So my update does not cause a deadlock, if the UniTable's query is like
SET TRANSACTION ISOLATION LEVEL SNAPSHOT {crlf} SELECT * FROM TEST ORDER BY IntField1

I've tested "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED" too, but then I get errors like "Could not continue scan with NOLOCK due to data movement". Looked that up, anything with "dirty reading" what should be avoided...

For the purpose of modifying the TUniTable's select query I derived a component from TUniTable and overrode the method "GetFinalSQL":
function TmyUniTable.GetFinalSQL: String;
begin
Result:= inherited GetFinalSQL;
Result:= 'SET TRANSACTION ISOLATION LEVEL SNAPSHOT' + #13#10 + Result;
end;

That seems to work well, but again I have no clue, if I did the modification in the right place. So, if you have no further idea to keep the SQL-Server away from locking the rows, could you please tell me, what the best way to implement my stuff would be?

Thanks again
Ralph