Deadlock e_waitPipeGetRow

21

As I said before, this scenario is executed in explicit transaction.
A few selects are executed without explicit transaction before this scenario and one select is executed after this scenario.

  1. multiple selects without transaction
  2. select with rowlock and following update in the same transaction
  3. one select without transaction
22
  • please show schema of tables involved and the indices on these tables
  • please post the actual sequence of code performed where the issue happens. Java code.
  • is this a brand new application
  • if it is not brand new why did this just start now? was there some changes: schema, indices, data spike?

imho I think it is a combination of application locks and design issues.

1 Like
23

App was working for 2 years. People from customer's infrastructure team also say that there were no changes (no update or anything). Also this happens only in production environment. We were unable to simulate this in UAT env even with tripled number of actual production users.

Actual shema:

CREATE TABLE [dbo_auth].[datatable](
[id] varchar NOT NULL,
[public_key_a] varchar NULL,
[public_key_u] varchar NULL,
[pin_verification_key] varchar NULL,
[device_encryption_salt] varchar NULL,
[created_date] [datetime] NOT NULL,
[deactivated_date] [datetime] NULL,
[created_from_ip] varchar NOT NULL,
[deactivated_from_ip] varchar NULL,
[last_signed_date] [datetime] NULL,
[last_signed_from_ip] varchar NULL,
[name] varchar NULL,
[wrong_signature_counter] [int] NOT NULL,
[dedatatable_info] varchar NULL,
[os] varchar NULL,
[os_version] varchar NULL,
[app_version] varchar NULL,
[device_info] varchar NULL,
[dedatatable_message] varchar NULL,
[payload] varbinary NULL,
[device_id] varchar NULL,
[user_id] varchar NOT NULL,
CONSTRAINT [datatable_pk] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
) ON [PRIMARY]

The actual sequence is

...
dataTableRepository.findOne(dataTableId);
...
dataTableRepository.findOne(dataTableId);
restCall("/api/v1/verify", HttpMethod.POST, callHeaders, request, VerifyResponse.class);
dataTableRepository.findOne(dataTableId);
...
//here app works with other tables

While "restCall" is in progress, the other server executes the following sequence

...
DataTable data = dataTableRepository.findAndLockById(dataTableId);
data.setDedatatableInfo(info);
data.setDedatatableMessage(StringUtils.trimToNull(message));
data.setDeactivatedDate(new DateTime());
data.setDeactivatedFromIp(clientIp.getHostAddress());
dataTableRepository.saveAndFlush(data);
...

This repository is @Autowired where necessary

@Repository
public interface DataTableRepository extends JpaRepository<DataTable, String> {
@Lock(LockModeType.PESSIMISTIC_WRITE)
Activation findAndLockById(String id);
}

24

App was working for 2 years. People from customer's infrastructure team also say that there were no changes (no update or anything). Also this happens only in production environment. We were unable to simulate this in UAT env even with tripled number of actual production users.

Actual shema:

CREATE TABLE [dbo_auth].[datatable](
[id] varchar NOT NULL,
[public_key_a] varchar NULL,
[public_key_u] varchar NULL,
[pin_verification_key] varchar NULL,
[device_encryption_salt] varchar NULL,
[created_date] [datetime] NOT NULL,
[deactivated_date] [datetime] NULL,
[created_from_ip] varchar NOT NULL,
[deactivated_from_ip] varchar NULL,
[last_signed_date] [datetime] NULL,
[last_signed_from_ip] varchar NULL,
[name] varchar NULL,
[wrong_signature_counter] [int] NOT NULL,
[dedatatable_info] varchar NULL,
[os] varchar NULL,
[os_version] varchar NULL,
[app_version] varchar NULL,
[device_info] varchar NULL,
[dedatatable_message] varchar NULL,
[payload] varbinary NULL,
[device_id] varchar NULL,
[user_id] varchar NOT NULL,
CONSTRAINT [datatable_pk] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
) ON [PRIMARY]

The actual sequence is

...
dataTableRepository.findOne(dataTableId);
...
dataTableRepository.findOne(dataTableId);
restCall("/api/v1/verify", HttpMethod.POST, callHeaders, request, VerifyResponse.class);
dataTableRepository.findOne(dataTableId);
...
//here app works with other tables

While "restCall" is in progress, the other server executes the following sequence

...
DataTable data = dataTableRepository.findAndLockById(dataTableId);
data.setDedatatableInfo(info);
data.setDedatatableMessage(StringUtils.trimToNull(message));
data.setDeactivatedDate(new DateTime());
data.setDeactivatedFromIp(clientIp.getHostAddress());
dataTableRepository.saveAndFlush(data);
...

This repository is @Autowired where necessary

@Repository
public interface DataTableRepository extends JpaRepository<DataTable, String> {
@Lock(LockModeType.PESSIMISTIC_WRITE)
DataTable findAndLockById(String id);
}

Sorry for late response, meanwhile we had to push another project forward.

25

so, is this Oracle database or Microsoft SQL Server?

1 Like
26

It is MsSQL