deadlock questions

Im able to produce a deadlock for learning purposes with BEGIN TRANSACTION
and no corresponding COMMIT. I do this using the textbook definition of a
deadlock as modeled in BOL. Easy enough. But what are the other scenarios
that can cause deadlocks that arent in that textbook definition?
Speifically, do UPDATES like these always need to be inside a transaction?
sql2k sp3
TIA, ChrisREvery DML statement creates an implicit transaction; this allows for
transactional atomicity. For instance, if an UPDATE effects 3 rows and only
one has been updated at the time of a server crash, the server will be able
to roll back the transaction upon restart. So you will always have a
transaction, whether or not you explicitly create one. You should create
transactions when you are doing more than one type of manipulation in a
batch, in which any failure within the batch would mean the whole thing was
invalid. For instance, if you were to insert an order into an orders table,
and then insert line items into a line items table, if one of the line items
were to fail, the entire order might not be in a consistent state.
Therefore, you would want the entire operation in a single transaction.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"ChrisR" <ChrisR@.NoEmails.com> wrote in message
news:%23xb1mVQwEHA.1264@.TK2MSFTNGP12.phx.gbl...
> Im able to produce a deadlock for learning purposes with BEGIN TRANSACTION
> and no corresponding COMMIT. I do this using the textbook definition of a
> deadlock as modeled in BOL. Easy enough. But what are the other scenarios
> that can cause deadlocks that arent in that textbook definition?
> Speifically, do UPDATES like these always need to be inside a transaction?
> --
> sql2k sp3
> TIA, ChrisR
>|||Chris,
Deadlocks occur when two procedures are trying to work with each other's
locked resources. In these situations, a deadlock victim is chosen and then
given an error. Their resources are then released so that the winner of the
deadlock can continue processing.
Transactions are not the only way to have a deadlock occur, but they are
more likely to occur during a transaction because transactions will maintain
locks until the transaction completes (COMMIT or ROLLBACK).
Large queries that are not transacted could possibly create a deadlock issue
as well. For example, if you are running a very large update on a table,
locking will begin and progress as follows:
1. Acquire row locks.
2. If enough row locks are acquired, then acquire page locks
3. If enough page locks are acquired, then acquire extent locks
4. If enough extent locks are acquired, then acquire a table lock.
This is known as lock escalation. So a large update that hits most rows in
a large table may eventually acquire a table level lock. If another data
modification is running on the same table and has some row locks or extent
locks and it is also attempting to acquire a table level lock, then one of
the two process will likely end as a deadlock victim.
To avoid these types of situations, it is best to write your procedures to
minimize the time a transaction runs. You should also try to use resources
(tables, views etc.) within your transactions and procedures in the same
order. If table A is already locked by a procedure, then a second procedure
must wait to acquire locks. This means that the second procedure is waiting
rather than becoming a potential deadlock victim.
Other options you may look at with large table updates is applying a query
hint that acquires a table lock right out of the gate rather than waiting
for lock escalation to take place.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <ChrisR@.NoEmails.com> wrote in message
news:%23xb1mVQwEHA.1264@.TK2MSFTNGP12.phx.gbl...
> Im able to produce a deadlock for learning purposes with BEGIN TRANSACTION
> and no corresponding COMMIT. I do this using the textbook definition of a
> deadlock as modeled in BOL. Easy enough. But what are the other scenarios
> that can cause deadlocks that arent in that textbook definition?
> Speifically, do UPDATES like these always need to be inside a transaction?
> --
> sql2k sp3
> TIA, ChrisR
>|||So then a deadlock occur with using the BEGIN TRANS?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uM8XsfQwEHA.2624@.TK2MSFTNGP11.phx.gbl...
> Every DML statement creates an implicit transaction; this allows for
> transactional atomicity. For instance, if an UPDATE effects 3 rows and
only
> one has been updated at the time of a server crash, the server will be
able
> to roll back the transaction upon restart. So you will always have a
> transaction, whether or not you explicitly create one. You should create
> transactions when you are doing more than one type of manipulation in a
> batch, in which any failure within the batch would mean the whole thing
was
> invalid. For instance, if you were to insert an order into an orders
table,
> and then insert line items into a line items table, if one of the line
items
> were to fail, the entire order might not be in a consistent state.
> Therefore, you would want the entire operation in a single transaction.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "ChrisR" <ChrisR@.NoEmails.com> wrote in message
> news:%23xb1mVQwEHA.1264@.TK2MSFTNGP12.phx.gbl...
TRANSACTION[vbcol=seagreen]
a[vbcol=seagreen]
scenarios[vbcol=seagreen]
transaction?[vbcol=seagreen]
>|||"ChrisR" <ChrisR@.NoEmails.com> wrote in message
news:erQLnqQwEHA.3528@.tk2msftngp13.phx.gbl...
> So then a deadlock occur with using the BEGIN TRANS?
Not necessarily.
The simplest answer (which of course is also the most complex) is keep your
transactions as short as possible.

>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uM8XsfQwEHA.2624@.TK2MSFTNGP11.phx.gbl...
> only
> able
create[vbcol=seagreen]
> was
> table,
> items
> TRANSACTION
of[vbcol=seagreen]
> a
> scenarios
> transaction?
>sql

deadlock questions

Im able to produce a deadlock for learning purposes with BEGIN TRANSACTION
and no corresponding COMMIT. I do this using the textbook definition of a
deadlock as modeled in BOL. Easy enough. But what are the other scenarios
that can cause deadlocks that arent in that textbook definition?
Speifically, do UPDATES like these always need to be inside a transaction?
sql2k sp3
TIA, ChrisR
Every DML statement creates an implicit transaction; this allows for
transactional atomicity. For instance, if an UPDATE effects 3 rows and only
one has been updated at the time of a server crash, the server will be able
to roll back the transaction upon restart. So you will always have a
transaction, whether or not you explicitly create one. You should create
transactions when you are doing more than one type of manipulation in a
batch, in which any failure within the batch would mean the whole thing was
invalid. For instance, if you were to insert an order into an orders table,
and then insert line items into a line items table, if one of the line items
were to fail, the entire order might not be in a consistent state.
Therefore, you would want the entire operation in a single transaction.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"ChrisR" <ChrisR@.NoEmails.com> wrote in message
news:%23xb1mVQwEHA.1264@.TK2MSFTNGP12.phx.gbl...
> Im able to produce a deadlock for learning purposes with BEGIN TRANSACTION
> and no corresponding COMMIT. I do this using the textbook definition of a
> deadlock as modeled in BOL. Easy enough. But what are the other scenarios
> that can cause deadlocks that arent in that textbook definition?
> Speifically, do UPDATES like these always need to be inside a transaction?
> --
> sql2k sp3
> TIA, ChrisR
>
|||Chris,
Deadlocks occur when two procedures are trying to work with each other's
locked resources. In these situations, a deadlock victim is chosen and then
given an error. Their resources are then released so that the winner of the
deadlock can continue processing.
Transactions are not the only way to have a deadlock occur, but they are
more likely to occur during a transaction because transactions will maintain
locks until the transaction completes (COMMIT or ROLLBACK).
Large queries that are not transacted could possibly create a deadlock issue
as well. For example, if you are running a very large update on a table,
locking will begin and progress as follows:
1. Acquire row locks.
2. If enough row locks are acquired, then acquire page locks
3. If enough page locks are acquired, then acquire extent locks
4. If enough extent locks are acquired, then acquire a table lock.
This is known as lock escalation. So a large update that hits most rows in
a large table may eventually acquire a table level lock. If another data
modification is running on the same table and has some row locks or extent
locks and it is also attempting to acquire a table level lock, then one of
the two process will likely end as a deadlock victim.
To avoid these types of situations, it is best to write your procedures to
minimize the time a transaction runs. You should also try to use resources
(tables, views etc.) within your transactions and procedures in the same
order. If table A is already locked by a procedure, then a second procedure
must wait to acquire locks. This means that the second procedure is waiting
rather than becoming a potential deadlock victim.
Other options you may look at with large table updates is applying a query
hint that acquires a table lock right out of the gate rather than waiting
for lock escalation to take place.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <ChrisR@.NoEmails.com> wrote in message
news:%23xb1mVQwEHA.1264@.TK2MSFTNGP12.phx.gbl...
> Im able to produce a deadlock for learning purposes with BEGIN TRANSACTION
> and no corresponding COMMIT. I do this using the textbook definition of a
> deadlock as modeled in BOL. Easy enough. But what are the other scenarios
> that can cause deadlocks that arent in that textbook definition?
> Speifically, do UPDATES like these always need to be inside a transaction?
> --
> sql2k sp3
> TIA, ChrisR
>
|||So then a deadlock occur with using the BEGIN TRANS?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uM8XsfQwEHA.2624@.TK2MSFTNGP11.phx.gbl...
> Every DML statement creates an implicit transaction; this allows for
> transactional atomicity. For instance, if an UPDATE effects 3 rows and
only
> one has been updated at the time of a server crash, the server will be
able
> to roll back the transaction upon restart. So you will always have a
> transaction, whether or not you explicitly create one. You should create
> transactions when you are doing more than one type of manipulation in a
> batch, in which any failure within the batch would mean the whole thing
was
> invalid. For instance, if you were to insert an order into an orders
table,
> and then insert line items into a line items table, if one of the line
items[vbcol=seagreen]
> were to fail, the entire order might not be in a consistent state.
> Therefore, you would want the entire operation in a single transaction.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "ChrisR" <ChrisR@.NoEmails.com> wrote in message
> news:%23xb1mVQwEHA.1264@.TK2MSFTNGP12.phx.gbl...
TRANSACTION[vbcol=seagreen]
a[vbcol=seagreen]
scenarios[vbcol=seagreen]
transaction?
>
|||"ChrisR" <ChrisR@.NoEmails.com> wrote in message
news:erQLnqQwEHA.3528@.tk2msftngp13.phx.gbl...
> So then a deadlock occur with using the BEGIN TRANS?
Not necessarily.
The simplest answer (which of course is also the most complex) is keep your
transactions as short as possible.
[vbcol=seagreen]
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uM8XsfQwEHA.2624@.TK2MSFTNGP11.phx.gbl...
> only
> able
create[vbcol=seagreen]
> was
> table,
> items
> TRANSACTION
of
> a
> scenarios
> transaction?
>

deadlock questions

Im able to produce a deadlock for learning purposes with BEGIN TRANSACTION
and no corresponding COMMIT. I do this using the textbook definition of a
deadlock as modeled in BOL. Easy enough. But what are the other scenarios
that can cause deadlocks that arent in that textbook definition?
Speifically, do UPDATES like these always need to be inside a transaction?
--
sql2k sp3
TIA, ChrisREvery DML statement creates an implicit transaction; this allows for
transactional atomicity. For instance, if an UPDATE effects 3 rows and only
one has been updated at the time of a server crash, the server will be able
to roll back the transaction upon restart. So you will always have a
transaction, whether or not you explicitly create one. You should create
transactions when you are doing more than one type of manipulation in a
batch, in which any failure within the batch would mean the whole thing was
invalid. For instance, if you were to insert an order into an orders table,
and then insert line items into a line items table, if one of the line items
were to fail, the entire order might not be in a consistent state.
Therefore, you would want the entire operation in a single transaction.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"ChrisR" <ChrisR@.NoEmails.com> wrote in message
news:%23xb1mVQwEHA.1264@.TK2MSFTNGP12.phx.gbl...
> Im able to produce a deadlock for learning purposes with BEGIN TRANSACTION
> and no corresponding COMMIT. I do this using the textbook definition of a
> deadlock as modeled in BOL. Easy enough. But what are the other scenarios
> that can cause deadlocks that arent in that textbook definition?
> Speifically, do UPDATES like these always need to be inside a transaction?
> --
> sql2k sp3
> TIA, ChrisR
>|||Chris,
Deadlocks occur when two procedures are trying to work with each other's
locked resources. In these situations, a deadlock victim is chosen and then
given an error. Their resources are then released so that the winner of the
deadlock can continue processing.
Transactions are not the only way to have a deadlock occur, but they are
more likely to occur during a transaction because transactions will maintain
locks until the transaction completes (COMMIT or ROLLBACK).
Large queries that are not transacted could possibly create a deadlock issue
as well. For example, if you are running a very large update on a table,
locking will begin and progress as follows:
1. Acquire row locks.
2. If enough row locks are acquired, then acquire page locks
3. If enough page locks are acquired, then acquire extent locks
4. If enough extent locks are acquired, then acquire a table lock.
This is known as lock escalation. So a large update that hits most rows in
a large table may eventually acquire a table level lock. If another data
modification is running on the same table and has some row locks or extent
locks and it is also attempting to acquire a table level lock, then one of
the two process will likely end as a deadlock victim.
To avoid these types of situations, it is best to write your procedures to
minimize the time a transaction runs. You should also try to use resources
(tables, views etc.) within your transactions and procedures in the same
order. If table A is already locked by a procedure, then a second procedure
must wait to acquire locks. This means that the second procedure is waiting
rather than becoming a potential deadlock victim.
Other options you may look at with large table updates is applying a query
hint that acquires a table lock right out of the gate rather than waiting
for lock escalation to take place.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <ChrisR@.NoEmails.com> wrote in message
news:%23xb1mVQwEHA.1264@.TK2MSFTNGP12.phx.gbl...
> Im able to produce a deadlock for learning purposes with BEGIN TRANSACTION
> and no corresponding COMMIT. I do this using the textbook definition of a
> deadlock as modeled in BOL. Easy enough. But what are the other scenarios
> that can cause deadlocks that arent in that textbook definition?
> Speifically, do UPDATES like these always need to be inside a transaction?
> --
> sql2k sp3
> TIA, ChrisR
>|||So then a deadlock occur with using the BEGIN TRANS?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uM8XsfQwEHA.2624@.TK2MSFTNGP11.phx.gbl...
> Every DML statement creates an implicit transaction; this allows for
> transactional atomicity. For instance, if an UPDATE effects 3 rows and
only
> one has been updated at the time of a server crash, the server will be
able
> to roll back the transaction upon restart. So you will always have a
> transaction, whether or not you explicitly create one. You should create
> transactions when you are doing more than one type of manipulation in a
> batch, in which any failure within the batch would mean the whole thing
was
> invalid. For instance, if you were to insert an order into an orders
table,
> and then insert line items into a line items table, if one of the line
items
> were to fail, the entire order might not be in a consistent state.
> Therefore, you would want the entire operation in a single transaction.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "ChrisR" <ChrisR@.NoEmails.com> wrote in message
> news:%23xb1mVQwEHA.1264@.TK2MSFTNGP12.phx.gbl...
> > Im able to produce a deadlock for learning purposes with BEGIN
TRANSACTION
> > and no corresponding COMMIT. I do this using the textbook definition of
a
> > deadlock as modeled in BOL. Easy enough. But what are the other
scenarios
> > that can cause deadlocks that arent in that textbook definition?
> > Speifically, do UPDATES like these always need to be inside a
transaction?
> >
> > --
> > sql2k sp3
> >
> > TIA, ChrisR
> >
> >
>|||"ChrisR" <ChrisR@.NoEmails.com> wrote in message
news:erQLnqQwEHA.3528@.tk2msftngp13.phx.gbl...
> So then a deadlock occur with using the BEGIN TRANS?
Not necessarily.
The simplest answer (which of course is also the most complex) is keep your
transactions as short as possible.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uM8XsfQwEHA.2624@.TK2MSFTNGP11.phx.gbl...
> > Every DML statement creates an implicit transaction; this allows for
> > transactional atomicity. For instance, if an UPDATE effects 3 rows and
> only
> > one has been updated at the time of a server crash, the server will be
> able
> > to roll back the transaction upon restart. So you will always have a
> > transaction, whether or not you explicitly create one. You should
create
> > transactions when you are doing more than one type of manipulation in a
> > batch, in which any failure within the batch would mean the whole thing
> was
> > invalid. For instance, if you were to insert an order into an orders
> table,
> > and then insert line items into a line items table, if one of the line
> items
> > were to fail, the entire order might not be in a consistent state.
> > Therefore, you would want the entire operation in a single transaction.
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "ChrisR" <ChrisR@.NoEmails.com> wrote in message
> > news:%23xb1mVQwEHA.1264@.TK2MSFTNGP12.phx.gbl...
> > > Im able to produce a deadlock for learning purposes with BEGIN
> TRANSACTION
> > > and no corresponding COMMIT. I do this using the textbook definition
of
> a
> > > deadlock as modeled in BOL. Easy enough. But what are the other
> scenarios
> > > that can cause deadlocks that arent in that textbook definition?
> > > Speifically, do UPDATES like these always need to be inside a
> transaction?
> > >
> > > --
> > > sql2k sp3
> > >
> > > TIA, ChrisR
> > >
> > >
> >
> >
>

DB-library network communications layer not loaded

Hi guys. I'm a pleb in a POS company and I'm learning SQL server on the fly. I'm sure that'll sound familiar.
I've got one client's having problems with our system. We have a program that runs at night that performs database maintenance tasks. This program has worked in all our other clients sites except this one.
At first I thought it might be a missing "sa" user due to the following error:

RUNTIME ERROR "-2147203048 (80044818): [Microsoft][ODBC SQL
Server driver][SQL Server]Login failed for user 'sa'

I've spoken to the programmers and they have no idea as to why:
1. The user isn't there
2. A quick way of fixing.
The best suggestion that they've come up with is to install Enterprise manager. Something is better then nothing I guess.
Anyway, in the mean time, I've contacted a former associate of the company and asked him to ask error. He then came back with some helpful stuff, one of which jogged my memory to just run a quick test of the 'sa' user to see if it's there. So I run just a simple command line query (isql -U sa, no password) which returned the following error:

DB-Library: Db-library network communications layer not loaded.

This leads me to believe that 'sa' is in fact there. The thing that further compounds this belief is that the main POS can logon through that driver except this particular database maintenance application.

I'd appreciate any help you've got. Please bear with me if this is posted on the wrong forum. Thanks.

Kind regards,

ka Kwok
Customer Service OperatorRE:
... a simple command line query (isql -U sa, no password) which returned...
Hopefully the issue is simply that the sa account HAS a password other than nothing (if not it would be wise to consider immediately issuing the sa account a strong password).
Q1 I'd appreciate any help you've got. Please bear with me if this is posted on the wrong forum. Thanks. Kind regards, ka Kwok Customer Service Operator

A1 Some things to consider and / or check may include:

Create and try another TestConnection account with a known (strong) password. What type of connections does the server support, and has it been verified that the Sql Service is in fact always running? Is Sql Server security configured for the connection type being attempted (standard Sql Server login, in the case of sa)? Is it possible to connect using Query Analyzer and a TestConnection account locally (if not, verify that the correct protocol, ports, etc., is being accessed)? Check and / or update MDAC levels as necessary / appropriate.

{More detailed information (including the Sql Server, MDAC, etc. versions involved, etc.), should make it easier for others to provide more useful / helpful feedback.}|||Thanks for your response. I've been thinking about this issue a fair bit.
I want to provide more information but given my position, I'm having problem trying to source that information. Currently, I'm not even been given a lot of help or support from any one with in the company to resolve this situation. However I'm pretty adamant on fixing this problem since it's an important client (if anything politcal happens I'll just treat it as the trigger).
Anyway, I'm starting to think that it might be a corrupted MDAC library. The server that's running our POS is serviced by another company and I'm starting to suspect something happened that no one's telling me stuff about.
I'm going to try a lot of what suggested with the other posts (on related matter) and see if that helps.
I'm still pretty much learning MS SQL on the fly.
I've bought a book on it but I'd value some better sources. Would you happen to have some sort good links or a list of good references.
I appreciated your help.
Thanks again.

Kind regards,

Broomer|||RE:
Thanks for your response. I've been thinking about this issue a fair bit.
I want to provide more information but given my position, I'm having problem trying to source that information. Currently, I'm not even been given a lot of help or support from any one with in the company to resolve this situation. However I'm pretty adamant on fixing this problem since it's an important client (if anything political happens I'll just treat it as the trigger).

Q1 Anyway, I'm starting to think that it might be a corrupted MDAC library.

The server that's running our POS is serviced by another company and I'm starting to suspect something happened that no one's telling me stuff about.
I'm going to try a lot of what suggested with the other posts (on related matter) and see if that helps.
I'm still pretty much learning MS SQL on the fly.
I've bought a book on it but I'd value some better sources.

You are welcome.

A1 You might consider having the MDAC component status determined with the compatibility checking tool. See:

http://www.microsoft.com/data/download.htm#CCinfo

http://download.microsoft.com/downl...XP/EN-US/cc.exe

RE:
Q2 Would you happen to have some sort of good links or a list of good references. I appreciated your help. Thanks again. Kind regards, Broomer

A2 Yes. Here are a few references: (on the fly, as it were)

* Best "On the fly", and general resource:
Upgrade to the SP3 update to BOL = Books On Line, http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp and use the F1 key (also shift - F1 on hilighted Query Analyzer text)

For some Links See:
http://www.microsoft.com/sql/techinfo/administration/2000/default.asp
http://www.sswug.org/ (great source compiling other resources, membership $ includes Sql server Mag.)
http://www.MSSqlServer.com
http://www.databasejournal.com/features/mssql/

Reference texts:
* Inside Microsoft SQL Server:
- Kalen Delaney for >= Ver 7.0
- Ron Soukop for <= Ver 6.5

- MS Press - Sql Server Performance Tuning

- Solomon for 6.5 Sql Server Unleashed
- Solomon for TSQL - Teach Yourself tsql in 21 Days
- Hotek for 7.0 (mostly still valid in 2k may have newer materials out)
- C. Date for RDBMS fundamentals (not vendor specific)
- Louis Davidson for design - Sql Server 2k DB Design
- Utley for Web Dev
- Peterson for DTS

Note: Those in general admin type roles sometimes prefer something like the SAMS DBA survival guide and a "complete" type reference like Shapiro's (as an alternative to obtaining a collection multiple advanced reference texts). Lots of good texts exist, however, quite a few "survival", fundamentals, and beginners type books offer nothing beyond BOL, and are less well organized.|||Cool Thanks.

All the best.

Kind regards,

Ka Kwok|||Hi, you guys have been extremely helpful. Anyway, I kinda nutted out the problem. Some shmoll has renamed the sa password. I came across the following MSKBA(#322336):
1. Goto the BINN dir
2. osql -U sa <enter><enter> ::This test the password
3. If you get a password file then at least you know the user's there.

To change the password:
1. Goto the BINN dir
2. osql -E <enter> ::Logs you in using Windows authentication
3. sp_password @.old = null, @.new = <password>, @.loginame = 'sa' <enter> ::Where <password> is the new 'password ' (in single quotes) or null for no password
4. go <enter>

My application needed no password (weak security I know, but it ain't my code), and that got them going (touch wood).

Thanks again.

All the best.

Kind regards,

Ka Kwok