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.
[Screenshot 1 is not available on this page.]
Few important Deadlock Details from the trace
This section specifies that this is an application issue, not an Oracle error.
[Screenshot 2 is not available on this page.]
The section shows the blocked SQL statement in the session that detected the deadlock.
[Screenshot 3 is not available on this page.]
This section lists the blocked SQL statements in the other waiting sessions.
[Screenshot 4 is not available on this page.]
The SQL statements listed in the trace file should allow you to identify the application code that is causing the problem.
[Screenshot 5 is not available on this page.]
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:
[Screenshot 6 is not available on this page.]
Running a query in session 2:
[Screenshot 7 is not available on this page.]
Deadlock occurred on session 1:
[Screenshot 8 is not available on this page.]
Note: To reproduce the deadlock once more please execute step 2.
Deadlock Detection in Applicare**
[Screenshot 9 is not available on this page.]
MySQL DB Monitoring
Deadlock Detection
The message will display the detail trace for the deadlock.
[Screenshot 10 is not available on this page.]
This section specifies the deadlock transaction details.
[Screenshot 11 is not available on this page.]
[Screenshot 12 is not available on this page.]
[Screenshot 13 is not available on this page.]
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:
[Screenshot 14 is not available on this page.]
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
[Screenshot 15 is not available on this page.]
MSSQL DB Monitoring
Deadlock Detection The message will display the detail trace for the deadlock.
[Screenshot 16 is not available on this page.]
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.
[Screenshot 17 is not available on this page.]
Resources section – lists all the resources that were involved in the deadlock, which locks each process acquired and which locks they requested.
[Screenshot 18 is not available on this page.]
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.
[Screenshot 19 is not available on this page.]
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
[Screenshot 20 is not available on this page.]
-- Run in session 2
use DeadlockDemo; begin tran update MyT2 set i= 3
[Screenshot 21 is not available on this page.]
-- Run in session 1 Select * from MyT2
[Screenshot 22 is not available on this page.]
-- Run in session 2 Select * from MyT1
[Screenshot 23 is not available on this page.]
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.
[Screenshot 24 is not available on this page.]
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;
[Screenshot 25 is not available on this page.]
Note: To reproduce the deadlock once more please execute step 4.
Deadlock Detection in Applicare**
[Screenshot 26 is not available on this page.]