make table unreadable - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

make table unreadable

Author

Topic

ChessMaster
Starting Member

5 Posts

Posted - 2007-09-19 : 07:51:41

Hi all, I have a problem. I need a query that blocks table and makes it unreadable. I decided to use WAITFOR to long blocking.


BEGIN TRAN myStopReadTrans 

USE MyDatabase
SELECT * From dbo.AB with(readpast,updlock)

WAITFOR DELAY '1:00:00'

COMMIT


USE MyDatabase
Select name from dbo.Clients

nr
SQLTeam MVY

12543 Posts

Posted - 2007-09-19 : 08:40:28

BEGIN TRAN myStopReadTrans

USE MyDatabase
SELECT * From dbo.AB with(holdlock,tablockx)

WAITFOR DELAY '1:00:00'

COMMIT

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ChessMaster
Starting Member

5 Posts

Posted - 2007-09-19 : 08:50:32

I started big query and after that the small one... and it works like no blocks was made by first query :(


BEGIN TRAN myStopReadTrans

USE MyDatabase
SELECT * From dbo.Clients with(holdlock,tablockx)

WAITFOR DELAY '1:00:00'

COMMIT


USE MyDatabase
Select name from dbo.Clients

nr
SQLTeam MVY

12543 Posts

Posted - 2007-09-19 : 08:55:04

What's the query on the other spid you are using to test?
Are you sure you ran the transaction?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ChessMaster
Starting Member

5 Posts

Posted - 2007-09-19 : 09:01:20

I opened two new queries and wrote text in eache one. Than pressed "play" in big query, went to smaller one and there pressed "play". And big has 55 spid, small - 51.

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-19 : 09:24:45

What sense does this make at all?

Why not DENY access?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

ChessMaster
Starting Member

5 Posts

Posted - 2007-09-19 : 10:34:35

I need to use QUERY WITH WAITFOR.

ChessMaster
Starting Member

5 Posts

Posted - 2007-09-21 : 06:52:49

Any ideas?

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-21 : 09:26:26

ideas? sure. my first idea is to rethink what you're doing because it makes no sense

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-21 : 12:37:32

I have to agree with Brett. Obviously, I never do this sort of thing. But, as an academic exercise I've only been successful at locking a table using an UPDATE statement. For example:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

UPDATE
	MyTable
SET	
	MyColumn = MyColumn

WAITFOR DELAY '1:00:00'

COMMIT TRANSACTION

And given that it is an update you shouldn't need to set the Isolation Level either. Perhaps there are other ways...? I've seen the stored procedure called sp_getapplock, but I do not know if it actually works as advertised.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-09-23 : 20:06:07

The query I gave will lock the table - I suspect there was a mistake in the OP's testing which caused him to think it wasn't.
Either the transaction was terminated or the other spid was doing dirty reads.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
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