A mechanism for committing a consistent set of changes into a database atomically.

learn more… | top users | synonyms

3
votes
2answers
59 views

Transactions within a Transaction

What behaviour would PostgreSQL display if for example the script below were called BEGIN; SELECT * FROM foo; INSERT INTO foo(name) VALUES ('bar'); BEGIN; <- The point of interest END; Would ...
3
votes
1answer
259 views

Does blocking always mean open transaction?

This may seem a bit dull question but does blocking always mean that there is open transaction and that may cause transaction log grow to infinity because open transaction prevents log truncation ...
3
votes
2answers
271 views

Synonyms and transactions

I'm working with an application that uses SQL synonyms to switch between tables on local and remote databases without duplicating code. BEGIN TRANSACTION if (@isRemote = 1) BEGIN CREATE ...
3
votes
1answer
728 views

Find out if two rows came from same transaction in Oracle

I am using Oracle 11.1 and would like to figure out if two rows in one table where inserted by the same transaction. Is there an easy sql statement to map a row to an transactions id? The table is an ...
3
votes
1answer
1k views

Why can't I change the isolation levels of my connection in MySQL?

I am trying to experiment a bit with the isolation level of MySQL in a test environment. I do the following: mysql> set @@session.tx_isolation='READ-UNCOMMITED'; ERROR 1231 (42000): Variable ...
3
votes
1answer
223 views

Is SQL Server Replication transactionally 'safe' across all published tables?

We have a system that has one of its datasources still on SQL Server 2000. In order to achieve higher availability, we are looking to setup transactional replication from this 2000 server to a 2008 ...
3
votes
1answer
482 views

Transaction and data consistency during a failure

When a database begins a transaction, all statements executed in that transaction are isolated and atomic (and consistent and durable). These are pretty much the definition of a transaction. ...
3
votes
1answer
443 views

Complex constraint across all data in a table

We have a table to record processing that occurs on a system and we need to ensure that only a single row has an 'in process' status. I want to ensure that the result of this is always either zero or ...
3
votes
1answer
245 views

SQL Server 2008 - Adding a column and a filtered index within a single transaction?

This is more of a general question. Why can you not add a filtered index within the same transaction scope as adding the new column in the first place? The solution is of course simple, just make two ...
3
votes
2answers
314 views

What is the correct term to describe a “persistent transaction”?

I'm kind of a the administrator of a zero-administration OODBMS, but mostly I do programming. I remember reading years ago about some SQL database that had a concept of something like a permanent ...
3
votes
2answers
1k views

How do I implement insert-if-not-found for transactions at serializable isolation level?

I'm having a hard time figuring out how to exactly implement a 'insert if not found' function. Consider the following. We have a table called artist with 2 columns, (name, id) where name is the ...
3
votes
1answer
2k views

MySQL Insert into two tables using new IDs

I'm trying to select data already in the database and insert it into two tables using the generated ID from table one. table1 contains configuration options, table2 contains pricing data. Table2 uses ...
3
votes
1answer
322 views

SQL Server : KEY lock prevents FK on nearby row, but not SELECT [closed]

I have the following table structure (simplified, pseudocode): TABLE dbo.Users (Id int NOT NULL IDENTITY PRIMARY KEY) TABLE dbo.Transfers ( Id int NOT NULL IDENTITY PRIMARY KEY, ...
3
votes
1answer
1k views

Rotate a table in PostgreSQL

Problem description I need to rotate (with DROP and CREATE) a table which is heavily used by other clients. At present, I have a program which replaces (DROP + CREATE) this table. Sometimes, just ...
3
votes
2answers
498 views

read before write transaction

Talking about transactions (db transactions), does a read operation have always precede a write operation? That's obvious, isn't it? Thanks in advance *Of course my question doesn't take into ...
3
votes
1answer
825 views

Duplicate UNIQUE KEY error after ROLLBACK in MySQL

I have a table like this mysql> describe seudonimos; +--------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra ...
3
votes
1answer
75 views

Use of Trigger After a Transaction

I have a scenario where there is a transaction being executed to update a few tables. On one of these tables there is a trigger that accesses the data on those tables, but is only planned to be on ...
2
votes
1answer
427 views

Distributed - across multiple databases or servers - Transactions

I have some doubts related to transactions in SQL. I perform below steps. Connection 1 open for DB1 database Begin transaction for Connection 1 Run the commands on Connection 1 Connection 2 open for ...
2
votes
2answers
671 views

Partial rollback doesn't decrement trancount

Suppose I have an open SQL Server session, and do the following: begin tran insert into People (Id) values (1) select @@TRANCOUNT -- Prints 1 save transaction tt begin tran select @@TRANCOUNT ...
2
votes
2answers
144 views

Transaction Log Backups

I am working on backup+recovery scenario and I came across a situation where I could not figure out how I would have to deal with it. We have couple databases where sometimes there may not be any ...
2
votes
2answers
89 views

Error on DDL statement never invokes CATCH

When I try to close the SS Management Studio window below is the error I understand that DDL statements are implicit COMMIT and the below WARNING message is because BEGIN TRAN never COMMIT or ...
2
votes
3answers
164 views

Transaction Isolation level different threads massive number of Writes

I have a lot of threads that writing to DB some data in two tables. tbl_raw_data and tbl_parsed_data where tbl_parsed_data have foreign key to tbl_raw_data. I also need the writing to be superfast. ...
2
votes
2answers
696 views

Read Committed Isolation Level

Quote from docs: Read Committed is the default isolation level in PostgreSQL. When a transaction uses this isolation level, a SELECT query (without a FOR UPDATE/SHARE clause) sees only data ...
2
votes
1answer
830 views

Rollback generate scripts

During the development of applications users often want to add new modules, business rules etc. So I've often used SQL Server 2008 option to create scripts from the development DB to generate new ...
2
votes
1answer
70 views

SSIS Transaction Level for Single Task

I have 3 tasks in a SSIS package. If the final task fails I only want that task to rollback. Do I just set that task to REQUIRED? Or leave it as SUPPORTED? Thanks!
2
votes
2answers
1k views

mysql transaction vs lock

I have a question regarding MySQL locking and transaction management. My Question is ..does mysql takes lock on the the tuple/table on which I am doing select/update in a transaction ?
2
votes
2answers
274 views

Separate table for 'scheduled payments' and 'completed payments'?

In a previous financial payments related database I'd worked on, it just had one large transactions table. We would insert scheduled transactions for the future, and when that time came, we'd execute ...
2
votes
1answer
56 views

Why - read but not update ops fail in case of lacking space in tempdb database?

MSDN online article "Choosing Row Versioning-based Isolation Levels" states: "tempdb must have enough disk space for the version store. If there are very long-running transactions, all the ...
2
votes
2answers
88 views

Read committed snapshot:Finding lock on table

IF update command is run on a table and commit is pending then another user cannot be able to update the table. Is there any query to get the user or PC which caused lock on the table.
2
votes
1answer
160 views

Transaction level difference between using a large IN filter VS. “BEGIN TRAN/COMMIT”

I would like to delete about 100,000 records with minimal server overhead. I've had a few nagging questions I haven't been able to test properly so I figured I'd ask some experts here. Which would ...
2
votes
1answer
83 views

How to find the active transactions in VLF?

Is there a way to find the T-SQL statement that is part of the active transaction that is blocking the reuse of the transaction log file VLFs? I am performing tlog backups on every 15 minutes, but ...
2
votes
1answer
38 views

MySQL InnoDB transactions with lot of delete instructions

We have a process that runs frequently and does the following steps on a mysql database of type innodb. SET autocommit = 0; DELETE FROM table WHERE category='something'; -- every 300 rows or whatever ...
2
votes
1answer
124 views

What is the standard isolation level for a db used for money data?

When using a database server for monetary/financial data, I can only assume that using transactions is mandatory. But what I am not sure is which is usually the isolation level? Is a READ-REPEATABLE ...
2
votes
1answer
385 views

Using the auto increment id from one query as the fk of the next insert

I'm not sure if I'm overcomplicating this or whether I can't see the wood for the trees. I have two tables, both have auto incrementing ID's. Table 1: ID - auto field - int other field - varchar ...
2
votes
2answers
748 views

How to rollback the identity seed after deadlock

Now that's an approximate sequence of operations Im performing: SET IDENTITY_INSERT <table-name> ON; INSERT SOMETHING to <table-name> with explicitly specifyed id DECLARE @oldID bigint ...
2
votes
1answer
229 views

Postgresql Two-phase Commit Transaction Identifier

Is it possible to perform a two-phase commit in Postgresql when the databases exist on the same server? As a simple test, I would connect to the first database and run: BEGIN; PREPARE TRANSACTION ...
2
votes
1answer
944 views

In InnoDB, does a Transaction imply any implicit locking of a table?

The Isolation level is REPEATABLE_READ. The logic is as under: Transaction begins Read data from Table A If (Table A has Any Data) End Transaction and exit If Table A has No Data, Proceed further ...
2
votes
1answer
1k views

Transaction Lock Timeouts When Updating a Row

I'm trying to determine the cause of an InnoDB transaction lock timeout. This one is very strange in that it appears to be a deadlock error instead - nonetheless, here's the output of my slow log ...
2
votes
1answer
1k views

Save Transaction: Is the name local to the stored procedure or not?

Some foot shooting just occured on a production server. I fixed the problem, but I'm now rather confused. There is a subset of stored procedures that preserve state on failure. That is, if the ...
2
votes
1answer
422 views

SQL Server Logon Trigger causing problems with the Distributed Transaction Coordinator

I've created the following simple logon trigger on my SQL Server 2005: CREATE TRIGGER LOGON_RESTRICTION ON ALL SERVER FOR LOGON AS BEGIN PRINT 'Hello World' END GO Which seems to be working fine ...
2
votes
1answer
40 views

Reading from subscriber during Transactional Replication

When using Transaction Replication, are there any restrictions around selecting data from the subscriber whilst transactions are being applied to it? i.e. can selects be made against tables to which ...
2
votes
1answer
38 views

MySQL behaviour on connection failure

My MySQL database is hosted on a different machine from my application system, which issues queries to the MySQL database remotely. If for some reason the network connection between the two machines ...
2
votes
1answer
237 views

How to scale MySQL transaction wrapped queries?

So I'm working on an app that wraps many of it's SQL queries in a transaction for it's SQL usage and I'm trying to think of some ways to scale the DB layer up.... Up until now we've been throwing ...
2
votes
1answer
548 views

Add user to Server Role in a transaction

Situation One webapp responsible for creating other webapps using SQL Server 2008 R2 Stored Procedures. One of the steps in creating another webapp is creating a new database, a login/user, assigning ...
2
votes
1answer
315 views

select for update gives error on indexed column

here is my code: mysql_query("start transaction"); $q="select max(id) from test for update"; $r=mysql_query($q); $row=mysql_fetch_array($r); for ($k=0;$k<100000;$k++) { $q="insert into test values ...
2
votes
1answer
422 views

Locking, deadlocking workaround while process data at the same table

My database has a table: tableX. Task1 makes intensive INSERTs (1000 records per minute) as records to process and very seldom UPDATEs (1-2 records per minute) as records to recalculate. At the ...
2
votes
0answers
61 views

Phantom vs. Non-repeatable read [on hold]

In Martin Fowler's book 'Patterns of Enterprise Application Architecture' on page 74, the authors say It's easier for databases to spot unrepeatable reads than phantoms, so the repeatable read ...
2
votes
0answers
279 views

Postgresql trigger - get all changed rows after transaction

I have invoice_item table and invoice table. I need to check total price of Order after each transaction. Since I still want to be able to change things, I need trigger after each transaction. CREATE ...
2
votes
0answers
102 views

Why does MySQL force the tradeoff between swallowing errors and leaking commits in transactions?

Referring specifically to the choice of whether to use DECLARE EXIT HANDLER FOR SQLEXCEPTION. If you use it, you swallow errors and there doesn't seem to be any way to access them programmatically, ...
2
votes
1answer
746 views

unique constraint violated (ORA-00001) in concurrent insert [closed]

I have a procedure that is called from concurrent transactions: //Some actions here INSERT INTO table1 (table1_id, table1_val1, table1_val2, table1_val3) VALUES ...