Hot answers tagged

51

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 ...


29

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 ...


27

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 ...


25

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 ...


23

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 ...


21

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 ...


17

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 ...


16

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 ...


16

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 ...


16

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 ...


15

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( ...


15

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 ...


14

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 ...


13

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 ...


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 ...


12

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 ...


11

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 ...


11

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 ...


11

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 ...


11

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 ...


11

when I explicitly lock a row or a table, am I employing the exact same constructs that are used by the database's transaction facilities under the covers to make the transaction work properly? Yes. If that would not be true, then your own 'locking' would only be scoped to other similar 'locking' and not interact with the engine own locking. So you would ...


11

I haven't managed to reproduce this after running your code a few times. I presume that it must happen when a later row gets inserted onto an earlier page in the file though. So the order of operations is (for example) Rows inserted into heap on pages 200, 207, 223 Select statement starts and performs an allocation ordered scan. Finds that the first ...


10

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 ...


10

The reason is that that some statements, like CREATE TABLE cause an implicit commit. You can read about them in the documentation: Statements That Cause an Implicit Commit. So the original sequence of statements: START TRANSACTION SHOW TABLES LIKE customers CREATE TABLE `customers__20150119_14_08_20` LIKE `customers` INSERT INTO ...


10

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 ...


9

The session along with the spid attached will be killed/rolled back. http://stackoverflow.com/questions/3978227/how-to-kill-or-rollback-active-transaction https://technet.microsoft.com/en-us/library/ms173730%28v=sql.110%29.aspx EDIT: I would check your logs to verify, but in essence, the socket would be forcibly closed. It's not a database issue from ...


9

1. Does the trigger follow the relational database's ACID principle? Is there any chance an insert might be committed but the trigger fail? This question is partly answered in a related question you linked to. Trigger code is executed in the same transactional context as the DML statement that caused it to fire, preserving the Atomic part of the ACID ...


9

"Where is uncommitted data stored, such that a READ_UNCOMMITTED transaction can read uncommitted data from another transaction?" The new uncommitted record (clustered PK) versions are treated as the "current" version of the record on page. So they can be stored in the buffer pool and/or in the tablespace (e.g. tablename.ibd). Transactions that then need to ...


8

You can still see (atleast in SQL Server 2008R2) trunc. log on chkpt. using sp_dboption, and you can set the database option as well In SQL Server 2012 and up, you will get a nice error Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'sp_dboption'. Its there lying for backward compatibility ONLY. In SQL Server 2000 and up, ...


8

There are two points of confusion here. The first is that you seem to think 'NULL' and NULL are the same; they're not. The former is a string literal, the latter merely signifies the absence of a value. The second is that you can use NULL in equality comparisons; and you cannot. Try this: SELECT CASE NULL WHEN NULL THEN 1 ELSE 0 END; Result: ---- 0 ...



Only top voted, non community-wiki answers of a minimum length are eligible