creating deadlock using RR with autocommit off.

create 2 table like t1 and t2

create table t1 (col1 int, col2 varchar(15));

insert into t1 values(1,'india'),(2,'usa), (3,'russia');

create table t2 (col1 int, col2 varchar(15));

insert into t2 values(1,'india'),(2,'usa), (3,'russia');

now open 2 sessions:

do auto commit off in both the sessions.

session1:

issue: update t1 set col2='canada';

session2:

issue: update t2 set col2='canada';

session1:

issue: update t2 set col2='newyork';

session2:

issue: update t1 set col2='amazon';

dead lock will occur because session 1 holding x lock on t1 and waiting lock on t2 at the sametime session 2 holds the x lock on t2 and waiting for lock on t1 after locktimeout parameter time over session 1 will be rolledback and session 2 will be able to update. If locktimeout is -1 appication will not  be rolled back. we can check this statements using event monitors.