if server fails, does server finish the tranaction that did not occor by reading the tranaction log?
-
if server fails, does server finish the tranaction that did not occor by reading the tranaction log . say part of a transaction was to update the balance of a checking account record set balance - 1000, the next statement was to update the record in a different databases checking account table set balance + 1000.
if the server went down before second statement successeed, will it see it by reading the trans log and compare waht should of happened to the actual data changes an pick up where it left off and update the savings account + 1000?
is this how it works???
thanks!!
-
If the transaction hasn't been committed in the transaction log, then it is rolled back when the server restarts.
--------------------
Colt 45 - the original point and click interface
-
Only if you have explicitly started a transaction.
For example, to transfer $1000 from one account to another account, you might write:
Update account set balance = balance - 1000
where AccountId = @FromAccountID
Update account set balance = balance + 1000
where AccountId = @ToAccountID
SQL Server internally converts these statements to:
BEGIN TRANSACTION
Update account set balance = balance - 1000
where AccountId = @FromAccountID
COMMIT
BEGIN TRANSACTION
Update account set balance = balance + 1000
where AccountId = @ToAccountID
COMMIT
On start-up, SQL Server reads the transaction log and then for each spid, matches "BEGIN TRANSACTION"/"COMMIT" to re-apply the transaction. When the "BEGIN TRANSACTION" does not have a corresponding "COMMIT", the transaction is reversed.
Instead you should code explicit "begin transaction" and "commit" commands to insure that either both suceed or both fail.
BEGIN TRANSACTION
Update account set balance = balance - 1000
where AccountId = @FromAccountID
Update account set balance = balance + 1000
where AccountId = @ToAccountID
COMMIT
There is an semi-documented command to dump the transaction log, but do not use this on a live SQL Server as the quantity of output could be large and will affect the SQL Server performance.
DBCC LOG
This command is used to view the transaction log for the specified database.
Syntax:
DBCC log ({dbid|dbname}, [, type={-1|0|1|2|3|4}])
where:
dbid or dbname - Enter either the dbid or the name of the database
type - is the type of output, and includes these options:
0 - minimum information (operation, context, transaction id)
1 - more information (plus flags, tags, row length, description)
2 - very detailed information (plus object name, index name, page id, slot id)
3 - full information about each operation
4 - full information about each operation plus hexadecimal dump of the current transaction log's row.
-1 - full information about each operation plus hexadecimal dump of the current transaction log's row, plus Checkpoint Begin, DB Version, Max XDESID
by default, type = 0
To view the transaction log for the master database, run the following command:
DBCC log (master)
SQL = Scarcely Qualifies as a Language
-
ANother super answer, that explained alot. thanks very much!!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply