|
A couple of sample sessions that produce deadlocks. Each of the colors represents a different session or process.
| Deadlock with Insert using Two Tables |
|---|
bash-2.03$ isql -S myServer -D myDb -U myUser -P myPass
1> create table rogL ( v varchar(15) )
2> begin transaction
3> insert rogL values( 'locked' )
4> go
(1 row affected)
bash-2.03$ isql -S myServer -D myDb -U myUser -P myPass
1> create table rogR ( v varchar(15) )
2> begin transaction
3> insert rogR values( 'locked' )
4> go
(1 row affected)
1> insert rogR values( 'hanging' )
2> go
1> insert rogL values( 'deadlock' )
2> go
(1 row affected)
Msg 1205, Level 13, State 1:
Server 'myServer', Line 1:
Your server command (family id #0, process id #297) was deadlocked with another process and has been
chosen as deadlock victim. Re-run your command.
1>
1> drop table rogR
2> go
Msg 2762, Level 16, State 4:
Server 'myServer', Line 1:
The 'DROP TABLE' command is not allowed within a multi-statement transaction in the 'myDb' database.
1> rollback
2> go
1> drop table rogR
2> go
1> quit
bash-2.03$
1> drop table rogL
2> go
1> quit
bash-2.03$
|
| |
| Deadlock with Lock Table using Two Tables |
|---|
bash-2.03$ isql -S myServer -D myDb -U myUser -P myPass
1> create table rogL ( v varchar(15) )
2> begin transaction
3> lock table rogL in exclusive mode
4> go
bash-2.03$ isql -S myServer -D myDb -U myUser -P myPass
1> create table rogR ( v varchar(15) )
2> begin transaction
3> lock table rogR in exclusive mode
4> go
1> lock table rogR in exclusive mode
2> go
3> lock table rogL in exclusive mode
2> go
Msg 1205, Level 13, State 1:
Server 'myServer', Line 1:
Your server command (family id #0, process id #310) was deadlocked with another process and has been
chosen as deadlock victim. Re-run your command.
1>
1> drop table rogR
2> go
Msg 2762, Level 16, State 4:
Server 'myServer', Line 1:
The 'DROP TABLE' command is not allowed within a multi-statement transaction in the 'myDb' database.
1> rollback
2> go
1> drop table rogR
2> go
1> quit
bash-2.03$
1> drop table rogL
2> go
1> quit
bash-2.03$
|
| |
| Deadlock with a Single table |
|---|
bash-2.03$ isql -S myServer -D myDatabase -U myUser -P myPass
1> create table rog ( c char ) lock datarows
2> begin transaction
3> insert rog values ( 'a' )
4> go
(1 row affected)
bash-2.03$ isql -S myServer -D myDatabase -U myUser -P myPass
1> begin transaction
2> insert rog values ( 'b' )
3> go
(1 row affected)
1> update rog set c = 'B' where c = 'b' -- Hangs
2> go
1> update rog set c = 'A' where c = 'a' -- Deadlocks
2> go
(0 rows affected)
Msg 1205, Level 13, State 1:
Server 'myServer', Line 1:
Your server command (family id #0, process id #297) was deadlocked with another
process and has been chosen as deadlock victim. Re-run your command.
1> rollback
2> go
1> drop table rog
2> go
1> quit
bash-2.03$
1> quit
bash-2.03$
|
|
