Deadlock Detection in database monitoring using Applicare

Oracle DB Monitoring

Deadlocks are checked for every 60 minutes by default. Interval can be changed using the below jvm argument.

-Dapplicare.deadlock.monitor.interval=IntervalValueInMinutes

e.g.

-Dapplicare.deadlock.monitor.interval=30

above setting would set deadlocks are checked every 30 minutes.

Deadlock Detection

The message will display the detail trace for the deadlock.

 

Few important Deadlock Details from the trace

This section specifies that this is an application issue, not an Oracle error. 

 

The section shows the blocked SQL statement in the session that detected the deadlock.

 

This section lists the blocked SQL statements in the other waiting sessions.

 

The SQL statements listed in the trace file should allow you to identify the application code that is causing the problem.

 

Please follow the below steps to create a deadlock in Oracle

Step 1: Login as a sysadmin in oracle. Create a test user and grant permissions.

CREATE USER test IDENTIFIED BY test
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;

GRANT CONNECT, CREATE TABLE TO test;
GRANT EXECUTE ON DBMS_LOCK TO test;

 

Step 2: Login into the created user and create a table and insert values.

CREATE TABLE deadlock_1 (
id NUMBER
);

CREATE TABLE deadlock_2 (
id NUMBER
);

INSERT INTO deadlock_1 (id) VALUES (1);
INSERT INTO deadlock_2 (id) VALUES (1);
COMMIT;

 

Step 3: Start two SQL*Plus sessions, each logged into the test user, then run the following pieces of code, one in each session simultaneously.

-- Run in session 1.
DECLARE
l_deadlock_1_id deadlock_1.id%TYPE;
l_deadlock_2_id deadlock_2.id%TYPE;
BEGIN
-- Lock row in first table.
SELECT id
INTO l_deadlock_1_id
FROM deadlock_1
WHERE id = 1
FOR UPDATE;

-- Pause.
DBMS_LOCK.sleep(30);

-- Lock row in second table.
SELECT id
INTO l_deadlock_2_id
FROM deadlock_2
WHERE id = 1
FOR UPDATE;

-- Release locks.
ROLLBACK;
END;
/

 

-- Run in session 2.
DECLARE
l_deadlock_1_id deadlock_1.id%TYPE;
l_deadlock_2_id deadlock_2.id%TYPE;
BEGIN
-- Lock row in second table.
SELECT id
INTO l_deadlock_2_id
FROM deadlock_2
WHERE id = 1
FOR UPDATE;

-- Pause.
DBMS_LOCK.sleep(30);

-- Lock row in first table.
SELECT id
INTO l_deadlock_1_id
FROM deadlock_1
WHERE id = 1
FOR UPDATE;

-- Release locks.
ROLLBACK;
END;
/

Running a query in session 1:

 

Running a query in session 2:

 

Deadlock occurred on session 1:

Note: To reproduce the deadlock once more please execute step 2.

Deadlock Detection in Applicare

 

MySQL DB Monitoring

Deadlock Detection

The message will display the detail trace for the deadlock.

This section specifies the deadlock transaction details.

 

 

Please follow the below steps to create a deadlock in Mysql

Step 1: Login into DBA user and create a test user and provide the below permissions.

CREATE USER 'testuser'@'%' IDENTIFIED BY 'root';
GRANT SELECT ON *.* TO 'testuser'@'%';
GRANT SHOW DATABASES ON *.* TO 'testuser'@'%';
GRANT SHOW VIEW ON *.* TO 'testuser'@'%';
GRANT CREATE ON *.* TO 'testuser'@'%';
GRANT INSERT ON *.* TO 'testuser'@'%';
GRANT DELETE ON *.* TO 'testuser'@'%';
GRANT DROP ON *.* TO 'testuser'@'%';

Step 2: Login into the created user and create a database.

CREATE DATABASE testdb;

Step 3: Start two mysql sessions, each logged into the test user, then run the following pieces of code one by one specified in the below order.

-- Run in session 1:

USE testdb;

CREATE TABLE t (i INT);

INSERT INTO t (i) VALUES(1);

START TRANSACTION;

SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;

-- Run in session 2:

USE testdb;

START TRANSACTION;

DELETE FROM t WHERE i = 1;

-- Run in session 1:

DELETE FROM t WHERE i = 1;

Deadlock occurred on session 2:

Step 4: Select and drop the created table

-- Run in session 1:

SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;

DROP TABLE t;

Note: To reproduce the deadlock once more please execute step 3 and 4.

Deadlock Detection in Applicare

 

MSSQL DB Monitoring

Deadlock Detection
The message will display the detail trace for the deadlock.

Deadlock Details from the trace

An XML deadlock graph has two main sections,

Processes section – details all the processes involved in the deadlock, what they were running, what isolation level they were in and more.

Resources section – lists all the resources that were involved in the deadlock, which locks each process acquired and which locks they requested.

 

Please follow the below steps to create a deadlock in MSSQL

Step 1: Create a new user with sql server authentication.

Syntax: CREATE LOGIN <new_user> WITH PASSWORD = '<password>';

Example: CREATE LOGIN test WITH PASSWORD = 'testpass';

Step 2: Create sample Database and tables.

CREATE DATABASE DeadlockDemo
GO
USE DeadlockDemo
GO
CREATE TABLE MyT1 (i INT)
GO
CREATE TABLE MyT2 (i INT)
GO
INSERT INTO MyT1
VALUES (1)
GO
INSERT INTO MyT2
VALUES (1)
GO

Step 3: Assign "sysadmin" role to the created user.

Step 4: Start two connections, each logged into the test user, then run the following pieces of code, one in each session simultaneously.

Query:

-- Run in session 1

use DeadlockDemo;
begin tran
update MyT1 set i= 3

 

-- Run in session 2

use DeadlockDemo;
begin tran
update MyT2 set i= 3

 

-- Run in session 1
Select * from MyT2

 

-- Run in session 2
Select * from MyT1

After running the above query deadlock will occur in any one of the session. Go to session 1 and 2 to see the deadlock message.

Here the deadlock occurred on session 1.

Now we need to run the "rollback" in non- deadlock session.
Since the deadlock was occurred on session 1, We need to run the "rollback" in session 2. 

-- Run in session 2

rollback;

Note: To reproduce the deadlock once more please execute step 4.

Deadlock Detection in Applicare