How to lock everything connected with transaction? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to lock everything connected with transaction?

Author

Topic

hornet
Yak Posting Veteran

96 Posts

Posted - 2006-07-27 : 09:15:16

Hi all!
I need to lock all tables which take part in a transaction.
Now i'm using next
SET TRANSACTION ISOLATIONLEVEL SERIALIZABLE

Are there any other means?

Thanks in advance!

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-27 : 09:21:57

What sort of lock do you want.
begin tran

select ...
from tbl with (tablockx, holdlock)
where

update tbl2
from tbl2 with (tablockx, holdlock)

commit tran

should take exclusive locks on them if that's what you want.

==========================================
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.

hornet
Yak Posting Veteran

96 Posts

Posted - 2006-07-27 : 09:37:25

Actually I have the working code but I have to optimize it, because
there are a lot of clients who are working with database but at the same time this transaction touches many tables.

Here is hack which I have to optimize:

begin tran
select * from tableA //here i'm getting info
.......... //something do
delete from tableA //here I need to be sure that data
//which I've recently got didn't change
commit tran

Kristen
Test

22859 Posts

Posted - 2006-07-27 : 09:53:23

"I need to lock all tables ..."

Do you really need to lock the whole table, or just the records invovled?

Kristen

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-07-27 : 09:59:18

quote:
Originally posted by hornet

Actually I have the working code but I have to optimize it, because
there are a lot of clients who are working with database but at the same time this transaction touches many tables.

Here is hack which I have to optimize:

begin tran
select * from tableA //here i'm getting info
.......... //something do
delete from tableA //here I need to be sure that data
//which I've recently got didn't change
commit tran

use the hints that nr suggests.

-ec

Kristen
Test

22859 Posts

Posted - 2006-07-27 : 10:09:20

+1 for hint.

Or store the full columns into a temporary table at the SELECT stage and then use a pessimistic WHERE clause to do the delete - and if the @@ROWCOUNT is wrong then raiserror() and roll the whole lot back.

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-27 : 11:01:58

I would suggest you think about another way of doing it - that will need knowledge of the requirement so we couldn't be much help with the info you have given.

==========================================
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.

hornet
Yak Posting Veteran

96 Posts

Posted - 2006-07-28 : 02:20:55

Thanks a lot everybody!
Perhars I'm mistaken but I came to a conclusion the best means is locking of records involved with a transaction only.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-28 : 05:11:56

Well if you don't have a transaction then you can't hold a lock.
Usually it is better to design so that locks are not held.

==========================================
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.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-28 : 05:16:11

another way is to
1. take the pk from tableA, save it on a temp or table variable
2. delete from tableA where pk in (select pk from temptable)

quote:
Originally posted by hornet

begin tran
select * from tableA //here i'm getting info
.......... //something do
delete from tableA //here I need to be sure that data
//which I've recently got didn't change
commit tran

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2006-07-28 : 06:25:55

Jen, that won't detect that nothing has changed in the rows in the meantime:

"delete from tableA // here I need to be sure that data which I've recently got didn't change"

Hence I suggested storing the full columns in the temptable and using pessimistic WHERE clause (or is that an optimistic WHERE clause, I can never remember!!)

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-28 : 10:32:16

what exactly is happening between the select and delete?

you may overcomplicating the case but sometimes it is always better to step back and look what is necessary without going overboard

you may think... something might happen to the records, but what? and how? do each user get to grab the records? if yes,then you can place a flag or token that will tell you that they're locked for processing, then release the flag or token if cancelled (delete, well you delete the records so nothing else to discuss there)

--------------------
keeping it simple...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-28 : 11:27:14

How about putting ROWVERSION columns in the tables involved, and using those to verify that nothing has changed since you selected the data? It's optimistic locking.

WHERE myPK = @myPK and myROWVERSION = @myROWVERSION

A ROWVERSION column changes every time a row is updated. If someone else updates the row between when you select the row and when you update it, it would have a new rowversion value. If you find a row has changed, you can take action like rollback.

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-07-28 : 11:32:25

"ROWVERSION"

Damm fine idea. Although the rows may get UPDATED without anything being changed ...

e.g.

UPDATE MyTable
SET MyFlag = 1

rather than

UPDATE MyTable
SET MyFlag = 1
WHERE MyFlag <> 1

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-28 : 11:46:23

quote:
Originally posted by Kristen

"ROWVERSION"

Damm fine idea. Although the rows may get UPDATED without anything being changed ...

e.g.

UPDATE MyTable
SET MyFlag = 1

rather than

UPDATE MyTable
SET MyFlag = 1
WHERE MyFlag <> 1

Kristen

You have to use other methods to solve the problem of stupid developers.

A smack in the head is a good start.

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-07-29 : 04:18:30

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2006-07-31 : 11:33:44

Depending on how bad the locking issue for you, you might be able to make use of sp_getapplock and sp_releaseapplock

-Lamprey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources