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 2005 Forums
 Transact-SQL (2005)
 Update Select deadlock

Author  Topic 

Dinky
Starting Member

37 Posts
Posted - 2009-08-23 : 18:17:52
Deadlock for typical scenario in SQL Server 2005:

Select -> Update -> Select

What is the best way to avoid deadlock for above (details below)scenario:

Use RowLock hint for First Select
Use RowLock hint for Second Select
Avoid putting Table locks during updates on myTable
Any other way / suggestion

Also do we have query to get readable/formated locked information on abobe case (table invloved, lock level, session, login user)

----------------------
begin tran

select * from mytable where c1 = 'abc'

update mytable set c2 = 100
where c3 = 200 -- ONE OF THE RECORDS RETURNED BY THE ABOVE SQL

----

before the transaction commits, another process does this:

select * from mytable where c1 = 'abc'

and is blocked by the first transaction having a table lock on the entire mytable.

russell
Pyro-ma-ni-yak

5072 Posts
Posted - 2009-08-23 : 22:50:34
sounds like a test question...

the best way is not to use the tran and 2 SQL statements when a simple UPDATE (without the transaction) will do...using the same WHERE criteria as your SELECT.
Go to Top of Page

Dinky
Starting Member

37 Posts
Posted - 2009-08-24 : 00:19:17
It needs to be transaction in application:
Also the Last select is for different row (where clause)

begin tran

select * from mytable where c1 = 'abc'

update mytable set c2 = 100
where c3 = 200 -- ONE OF THE RECORDS RETURNED BY THE ABOVE SQL

----

before the transaction commits, another process/user/session does this:

select * from mytable where c1 = 'xyz'

Please advice...
Go to Top of Page

IncisiveOne
Starting Member

36 Posts
Posted - 2009-08-24 : 11:20:48
Yeah, you cannot avoid a transaction if the update depends on the selected values (and you do not want them to change within the xact).

First find out why you have the "whole table locked". Does the select get more than 200 rows ? That is the first thing to work out, transactions should be designed as the smallest possible unit of work ... both the update and the qualifying select.


-- since the update depends on "c3 = 200"
select * from mytable where c1 = "abc"

begin tran

select * from mytable where c3 = 200
update ...

Cheers


Anything worth doing, is worth doing right.
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts
Posted - 2009-08-24 : 13:49:08
I'd like to see the real code. In the example given, there is no need for the transaction, which by nature takes out an exclusive table lock during the update.

Unless you're returning random rows, then the criteria used to select a particular record can be used to update it without the select or the transaction.
Go to Top of Page

IncisiveOne
Starting Member

36 Posts
Posted - 2009-08-24 : 18:51:29
quote:
there is no need for the transaction, which by nature takes out an exclusive table lock during the update.

Last time I coded this on MS, it acquired exclusive page, not exclusive table locks; for the duration of the transaction, other users could select (or update) the table as long as they did not collide with the specific page locks. However, if the transaction was large (undesigned), and the page locks went into the hundreds (default 200), it would escalate to an exclusive table lock.

Anyway the "need for the transaction" may be by definition; that they do not want the selected rows being changed by others until the transaction is complete. It may not be a mere coding requirement.

Cheers


Anything worth doing, is worth doing right.
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts
Posted - 2009-08-24 : 23:34:33
The rows CANT be changed by others during an UPDATE statement.

If you're showing records to the end user and letting them choose what to update, then holding the lock until the user input occurs is guananteed to cause performance problems. There are other (better) ways to handle this type of concurrency issue.

Again...the example shown by the OP has no need for a transaction, or for two seperate queries. If they show us the actual code and explain the requirement, we may be able to help. BUT...from the example shown, the code is simply creating problems that don't need to exist and are easily resolved.
Go to Top of Page

IncisiveOne
Starting Member

36 Posts
Posted - 2009-08-25 : 08:19:30
Russel

In general, I agree, but you're losing me when you suggest avoiding a transaction for a transaction requirement (it is not for you or me to decide; avoiding a transaction, rather than implementing a good transaction design wil cause consequential problems), so I will drop it. Oh, and I did not state that rows can be changed by others during the update sttmt; I said OP did not want the select set (upon which the update is based) changing during the transaction. Quite a different point.

Dinky

Let's start at the top. I am assuming your transaction is:
-- user interaction; no transaction; selects, etc
begin tran
select mytable
update mytable
select mytable
...
commit tran
-- no user interaction during transaction

As posted:
- that should not hold table locks unless the result set is huge
(and it should not be; if it is, reduce the transaction to something manageable that does not hang up the server; removing transaction control is not the answer)

Now we have:
- the code is not the problem, the transaction design is the problem
- Yes, there are much better ways of (a) reducing locks and (b) avoiding locks while conducting user interaction. Optimistic Locking (a construct you have to implement) has been around for over 30 years.
- it eliminates (not reduces) deadlocks as well.

Over to you, Dinky.

Cheers



Anything worth doing, is worth doing right.
Go to Top of Page

cat_jesus
Aged Yak Warrior

547 Posts
Posted - 2009-08-25 : 08:26:27
quote:
Originally posted by IncisiveOne

Russel

In general, I agree, but you're losing me when you suggest avoiding a transaction for a transaction requirement (it is not for you or me to decide; avoiding a transaction, rather than implementing a good transaction design will cause consequential problems)


In my shop, I do decide. I'm lucky that way. :)

So what is the consequential problem you speak of?



An infinite universe is the ultimate cartesian product.
Go to Top of Page

IncisiveOne
Starting Member

36 Posts
Posted - 2009-08-25 : 20:43:18
Oh goody, I have a follower. Lucky me.

quote:
In my shop, I do decide.
for someone who evidently knows nothing about the consequences of avoiding transactions and transaction design, it must be a very small shop. Shop of One perhaps, no production.
quote:
So what is the consequential problem you speak of?
Let's see, where do I start...
- have you read the manuals, especially the bit re commit and rollback
- computer systems theory re resource contention
- online transaction processing
- locks and lock contention
- the admin guide re transaction log, and management thereof
- recovery and startup timeframes
- ACID Properties
- Optimistic Locking
- or maybe you are one of those innocent darlings who run "production" with truncate_log_on_checkpt set, or you have a masters degree in Large Transactions. A 50gb database and a 10gb transaction log.

Your question makes one wonder: do you have any actual experience with transactions in a multi-user production "database" ?

In your singular focus to attack me, you have missed the fact that OP is posting a question re one specific (one of the many) and common consequence of poor/absent transaction design. But you want the consequences listed. Hilarious.

quote:
In my shop, I do decide.
Let me guess, in your shop you have decided that zere vill be no consequences. Jawohl, mein commandant.

Please be a little understanding. This is is not just about single-user, non-production systems; this is not just a one-man website. In other shops, such as OP's, there are other users who have rights to use the system and the "database". Writing undesigned code and unconstrained transactions that lock up the system has consequences, including career consequences. Which may include having the rather visible consequences in the database/tran log that you were ignoring or actively denying, being pointed out to you in no uncertain terms; having to rewrite the code to acceptable standards; which may include education and design.

Some of us actually reverse the sequence: get unblinded first; get educated second; write code that does not break third; therefore never suffer the consequences of poor code/transactions or the consequences of that; never revisit/rewrite code, and move on to the next project. Others are forever writing "transactions" that block other users; hang up the log, and blaming the system or the "database", ordering more hardware, and rewriting code. Some people are just lucky.

Maybe you can post a new question, rather than hijacking Dinky's thread. After all, he is dealing with reality (facts, evidence, real world problem); you are evidently in denial of that exact said reality.

Anything worth doing, is worth doing right.
Go to Top of Page

cat_jesus
Aged Yak Warrior

547 Posts
Posted - 2009-08-25 : 20:55:06
So in other words, you don't know.

An infinite universe is the ultimate cartesian product.
Go to Top of Page

IncisiveOne
Starting Member

36 Posts
Posted - 2009-08-26 : 14:07:02
In other words, I can't be bothered to write, what has already been written by minds greater than mine, for people who won't read. The names such people call me, the boxes they pidgeon-hole me in, have as much effect as a blind man's movie reviews. Bait is for fish, dear follower.

Anything worth doing, is worth doing right.
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts
Posted - 2009-08-26 : 14:48:49
quote:
Originally posted by IncisiveOne

Yeah, you cannot avoid a transaction if the update depends on the selected values (and you do not want them to change within the xact).

This is simply not true. You can't make that generalization without knowing more about the app.

quote:
Originally posted by IncisiveOne

First find out why you have the "whole table locked". Does the select get more than 200 rows ?

200 is not some magic #. The answer to the question is irrelavant. "What does the execution plan look like?" would be a relavent question. "Are there exclusive locks on the indexes?" may be interesting to know...
quote:
Originally posted by IncisiveOne

I can't be bothered to write, what has already been written by minds greater than mine

Then why bother posting? You've offerred no help to the OP, or to the community in general, whatsoever.
Go to Top of Page

cat_jesus
Aged Yak Warrior

547 Posts
Posted - 2009-08-26 : 14:55:13
Come now Russell, He's a wizard who's too busy "symphonizing databases" to be bothered with the problems of mere mortals.

Please IncisiveOne, tell us how great you are. We will believe it, I promise.

An infinite universe is the ultimate cartesian product.
Go to Top of Page

IncisiveOne
Starting Member

36 Posts
Posted - 2009-08-27 : 11:13:39
Russel
quote:
You've offerred no help to the OP
That's for OP to decide, mate, not for the likes of you, and particularly not someone who is giving advice that is the opposite to mine in the thread. You have heard of concept of "bias", haven't you ?
quote:
or to the community in general
What, all 42 of you? With your 'attack-anyone-who-doesn't-genuflect-to-the-skill-level-at-this-site' mentality. Sheesh.
I suppose you don't (yet again) see the contradiction, so let me explain it for you. Since you guys are in attack mode, you guys are incapable of learning anything from me; it is a waste of time for me to attempt "contributions" here. In case you are not sure, just look at the one contribution I did make, and the attack that followed. You people are used to treating ohers badly, a law unto yourself, telling each other how good you are (nice virtual reality there); and attacking anyone who doesn't follow suit. Now, you may think, promise even, that no, that won't happen again. But I am not going to um contribute to the um community and find out; no, I trust that since nothing has changed, the result will be the same. Attack anything different, preserve the skill level of the um community, do not give us anything more than we can deal with. Puh-lease.

Remember, you are the same people who cannot make the distinction between such fundaments as joins (an expression of Facts resident in a Relational Database) and outer joins (a projection of non-facts; not-resident) ... but I am the bad guy, because I do not take the inordinate amount of time explaining the higher-order truths, while being tarred and feathered in the process. By definition, you cannot understand the in-depth issues, if you do not first understand the basics.

Bait is for fish, dear follower.

Anything worth doing, is worth doing right.
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts
Posted - 2009-08-27 : 11:38:45
IncisiveOne...you're just wrong.

The problem isn't that we're incapable of learning from you, the problem is that those who come here for help may actually believe some of the things you've said and make those mistakes in their own environments. And it isn't only for the OP to decide. When you're mistaken, someone who knows better should point it out.

And as for the join question, you've demonstrated that you really do not understand it. Why not let it rest?

There is a lot of good information here, perhaps you should type less and read more...then even you may learn something.

None of this was meant to be an attack on you. But when you make false statements, it is counterproductive to the purpose of this forum and forums like these.
Go to Top of Page

cat_jesus
Aged Yak Warrior

547 Posts
Posted - 2009-08-27 : 12:25:55
Dear IncisiveOne,
That was incredibly persuasive. I was wrong about you. You're obviously a superior database wizard and we're lucky to have you here.

Your humbled servant,
Cat

PS. please have the grace to explain to me what the consequences of not using transacations are so I can someday (hopefully, if I'm lucky and work hard and follow your great example) not be such a retard.





An infinite universe is the ultimate cartesian product.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2009-08-27 : 12:48:45
Well, getting back to answering the OPs question...


I'm not sure of the OP is asking about blocks or deadlocks, since he mentioned both in the post.

Using SNAPSHOT isolation should eliminate deadlocks and blocks in this example, since there would be no locks on the table from the select on connection #1, and the select on connection #2 would access the prior version of the row updated by connection #1.

Of course, you should read all about SNAPSHOT ISOLATION and row versioning in SQL Server 2005 Books Online to make sure you understand all the implications of using it.

SQL Server 2005 Books Online
Row Versioning-based Isolation Levels in the Database Engine
http://msdn.microsoft.com/en-us/library/ms177404(SQL.90).aspx







CODO ERGO SUM
Go to Top of Page

IncisiveOne
Starting Member

36 Posts
Posted - 2009-08-28 : 02:59:34
quote:
The problem isn't that we're incapable of learning from you, the problem is that those who come here for help may actually believe some of the things you've said and make those mistakes in their own environments.

Soooo ... let's get this straight. People have been following me for years; implementing my advice; giving me excellent feedback. But here, you know of others who do not post, but who have undesirable consequences to following my advice, without following me, and You know they are mistakes. Wow. Amazing. No evidence, but You just know it. Forget IT, you will make far more money providing psychic services.
quote:
And it isn't only for the OP to decide. When you're mistaken, someone who knows better should point it out.

Thanks so much, but I will wait for the actual poster with the actual problem to post actual experience/results. Generally I do not listen to the wondrous statements and psychic musings made by people with no qualifications; evidently [by virtue of the specific evidence at hand] have no experience; and who make no effort to provide evidence or background to their statements (same goes for me, ignore my statement re joins). No, I am the Bad Guy, because I do not believe the tooth fairy.

quote:
And as for the join question, you've demonstrated that you really do not understand it. Why not let it rest?

When ignorance is bliss, it is folly to be wise.
A wise man can play the fool, but a fool cannot play the wise man.
No amount of education will make a table out of a toadstool.
So forgive me for not trying.
quote:
There is a lot of good information here, perhaps you should type less and read more...then even you may learn something.

Wizards are not interested in your book of card tricks, you can stop marketing to people who do not need your wares. But, next time I need to tie my shoe laces, I know exactly where to come. Promise.
quote:
None of this was meant to be an attack on you.

Of course not, how can I take it personally. You would attack anyone who is above your level of mediocrity, because it is threatening to your virtual reality (telling yourselves how "good" you are; telling each other how "good" you are), why, you have your own dream company. Why would anyone not want this dream, this manufactured and protected virtual reality ?

Wizards, of course, they invent their own dreams, without help or propping up from anyone else. I get my acknowledgement from the real world, from production systems, from customers ... not from people who are scared of the truth, of the light.

quote:
But when you make false statements,

Have you tried it ? how, exactly do you know it is false ? So far, the evidence is, it is so scary, so threatening, that you deny it without any understanding or experience whatsoever.

quote:
Please IncisiveOne, tell us how great you are. We will believe it, I promise.
1. Sure, that is what you are used to doing with each other telling yourselves things about yourselves, in the hope that repetition will somehow, one day, make it real; but for an outsider, it is a trap, so that you can collectively laugh at them, and feel "better" than the very thing you are afraid of. All that is short-lived and illusory, you will need a fresh fix tomorrow. Sorry for not biting.
2. Words are silver, action is gold. I scorn silver.

quote:
please have the grace to explain to me what the consequences of not using transacations
Dear follower
1. go through the thread and read it again
2. follow the instructions I have already provided and read said documents
3. There is a facility called "Google" that gets information about such things, from outside your um reality. Use it.
4. If you still do not have specific answers to that problem, send me an email listing:
- the documents that you have read
- and specific (not general) questions about the bits that you do not understand, based on the bits that you have understood thus far.

The alternative is, in order to prove that you are no longer the dishonest and self-contradictory person that you have proven yourself to be in this thread (just read it again!), follow me around for one year without doing anything dishonest or insulting. At the end of the year, you can sit in on my next scheduled Advanced SQL for Dummies course, and read all my articles and postings, without the normal charge. If you get 80% or more on the course, you can read (not have) my server administration scripts (which manage the transaction log automatically).

This site is so small and inactive, that not even the original posters come back to read the responses, there are many threads responded to and waiting for the next step from the OPs, for weeks.

Nice place to visit, but I wouldn't dream of living here.

Anything worth doing, is worth doing right.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site