I have a big problem with DMI-Methods and I hope you can help me with this. I have this problem since a long time ago, but only now I am able to reproduce it better.
I am having (sometimes) blocked resources in my MSSQL Database when executing statements inside a DMI Method.
This is NOT ALWAYS happening, so sometimes I have to execute the same method multiple times until the DB blocks.
Basically, I am doing the following steps in my DMI Method:
1) delete all records from the current user from a table
2) insert some records to the table
3) read those records, and do something with them
This is my (simplified) DMIMethod:
So you see, I am first deleting all records for the current user, then I insert some records, and then I call "doUpdateAddresses". ALL ON THE SAME TABLE: "tmpAddressAenderungen"
This is the table getting blocked!!!!
In my "doUpdateAddresses", for testing, I only read all records one by one:
So I am basically only reading these records one by one, using a cursor. In my testing application I am not doing anything else with those records.
As I said, the table is getting blocked. Here is the blocking report:
So the blocked process is the process creating my cursor, and the blocking process is a select statement. But this blocking select statement is automatically created by smartGWT (after executing the INSERT statement!!!).
Of course, I could disable this fetch using canSyncCache=false, but since I have observed this blocking in several situations, I would like to correct this problem.
WHen digging into the blocked process, I see that the blocked statement is:
and, as I said, the blocking process is the fetch automatically created by SmartGWT after the insert.
So is there a bug here? I have observed this problem since more than a year, but I never was able to reproduce it, until now.
Using smartGWT 5.0p power 2015-02-27 with MSSQL Server 2014.
I am having (sometimes) blocked resources in my MSSQL Database when executing statements inside a DMI Method.
This is NOT ALWAYS happening, so sometimes I have to execute the same method multiple times until the DB blocks.
Basically, I am doing the following steps in my DMI Method:
1) delete all records from the current user from a table
2) insert some records to the table
3) read those records, and do something with them
This is my (simplified) DMIMethod:
Code:
RPCManager rpcManager = dsRequest.getRPCManager();
Map<String, Object> values = dsRequest.getValues();
List<Map<String, Object>> toUpdate = (List) values.get("toUpdate");
Long userId = dsRequest
.getHttpServletRequest().getSession().getAttribute("userId");
DSRequest deleteReq = new DSRequest(
"tmpAddressAenderungen",
DataSource.OP_REMOVE, rpcManager);
deleteReq.setAllowMultiUpdate(true);
deleteReq.setCriteria("f_user_id", userId);
deleteReq.execute();
for (Map<String, Object> m : toUpdate) {
DSRequest insertReq = new DSRequest(
"tmpAddressAenderungen",
DataSource.OP_ADD, rpcManager);
Map<String, Object> valuesToInsert = new HashMap<>();
valuesToInsert.put("f_type", Type.UPDATE.name());
valuesToInsert.put("f_field_f_id", m.get("f_id"));
valuesToInsert.put("f_field_f_strasse", m.get("f_strasse"));
valuesToInsert.put("f_field_f_plz", m.get("f_plz"));
valuesToInsert.put("f_field_f_ort", m.get("f_ort"));
valuesToInsert.put("f_field_f_ortsteil", m.get("f_ortsteil"));
valuesToInsert.put("f_field_f_land", m.get("f_land"));
insertReq.setValues(valuesToInsert);
insertReq.execute();
}
DSRequest editAddressesReq = new DSRequest(
"tmpAddressAenderungen",
DataSource.OP_FETCH, rpcManager);
editAddressesReq.setOperationId("doUpdateAddresses");
editAddressesReq.execute();
return new DSResponse();
This is the table getting blocked!!!!
In my "doUpdateAddresses", for testing, I only read all records one by one:
Code:
declare @c_f_field_f_id as integer, @c_f_field_f_strasse as varchar(250), @c_f_field_f_zusatz as varchar(250), @c_f_field_f_plz as varchar(250), @c_f_field_f_ort as varchar(250), @c_f_field_f_ortsteil as varchar(250), @c_f_field_f_land as varchar(2); declare c_cursor cursor for select f_field_f_id, f_field_f_strasse, f_field_f_zusatz, f_field_f_plz, f_field_f_ort, f_field_f_ortsteil, f_field_f_land from t_tmp_address_aenderungen where f_type = 'UPDATE' and f_mandant = @mandantId and f_user_id = @userId; open c_cursor FETCH NEXT FROM c_cursor INTO @c_f_field_f_id, @c_f_field_f_strasse, @c_f_field_f_zusatz, @c_f_field_f_plz, @c_f_field_f_ort, @c_f_field_f_ortsteil, @c_f_field_f_land; WHILE @@FETCH_STATUS = 0 begin update t_schueler set f_name = '123' where f_schueler_id = 1; FETCH NEXT FROM c_cursor INTO @c_f_field_f_id, @c_f_field_f_strasse, @c_f_field_f_zusatz, @c_f_field_f_plz, @c_f_field_f_ort, @c_f_field_f_ortsteil, @c_f_field_f_land; end close c_cursor; deallocate c_cursor;
As I said, the table is getting blocked. Here is the blocking report:
Code:
<blocked-process-report monitorLoop="86592">
<blocked-process>
<process id="process3e8692ca8" taskpriority="0" logused="0" waitresource="KEY: 5:72057594064732160 (becef0eb17cd)" waittime="7212" ownerId="945557" transactionname="FETCH CURSOR" lasttranstarted="2015-03-06T20:44:56.683" XDES="0x3d6b6ba20" lockMode="S" schedulerid="1" kpid="884" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2015-03-06T20:44:56.680" lastbatchcompleted="2015-03-06T20:44:51.693" lastattention="1900-01-01T00:00:00.693" clientapp="Microsoft JDBC Driver for SQL Server" hostname="vmd6271" hostpid="0" loginname="test" isolationlevel="read committed (2)" xactid="945557" currentdb="5" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame line="21" stmtstart="1800" stmtend="2152" sqlhandle="0x02000000e76ad902a5ee95e8bad6c48297e71a34087072a80000000000000000000000000000000000000000"/>
</executionStack>
<inputbuf>
set nocount on;
declare @mandantId as integer;
declare @userId as integer;
set @mandantId = 1;
set @userId = 47;
delete from t_tmp_map_local_remote_address where f_user_id = @userId and f_mandant = @mandantId;
----------------------------------------------------------------------
--updates
declare @c_f_field_f_id as integer, @c_f_field_f_strasse as varchar(250), @c_f_field_f_zusatz as varchar(250), @c_f_field_f_plz as varchar(250),
@c_f_field_f_ort as varchar(250), @c_f_field_f_ortsteil as varchar(250), @c_f_field_f_land as varchar(2);
declare c_cursor cursor for
select f_field_f_id, f_field_f_strasse, f_field_f_zusatz, f_field_f_plz, f_field_f_ort, f_field_f_ortsteil, f_field_f_land
from t_tmp_address_aenderungen
where f_type = 'UPDATE' and
f_mandant = @mandantId and f_user_id = @userId;
open c_cursor
FETCH NEXT FROM c_cursor
INTO @c_f_field_f_id, @c_f_field_f_strasse, @c_f_field_f_zusatz, @c_f_field_f_plz, @c_f_field_ </inputbuf>
</process>
</blocked-process>
<blocking-process>
<process status="sleeping" spid="54" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2015-03-06T20:44:56.677" lastbatchcompleted="2015-03-06T20:44:56.677" lastattention="1900-01-01T00:00:00.677" clientapp="Microsoft JDBC Driver for SQL Server" hostname="vmd6271" hostpid="0" loginname="test" isolationlevel="read committed (2)" xactid="945552" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack/>
<inputbuf>
SELECT t_tmp_address_aenderungen.f_id, t_tmp_address_aenderungen.f_mandant, t_tmp_address_aenderungen.f_type, t_tmp_address_aenderungen.f_field_f_id, t_tmp_address_aenderungen.f_field_f_strasse, t_tmp_address_aenderungen.f_field_f_zusatz, t_tmp_address_aenderungen.f_field_f_plz, t_tmp_address_aenderungen.f_field_f_ort, t_tmp_address_aenderungen.f_field_f_ortsteil, t_tmp_address_aenderungen.f_field_f_land, t_tmp_address_aenderungen.f_field_local_id, t_tmp_address_aenderungen.f_field_f_owner_id, t_tmp_address_aenderungen.f_field_f_adresse_id, t_tmp_address_aenderungen.f_field_f_owner, t_tmp_address_aenderungen.f_field_f_schueler_id, t_tmp_address_aenderungen.f_field_f_datum, t_tmp_address_aenderungen.f_field_f_old_address, t_tmp_address_aenderungen.f_field_f_new_address, t_tmp_address_aenderungen.f_field_f_typ, t_tmp_address_aenderungen.f_user_id FROM t_tmp_address_aenderungen WHERE (((t_tmp_address_aenderungen.f_id = 1004 AND t_tmp_address_aenderungen.f_id IS NOT NULL)) AND (t_tmp_address_aenderungen.f_mandan </inputbuf>
</process>
</blocking-process>
</blocked-process-report>
Of course, I could disable this fetch using canSyncCache=false, but since I have observed this blocking in several situations, I would like to correct this problem.
WHen digging into the blocked process, I see that the blocked statement is:
Code:
FETCH NEXT FROM c_cursor INTO @c_f_field_f_id, @c_f_field_f_strasse, @c_f_field_f_zusatz, @c_f_field_f_plz, @c_f_field_f_ort, @c_f_field_f_ortsteil, @c_f_field_f_land;
So is there a bug here? I have observed this problem since more than a year, but I never was able to reproduce it, until now.
Using smartGWT 5.0p power 2015-02-27 with MSSQL Server 2014.
Comment