Hot answers tagged transaction
18
For relatively small values of n (20 in this example), you can use a method that exploits the fact that the natural integers are combinations of bits.
T-SQL Solution
Sample data:
DECLARE @Sample AS TABLE
(
item_id tinyint IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
item nvarchar(500) NOT NULL,
bit_value AS
CONVERT
...
13
I've not tried fn_dblog on Express but if it is available the following will give you delete operations:
SELECT
*
FROM
fn_dblog(NULL, NULL)
WHERE
Operation = 'LOP_DELETE_ROWS'
Take the transaction ID for transactions you're interested in and identify the SID that initiated the transaction with:
SELECT
[Transaction SID]
FROM
...
13
It will truncate automatically but that is very different to shrink. Truncation reclaims log space for re-use, shrinking physically reduces the file size to release space back to the OS. If your log has grown to its current size its likely that it will grow again if you shrink it.
I'd suggest getting a handle on what typical and maximum log usage is for ...
12
There are a few different approaches depending on the details of your batch process and why you're trying to view the uncommitted changes.
1) Oracle Workspace Manager is a tool that was originally designed to allow people developing Spatial applications to have the equivalent of extremely long-running transactions (i.e. transactions that may require ...
12
Summary
If you have locking problems then you have a problem with your code: it isn't the database engine
It isn't a magic bullet
You may add more problems
Load
It will also increase load on your tempdb and CPU. Also see:
"Performance Impact: The Potential Cost of Read_Committed_Snapshot" (Linchi Shea)
Safety
Most important, snapshot isolations ...
12
An insert is always within a transaction.
If you don't have an explicit BEGIN TRAN ... COMMIT or SET IMPLICIT_TRANSACTIONS ON then the statement runs as a self contained auto commit transaction.
The trigger is always part of the transaction for the action that fires the trigger. If an error occurs in the trigger that causes transaction rollback then the ...
10
For SQL Server, you could argue that a commit operation is nothing more than writing LOP_COMMIT_XACT to the log file and releasing locks, which is of course going to be faster than the ROLLBACK of every action your transaction performed since BEGIN TRAN.
If you are considering every action of a transaction, not just the commit, I'd still argue your ...
9
I'm only familiar with SQL Server:
Each operation is atomic. If you run a delete, and it cascades to other tables, those records are gone, too, as soon as the statement is over. They don't magically come back into existence unless the transaction is rolled back.
If you're relying on the ID values and don't want to cascade the related tables, consider ...
8
What Oracle doesn't have is a read uncommitted isolation mode. In other words you will not be able to query uncommitted data in another transaction.
There are ways of getting information out of a long running transaction - one not mentioned so far is autonomous transactions (which should be used with caution)
7
For Oracle, rollback can take many times longer than the time it took to make the changes that are rolling back. This often does not matter because
No locks are held while the transaction is rolling back
It is handled by a low priority background process
For SQL Server I'm not sure if the situation is the same but someone else will say if it isn't...
As ...
7
Yes. Transactions apply to DDL and span batches.
I'd do something like this. Note the use of SERIALIZABLE ISOLATION to ensure full isolation and XACT_ABORT which will force a rollback on any error.
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
begin Transaction
GO
exec sp_rename LogTable, LogTableOld;
GO
CREATE TABLE dbo.LogTable(
...
7
I believe this will give us something closer to oracle where if one
transaction is updating other transactions can still read the old
data. Is this correct?
Yes, this is correct.
Well worth reading the links in gbn's answer and I believe the same applies to Oracle's default MVCC as to SQL Server in Snapshot Isolation mode. I would add that if you ...
7
Yes - LogMiner can do this. In fact, if you only want committed transactions, you have to specifically filter the output! And there is TABLE_NAME in V$LOGMINER_CONTENTS, that's how you would look at a single table.
7
In an ideal world you would have two choices, SNAPSHOT and READ COMMITTED SNAPSHOT (RCSI). Make sure you understand the basics of transaction isolation levels before you decide which is appropriate for your workload. Specifically be aware of the different results you may see as a result of moving to RCSI.
This sounds like it's not an ideal world as you ...
7
No.
If you try the following from two different connections then the second one will be blocked by the first (visible in sys.dm_os_waiting_tasks) but neither will result in any transaction log activity and running DBCC OPENTRAN will report "No active open transactions" (assuming no other activity).
SELECT COUNT_BIG(*)
FROM master..spt_values v1 WITH ...
7
Extending Mark's answer...
When a client timeout event occurs (.net CommandTimeout for example), the client sends an "ABORT" to SQL Server. SQL Server then simply abandons the query processing. No transaction is rolled back, no locks are released.
Now, the connection is returned to the connection pool, so it isn't closed on SQL Server. If this ever happens ...
6
You could use flashback queries to see the table without your own uncommited data.
Consider:
SQL> CREATE TABLE my_table
2 AS SELECT ROWNUM ID FROM dual CONNECT BY LEVEL <= 5;
Table created
SQL> INSERT INTO my_table VALUES (6);
1 row inserted
To see the difference between the table with my transaction and the table as seen by others I could ...
6
Couple of additional points to add to the other answers:
SET ALLOW_SNAPSHOT_ISOLATION ON only enables snapshot isolation in a database. To take advantage of it you have to recode and SET TRANSACTION ISOLATION LEVEL SNAPSHOT for the transactions you want it to apply to. The calling code will need to be changed to handle update conflict errors.
After SET ...
6
Correct, use SNAPSHOT isolation to get consistent, commited data from before the transaction started.
The READ UNCOMMITTED isolation (aka NOLOCK hint) will read dirtz, inconsistent data
When you enable SNAPSHOT isolation, then it takes effect for all SELECTs going forward. You run ALTER DATABASE with READ_COMMITTED_SNAPSHOT in this case
Edit: added link ...
6
You can get the SCN for a row with the ORA_ROWSCN pseudocolumn
Unless you have set row-level dependency tracking for the table, this will report the SCN of the last change to the block the row is in, which may not be much use. You can turn on row-level dependency tracking at create table time only, so you may need to drop and re-create your table.
Note ...
6
I'm answering this with hesitation as there isn't enough information in your description of the problem to be 100% sure this is the best advice. "Hangs or throws an exception" suggests the source of the issue isn't properly understood, so proceed with caution.
The simplest solution to this is probably SET XACT_ABORT ON.
XACT_ABORT determines whether SQL ...
6
Rather than theorising as to what could potentially be responsible, qualify exactly what is by capturing the SQL Server Profiler Deadlock Graph Event.
How To Track Down Deadlocks in SQL Server using Profiler
Once you know what the problem is that you are trying to solve, you can then proceed to actually solve it.
6
This looks like a typical case of table scan (possible missing indexes, but could be more). The SELECT has chosen a page lock granularity, indicative of a large scan (on blocks table). Also note how all the locks are on the same resource rowset, the clustered index, another indication that the SELECT does not use a selective secondary index to locate the ...
6
TRUNCATE table will take a SCH-M lock on the table.
Even at READ UNCOMMITTED level the SELECT query will need to take a SCH-S lock. This won't be possible until the TRUNCATE transaction has ended and the conflicting SCH-M lock has been released.
5
Not all transactions will have their commit activity perform much better than their rollback. One such case is the delete operation in SQL. When a transaction deletes rows, these rows are marked as ghost records. Once a commit is issued and a ghost record cleanup task starts, then only are these records 'deleted'.
If a rollback was issued instead, it just ...
5
Well, there are three different types of replication, but they are all transactionally safe, to a degree.
There are three types of replication in SQL Server: Transaction Replication, Merge Replication, and Snapshot Replication.
Transaction Replication
This type of replication transfers each modification that occurs in the master to each of the ...
5
They are completely unrelated concepts.
Serializable is a type of isolation level (read here for more info)
Flashback Query is an Oracle technology for getting querying data as it was at a certain point in time - for example, if certain conditions are met you can use Flashback Query to get those million rows back after committing that transaction that ...
5
I've just come across StandardCDC which might be of interest:
StandardCDC captures data manipulation language (DML) changes for a specified table and stores the results in a relational format. The capture table mirrors the column list of the tracked object, with options for storing only specific columns.
5
As Remus Rusanu said, you cannot rebuild the database. It's bad, but it's true. But the data from your log can be extracted by tools like Lumigent LogExplorer. I understand, that it is very litle possiblity that it can help, but maybe this is the way to extract something that you need.
5
No. It doesn't affect anything regarding the internal management within SQL Server. You're setting that connection for you, for your queries. SQL Server manages it's own locking it's own way.
Why would you turn off page and row locking on an index? You're more likely to see more severe locking than if you let SQL Server manage that index as it sees fit. By ...
Only top voted, non community-wiki answers of a minimum length are eligible
