99
Is a bad practice to create a transaction always?
It depends on what context you are talking here. If it is an update, then I would highly recommend using TRANSACTIONS explicitly. If it is a SELECT then NO (explicitly).
But wait there is more to understand first :
Everything in sql server is contained in a transaction.
When the session option ...
56
Given only the code shown in the question, and assuming that none of the three sub-procs have any explicit transaction handling, then yes, an error in any of the three sub-procs will be caught and the ROLLBACK in the CATCH block will roll back all of the work.
BUT here are some things to note about transactions (at least in SQL Server):
There is only ever ...
48
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 are ...
38
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 ...
35
A SQL statement always runs in a transaction. If you don't start one explicitly, every SQL statement will run in a transaction of itself.
The only choice is whether you bundle multiple statements in one transaction. Transactions that span multiple statements leave locks that hurt concurrency. So "always" creating a transactions is not a good idea. You ...
34
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
...
34
I know this is an old thread but I would say to a large degree snapshot isolation is a magic bullet. It will eliminate all of your blocking between readers and writers. It will however not prevent writers from blocking other writers. There is no way around that.
In my experience, the additional load on the TEMPDB is negligible and the benefits of row ...
29
It is simply a remnant of olden times, when it was used in contrast to batch processing. "Online" here means "interactive", that is, requests to the database are processed as they come and responses are given more or less immediately, or at least as soon as they are available. Batch processing would collect requests into, well, batches, and execute them on ...
25
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 ...
24
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(
...
24
In addition to what @Craig provided (and correcting some of it):
Effective Postgres 9.4, UNIQUE, PRIMARY KEY and EXCLUDE constraints are checked immediately after each row when defined NOT DEFERRABLE. This is different from other kinds of NOT DEFERRABLE constraints (currently only REFERENCES (foreign key)) which are checked after each statement. We worked ...
23
Now I have a SQL statement, which selects the MAX value, adds to it, and INSERTs that value into the same table -- all inside one statement. So in theory, it's not possible to get a PK violation on this statement (although I think could possibly get a deadlock). But, somehow, it IS getting one.
It definitely IS possible. This is due to your isolation level ...
20
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 ...
20
One bottleneck to be aware of is the InnoDB Log Buffer. The size is set by innodb_log_buffer_size. Here is what the MySQL Documentation says about it:
The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is 8MB. A large log buffer enables large transactions to run without a need to write the log to disk ...
19
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 ...
19
First, you should always have proper transaction handling in all of your procedures so that it does not matter if they are called by app code, by another procedure, individually in an ad-hoc query, by a SQL Agent job, or some other means. But single DML statements, or code that doesn't make any modifications, doesn't need an explicit Transaction. So, what I ...
18
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 ...
17
File this under "just because you can, doesn't mean you should"
If you generate a bind token in the first session, and somehow publish it, you can join its transaction from another session.
Eg from spid 61:
if @@TRANCOUNT > 0 rollback
go
begin transaction
select *
into ##t
from sys.objects
declare @bind_token varchar(255);
exec sp_getbindtoken @...
17
Generally speaking, no. SQL Server compiles the whole batch at the current scope before execution so referenced entities have to exist (statement-level recompilations may also happen later). The main exception is Deferred Name Resolution but that applies to tables, not columns:
Deferred name resolution can only be used when you reference nonexistent table ...
16
Yes.
Everything you did inside the same transaction is visible to later commands inside the same transaction. Just not to other transactions until committed. This is true for all isolation levels except Read uncommitted where "dirty reads" are possible (but that does not affect your question per se).
It's implemented with the MVCC model (Multiversion ...
14
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 ...
14
You like need to wrap that code in CREATE PROCEDURE ... syntax, and remove the GO statements after BEGIN TRANSACTION and before COMMIT TRANSACTION.
GO
CREATE PROCEDURE dbo.AssignUserToTicket
(
@updateAuthor varchar(100)
, @assignedUser varchar(100)
, @ticketID bigint
)
AS
BEGIN
BEGIN TRANSACTION;
SAVE TRANSACTION MySavePoint;
SET @...
14
Having an open transaction by itself will have almost no consequence. A simple
BEGIN TRANSACTION
-- wait for a while, doing nothing
-- wait a bit longer
COMMIT
will, at worst, hold a few bytes of status values. No big deal.
Most programs will do actual work within the transaction and this is another matter. The point of a transaction is so you can be sure ...
14
A index cannot be deferred - doesn't matter if it is UNIQUE or not, partial or not, only a UNIQUE constraint. Other types of constraints (FOREIGN KEY, PRIMARY KEY, EXCLUDE) are also deferrable - but not CHECK constraints.
So the unique partial index (and the implicit constraint it implements) will be checked at every statement (and in fact after every row ...
14
You can find a pretty comprehensive guide to this question here, but to summarise, SQL Server will not return control to the application that committed a transaction until that transaction has been hardened to disk. Specifically, once it has been hardened to the transaction log file, control can be returned.
The data, at this point, may not be hardened into ...
13
Bear with me, this is a complicated question to clarify and we may go through a few rounds of edit and commenting to plug the gaps. From the way your question is phrased I'm guessing you're not differentiating the atomicity, isolation, consistency and durability elements of ACID.
When a database begins a transaction, all statements executed in that
...
13
A connection from the pool will have the isolation level set by the last client to use that connection. Yes, it really is that scary.
The long and the short of it is that if you change the isolation level of a connection you must explicitly set it back to READ COMMITTED before closing. Better is to explicitly declare your required isolation level at the ...
13
As I understand it, your issue here is that the constraint is checked after each statement, but you want it checked at the end of the transaction, so it compares the before-state to the after-state, ignoring the intermediate states.
If so, that is possible with a deferrable constraint.
See SET CONSTRAINTS and DEFERRABLE constraints as documented in CREATE ...
13
Individual statements -- DML, DDL, etc -- are transactions in themselves. So yes, after each iteration of the loop (technically: after each statement), whatever that UPDATE statement changed has been auto-committed.
Of course, there is always an exception, right? It is possible to enable Implicit Transactions via SET IMPLICIT_TRANSACTIONS, in which case the ...
13
A single statement like that works the same with MyISAM or InnoDB, with a transaction or with autocommit=ON. It blocks enough to do the query, thereby blocking the other connection. When finished, the other connection proceeds. In all cases, the column is soon decremented by 11.
A third user may see the value decremented by 0 or 4 or 7 or 11. The "very ...
Only top voted, non community-wiki answers of a minimum length are eligible
