Please make my database deadlock

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Please make my database deadlock

Author

Topic

Jerry_Newlove
Starting Member

3 Posts

Posted - 2002-01-27 : 12:18:22

Hi,

My name is Jerry and I have now become unstuck making a database deadlock. Below is the schema of my database.

Branch (Branch_name, Branch_id, Location, Telephone)
Customer (Customer_name, Customer_id, Address, Telephone, Branch_id)
Current Account (Current_account_no, Customer_id, Balance)
Deposit Account (Deposit_account_no, Customer_id, Balance)
Loan Account (Loan_account_no, Customer_id, Amount, Date_of_loan)

I need to give a demonstration of this database suffering from a deadlock due to access by multiple users, and then create a locking strategy to overcome the specific lockup (maybe by selecting row lock, I'm not sure). I have an idea how to deadlock it by opening several windows in the Query Analyser and not commiting several transactions but still, your help could give me a better understanding of how this works.

Thanks a lot,

Jerry

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-27 : 14:08:32

Can you provide more information? It's extremely hard to figure out where the problem is without any detail on what's being done.

As far as creating deadlock, check out the WAITFOR command in Books Online (cause I don't know the exact syntax for it). This will let you set a delay for the batch to wait, and it should hold locks within a BEGIN TRANSACTION...ROLLBACK section. If you set the WAITFOR for something like 60-120 seconds, then run it multiple times, you might be able to create a deadlock situation.

WARNING: be EXTREMELY careful with WAITFOR, don't set it too high or your SQL Server might freeze up and require a reboot.

You might want to look into using TABLOCK, TABLOCKX and HOLDLOCK for your SQL statements; these should help create the deadlock.

As far as overcoming the situation; ROWLOCK should help, but what you really should do is minimize the size, number and duration of transactions. SELECT statements would benefit from the NOLOCK hint. You didn't mention how the users are interacting with the database (please let us know if you respond to this post), but if you are using ADO recordsets and using the .Update or .AddNew methods, don't. Create stored procedures for UPDATE, INSERT and DELETE operations and call them instead.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-27 : 16:44:07

Way to create deadlock

in one query window

begin tran
select * from tbla (tablockx)
waitfor delay, '00:00:10'
select * from tblb (tablockx)
rollback tran

in another window
begin tran
select * from tblb (tablockx)
waitfor delay, '00:00:10'
select * from tbla (tablockx)
rollback tran

If you run these at the same time (within 10 secs of each other) you should get a deadlock.

To get round any deadlock situation will depend on the instance. Usually you should design the database and access methods so that deadlock situations do not often occur - to find out why and solve them after a system is live is a nuissance.

In this case it would be by changing the order in which one of the queries takes it's locks (if the locks are necessary).

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources