If you start a transaction with BEGIN TRAN and then, for some reason this is
interrupted and not recovered from (in other words, no ROLLBACK or no
COMMIT), what happens? Is there a time after which this will timeout? If
so, where is it set and what is the deafault?
I have a situation where i believe this happened, and now queries to the
table simply hang, do not return and do not give any error message. More
specifically if the query contains a WHERE clause. For instance, the query
SELECT * from Table returns quickly (it is a small table). But SELECT *
from Table WHERE column='xxx' is the one that hangs.
Any advice is appreciated. Thank you.I would expect it to actually be the other way around. But in any case an
open tran will stay that way for as long as the connection that started it
is valid. Your application that begins the tran should always handle
situations sucha s that. You can check to see if there is an open tran in
any db by running DBCC OPENTRAN(). If you find one that is hanging around
long after it should be you can kill that SPID and the tran will be rolled
back automatically. You can also use sp_who2 to see if you are begin blocked
and by who. sp_lock will also show any currently held locks.
Andrew J. Kelly SQL MVP
"THOMAS CONLON" <someone@.verizon.net> wrote in message
news:bJlgg.2906$td6.2754@.trnddc02...
> If you start a transaction with BEGIN TRAN and then, for some reason this
> is interrupted and not recovered from (in other words, no ROLLBACK or no
> COMMIT), what happens? Is there a time after which this will timeout? If
> so, where is it set and what is the deafault?
> I have a situation where i believe this happened, and now queries to the
> table simply hang, do not return and do not give any error message. More
> specifically if the query contains a WHERE clause. For instance, the
> query SELECT * from Table returns quickly (it is a small table). But
> SELECT * from Table WHERE column='xxx' is the one that hangs.
> Any advice is appreciated. Thank you.
>|||THOMAS CONLON (someone@.verizon.net) writes:
> If you start a transaction with BEGIN TRAN and then, for some reason
> this is interrupted and not recovered from (in other words, no ROLLBACK
> or no COMMIT), what happens? Is there a time after which this will
> timeout? If so, where is it set and what is the deafault?
There is no timeout. If there is never any COMMIT or ROLLBACK explicitly,
there will be a ROLLBACK when the connection is physically disconnected.
Physical disconnection happens when the application exits. It can also
happen if the application code issues a disconnect. However, usually
connection is in force in the client API. This causes the physical
connection to be retained for 60 seconds, unless it can be reused in this
time frame. If a connection is reused from the pool, the rollback will
occur on reuse.
> I have a situation where i believe this happened, and now queries to the
> table simply hang, do not return and do not give any error message.
> More specifically if the query contains a WHERE clause. For instance,
> the query SELECT * from Table returns quickly (it is a small table).
> But SELECT * from Table WHERE column='xxx' is the one that hangs.
Note that if you get a "Timeout expired" in your application, you must
submit a IF @.@.trancount > 0 ROLLBACK TRANSACTION". The timeout is
something the client API raises, and SQL Server does not know what it
means, and will keep the transaction alive.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Showing posts with label commit. Show all posts
Showing posts with label commit. Show all posts
Friday, March 23, 2012
question on Transactions and Commit
Labels:
commit,
database,
isinterrupted,
microsoft,
mysql,
oracle,
recovered,
rollback,
server,
sql,
tran,
transaction,
transactions,
words
Saturday, February 25, 2012
question on commit
Hi
i have a question about what the COMMIT command actually does
According to my understanding i thought a commit is just like including a marker and it shouldnt take time to finish.
Checkpoint is actuallly the process which writes committed transaction to the disk so that should be the one taking time.
If this is true why does BCP in take a long time at the end when its commiting all rows copied from the file
Anywhere i can go to read about what commit actually does and what checkpoint does etc
SanjaySanjay
BOL says:
COMMIT TRANSACTION
Marks the end of a successful implicit or user-defined transaction. If
@.@.TRANCOUNT is 1, COMMIT TRANSACTION makes all data modifications performed
since the start of the transaction a permanent part of the database, frees
the resources held by the connection, and decrements @.@.TRANCOUNT to 0. If
@.@.TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements @.@.TRANCOUNT
only by 1.
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:A1B8DE74-8DF1-4F7C-9341-3161D1F05158@.microsoft.com...
> Hi
> i have a question about what the COMMIT command actually does
> According to my understanding i thought a commit is just like including a
marker and it shouldnt take time to finish.
> Checkpoint is actuallly the process which writes committed transaction to
the disk so that should be the one taking time.
> If this is true why does BCP in take a long time at the end when its
commiting all rows copied from the file
> Anywhere i can go to read about what commit actually does and what
checkpoint does etc
> Sanjay
>|||Hi Sanjay,
By default checkpoints happen automatically and very regularly on SQL
Server. How often a checkpoint happen is determined by the Recovery Interval
setting. If SQL Server determines that the estimate time to recover the
database by rolling forward the transactions in the transaction log is
longer than the recovery interval, it will issue a checkpoint so all the
changes are flushed to the data file and don't have to be rolled forward the
next time the database starts up.
What I think happens is that BCP makes so many changes that it automatically
triggers a checkpoint on commit.
--
Jacco Schalkwijk
SQL Server MVP
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:A1B8DE74-8DF1-4F7C-9341-3161D1F05158@.microsoft.com...
> Hi
> i have a question about what the COMMIT command actually does
> According to my understanding i thought a commit is just like including a
marker and it shouldnt take time to finish.
> Checkpoint is actuallly the process which writes committed transaction to
the disk so that should be the one taking time.
> If this is true why does BCP in take a long time at the end when its
commiting all rows copied from the file
> Anywhere i can go to read about what commit actually does and what
checkpoint does etc
> Sanjay
>|||If I am not mistaken, BCP can also defer all index fixups to once ( at the
end)... I think TABLOCK might be required for this, but i am not sure...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:A1B8DE74-8DF1-4F7C-9341-3161D1F05158@.microsoft.com...
> Hi
> i have a question about what the COMMIT command actually does
> According to my understanding i thought a commit is just like including a
marker and it shouldnt take time to finish.
> Checkpoint is actuallly the process which writes committed transaction to
the disk so that should be the one taking time.
> If this is true why does BCP in take a long time at the end when its
commiting all rows copied from the file
> Anywhere i can go to read about what commit actually does and what
checkpoint does etc
> Sanjay
>|||I think what you are looking for is the batch size. If
you do not specify a batch size by using ther -b
parameter, the entire set of rows being inserted is
treated as one transaction and all row inserts are
commited and checkpointed at the same time.
This option is documented in the BOL.
I hope that this helps.
Matthew Bando
BandoM@.CSCTechnologies(remove).com
>--Original Message--
>Hi
>i have a question about what the COMMIT command actually
does
>According to my understanding i thought a commit is just
like including a marker and it shouldnt take time to
finish.
>Checkpoint is actuallly the process which writes
committed transaction to the disk so that should be the
one taking time.
>If this is true why does BCP in take a long time at the
end when its commiting all rows copied from the file
>Anywhere i can go to read about what commit actually
does and what checkpoint does etc
>Sanjay
>.
>
i have a question about what the COMMIT command actually does
According to my understanding i thought a commit is just like including a marker and it shouldnt take time to finish.
Checkpoint is actuallly the process which writes committed transaction to the disk so that should be the one taking time.
If this is true why does BCP in take a long time at the end when its commiting all rows copied from the file
Anywhere i can go to read about what commit actually does and what checkpoint does etc
SanjaySanjay
BOL says:
COMMIT TRANSACTION
Marks the end of a successful implicit or user-defined transaction. If
@.@.TRANCOUNT is 1, COMMIT TRANSACTION makes all data modifications performed
since the start of the transaction a permanent part of the database, frees
the resources held by the connection, and decrements @.@.TRANCOUNT to 0. If
@.@.TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements @.@.TRANCOUNT
only by 1.
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:A1B8DE74-8DF1-4F7C-9341-3161D1F05158@.microsoft.com...
> Hi
> i have a question about what the COMMIT command actually does
> According to my understanding i thought a commit is just like including a
marker and it shouldnt take time to finish.
> Checkpoint is actuallly the process which writes committed transaction to
the disk so that should be the one taking time.
> If this is true why does BCP in take a long time at the end when its
commiting all rows copied from the file
> Anywhere i can go to read about what commit actually does and what
checkpoint does etc
> Sanjay
>|||Hi Sanjay,
By default checkpoints happen automatically and very regularly on SQL
Server. How often a checkpoint happen is determined by the Recovery Interval
setting. If SQL Server determines that the estimate time to recover the
database by rolling forward the transactions in the transaction log is
longer than the recovery interval, it will issue a checkpoint so all the
changes are flushed to the data file and don't have to be rolled forward the
next time the database starts up.
What I think happens is that BCP makes so many changes that it automatically
triggers a checkpoint on commit.
--
Jacco Schalkwijk
SQL Server MVP
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:A1B8DE74-8DF1-4F7C-9341-3161D1F05158@.microsoft.com...
> Hi
> i have a question about what the COMMIT command actually does
> According to my understanding i thought a commit is just like including a
marker and it shouldnt take time to finish.
> Checkpoint is actuallly the process which writes committed transaction to
the disk so that should be the one taking time.
> If this is true why does BCP in take a long time at the end when its
commiting all rows copied from the file
> Anywhere i can go to read about what commit actually does and what
checkpoint does etc
> Sanjay
>|||If I am not mistaken, BCP can also defer all index fixups to once ( at the
end)... I think TABLOCK might be required for this, but i am not sure...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:A1B8DE74-8DF1-4F7C-9341-3161D1F05158@.microsoft.com...
> Hi
> i have a question about what the COMMIT command actually does
> According to my understanding i thought a commit is just like including a
marker and it shouldnt take time to finish.
> Checkpoint is actuallly the process which writes committed transaction to
the disk so that should be the one taking time.
> If this is true why does BCP in take a long time at the end when its
commiting all rows copied from the file
> Anywhere i can go to read about what commit actually does and what
checkpoint does etc
> Sanjay
>|||I think what you are looking for is the batch size. If
you do not specify a batch size by using ther -b
parameter, the entire set of rows being inserted is
treated as one transaction and all row inserts are
commited and checkpointed at the same time.
This option is documented in the BOL.
I hope that this helps.
Matthew Bando
BandoM@.CSCTechnologies(remove).com
>--Original Message--
>Hi
>i have a question about what the COMMIT command actually
does
>According to my understanding i thought a commit is just
like including a marker and it shouldnt take time to
finish.
>Checkpoint is actuallly the process which writes
committed transaction to the disk so that should be the
one taking time.
>If this is true why does BCP in take a long time at the
end when its commiting all rows copied from the file
>Anywhere i can go to read about what commit actually
does and what checkpoint does etc
>Sanjay
>.
>
question on commit
Hi
i have a question about what the COMMIT command actually does
According to my understanding i thought a commit is just like including a ma
rker and it shouldnt take time to finish.
Checkpoint is actuallly the process which writes committed transaction to th
e disk so that should be the one taking time.
If this is true why does BCP in take a long time at the end when its commiti
ng all rows copied from the file
Anywhere i can go to read about what commit actually does and what checkpoin
t does etc
SanjaySanjay
BOL says:
COMMIT TRANSACTION
Marks the end of a successful implicit or user-defined transaction. If
@.@.TRANCOUNT is 1, COMMIT TRANSACTION makes all data modifications performed
since the start of the transaction a permanent part of the database, frees
the resources held by the connection, and decrements @.@.TRANCOUNT to 0. If
@.@.TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements @.@.TRANCOUNT
only by 1.
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:A1B8DE74-8DF1-4F7C-9341-3161D1F05158@.microsoft.com...
marker and it shouldnt take time to finish.
the disk so that should be the one taking time.
commiting all rows copied from the file
checkpoint does etc
By default checkpoints happen automatically and very regularly on SQL
Server. How often a checkpoint happen is determined by the Recovery Interval
setting. If SQL Server determines that the estimate time to recover the
database by rolling forward the transactions in the transaction log is
longer than the recovery interval, it will issue a checkpoint so all the
changes are flushed to the data file and don't have to be rolled forward the
next time the database starts up.
What I think happens is that BCP makes so many changes that it automatically
triggers a checkpoint on commit.
Jacco Schalkwijk
SQL Server MVP
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:A1B8DE74-8DF1-4F7C-9341-3161D1F05158@.microsoft.com...
marker and it shouldnt take time to finish.
the disk so that should be the one taking time.
commiting all rows copied from the file
checkpoint does etc
end)... I think TABLOCK might be required for this, but i am not sure...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:A1B8DE74-8DF1-4F7C-9341-3161D1F05158@.microsoft.com...
marker and it shouldnt take time to finish.
the disk so that should be the one taking time.
commiting all rows copied from the file
checkpoint does etc
i have a question about what the COMMIT command actually does
According to my understanding i thought a commit is just like including a ma
rker and it shouldnt take time to finish.
Checkpoint is actuallly the process which writes committed transaction to th
e disk so that should be the one taking time.
If this is true why does BCP in take a long time at the end when its commiti
ng all rows copied from the file
Anywhere i can go to read about what commit actually does and what checkpoin
t does etc
SanjaySanjay
BOL says:
COMMIT TRANSACTION
Marks the end of a successful implicit or user-defined transaction. If
@.@.TRANCOUNT is 1, COMMIT TRANSACTION makes all data modifications performed
since the start of the transaction a permanent part of the database, frees
the resources held by the connection, and decrements @.@.TRANCOUNT to 0. If
@.@.TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements @.@.TRANCOUNT
only by 1.
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:A1B8DE74-8DF1-4F7C-9341-3161D1F05158@.microsoft.com...
quote:
> Hi
> i have a question about what the COMMIT command actually does
> According to my understanding i thought a commit is just like including a
marker and it shouldnt take time to finish.
quote:
> Checkpoint is actuallly the process which writes committed transaction to
the disk so that should be the one taking time.
quote:
> If this is true why does BCP in take a long time at the end when its
commiting all rows copied from the file
quote:
> Anywhere i can go to read about what commit actually does and what
checkpoint does etc
quote:|||Hi Sanjay,
> Sanjay
>
By default checkpoints happen automatically and very regularly on SQL
Server. How often a checkpoint happen is determined by the Recovery Interval
setting. If SQL Server determines that the estimate time to recover the
database by rolling forward the transactions in the transaction log is
longer than the recovery interval, it will issue a checkpoint so all the
changes are flushed to the data file and don't have to be rolled forward the
next time the database starts up.
What I think happens is that BCP makes so many changes that it automatically
triggers a checkpoint on commit.
Jacco Schalkwijk
SQL Server MVP
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:A1B8DE74-8DF1-4F7C-9341-3161D1F05158@.microsoft.com...
quote:
> Hi
> i have a question about what the COMMIT command actually does
> According to my understanding i thought a commit is just like including a
marker and it shouldnt take time to finish.
quote:
> Checkpoint is actuallly the process which writes committed transaction to
the disk so that should be the one taking time.
quote:
> If this is true why does BCP in take a long time at the end when its
commiting all rows copied from the file
quote:
> Anywhere i can go to read about what commit actually does and what
checkpoint does etc
quote:|||If I am not mistaken, BCP can also defer all index fixups to once ( at the
> Sanjay
>
end)... I think TABLOCK might be required for this, but i am not sure...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:A1B8DE74-8DF1-4F7C-9341-3161D1F05158@.microsoft.com...
quote:
> Hi
> i have a question about what the COMMIT command actually does
> According to my understanding i thought a commit is just like including a
marker and it shouldnt take time to finish.
quote:
> Checkpoint is actuallly the process which writes committed transaction to
the disk so that should be the one taking time.
quote:
> If this is true why does BCP in take a long time at the end when its
commiting all rows copied from the file
quote:
> Anywhere i can go to read about what commit actually does and what
checkpoint does etc
quote:
> Sanjay
>
Subscribe to:
Posts (Atom)