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

learn more… | top users | synonyms

1
vote
1answer
102 views

Print, transaction and cursor performance in Transact-sql

I'm having some performance issues when I run a script. I was reading about the problem with print and its performance, but I cannot figure out how to reduce the impact. The algorithm is O(n^2). The ...
2
votes
2answers
227 views

Do Nested Transactions commit? [on hold]

I am not happy with the Wikipedia article about nested transactions. I would not use the word "commit" for a nested transaction. For me "commit" means "durability". A nested transaction does not ...
2
votes
1answer
31 views

How to create test environment for lost transactions

I have a problem that I hope you can help me to solve. I'm using SQL Server 2008 R2. I need to create a test environment in which I'll be able to artificially 'create' lost transactions. Due to ...
3
votes
1answer
148 views

Is REPEATABLE READ the right choice for validation before modifying data?

In my database I have a Task table. This table has a number of fields that are foreign keys to other tables. There are also a few link tables, that have Task on one end. When a new task is created ...
7
votes
1answer
153 views

Is there a performance difference in committing and rolling back a read-only transaction?

I open a (repeatable read) transaction (BEGIN TRAN) to do some work on certain records. First thing I do is checking if the data I need to change is in the database. In some cases there will be and ...
1
vote
1answer
20 views

SQLAnywhere: Savepoint for ROLLBACK not found

What is the correct way on setting and rolling back to savepoint on SQLAnywhere. Having this snippet: begin transaction; SAVEPOINT spt_abc; insert into eins (pl1) values (5); SAVEPOINT spt_123; ...
16
votes
2answers
313 views

Transactional DDL workflow for MySQL

I was a little surprised to discover that DDL statements (alter table, create index etc) implicitly commit the current transaction in MySQL. Coming from MS SQL Server, the ability to do database ...
8
votes
1answer
204 views

How to terminate a suspended SQL Server transaction waiting for IO_COMPLETION?

We have a transaction that has been running for more than 5 hours. We are running out of disk space. The session has been killed but it is still waiting for IO_COMPLETION. Actually the wait_type just ...
0
votes
1answer
434 views

MySQL: Transaction and Deadlock

I have table "tbl_data", where have a lot of rows (3 - 5 million). I have JAVA (*.jar) module from which I INSERT/UPDATE data into this table. If I start *.jar file (for example, "run.jar") and after ...
3
votes
1answer
254 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 ...
1
vote
0answers
17 views

MySQL sizing tool for Hardware [closed]

Is there any sizing tool for hardware? I'm not sure what kind of servers we need to offer to my customer like CPU, memory, Disk or number of servers? What kind of questions do we need to ask our ...
21
votes
3answers
39k views

How to find out who deleted some data SQL Server

My boss had a query from a customer yesterday asking how they could find out who deleted some data in their SQL Server database (it is the express edition if that matters). I thought this could be ...
1
vote
0answers
20 views

Does the `Tomcat JDBC Connection Pool` call `rollback` when returning a Connection to the pool? [migrated]

I’ve heard it said that a good JDBC connection pool should call rollback when it returns a connection back to the pool. The purpose is to resolve any possibly still pending transaction. Does the ...
7
votes
2answers
5k views

What is the “Chaos” Isolation level and when should it be used?

ADO.NET documentation shows the possibility of setting the transaction level for a SQL transaction to Chaos. It sounds unpleasant, but if the feature is there, presumably it has some legitimate use. ...
4
votes
1answer
37 views

Mysql transaction READ REPEATABLE and READ COMMITTED read view [closed]

I've come across something strage using Amazon RDS (MySQL 5.6.23). I had issues when concurrent transactions were locking and then calculating a value from multiple tables. The following is a quick ...
11
votes
1answer
183 views

MySQL Commited data not seen to select query

Context: The framework used is Spring and all queries are run with JdbcTemplate. Mysql Server version is 5.6.19. The table is an InnoDB table and defaults like auto commit and isolation level ...
1
vote
1answer
52 views

MySQL SELECT blocking an INSERT?

Is it possible for a SELECT query to block INSERT / ALTER queries? I have the following (taken from ENGINE INNODB STATUS) on my slave database: ---TRANSACTION 26DA7060, not started mysql tables in ...
1
vote
0answers
38 views

Will longer DB transactions make calls to other DB wait?

I have a fundamental question but unable to find precise information online. I opened up a DB transaction (SQL Server) and making multiple inserts, updates, delete etc; the entire process takes about ...
1
vote
1answer
37 views

What is the correct implementation of handling concurrent SQL inserts while ensuring a non-primary key column (i.e. wid) unique and auto-incremental?

What is the correct implementation of handling concurrent SQL inserts while ensuring a non-primary key column (i.e. wid) unique and auto-incremental? Note: There was a design constraint that led this ...
0
votes
1answer
22 views

PostgreSQL visibility of records changes from other transaction inside the current one

I have a table "tst" which has a "status" column using which we process records in that table. Once the records are processed we set the status to 'Y'; #\d tst Table "core.tst" ...
0
votes
1answer
18 views

How to name a new table storing location at which transaction occurred [closed]

My client wants to begin collecting data about where transactions occurred. We have a list of their events and locations where payments might be received, I'm just stumped on what to call the table ...
1
vote
1answer
24 views

SSIS Transaction Scope

My question is in regards to the scope of a transaction when using SSIS transactions. If I have a sequence container and it is marked as "transaction required" and I have tasks in that sequence ...
2
votes
2answers
46 views

CLR/C# equivalent to XACT_STATE()

I am porting a set of T-SQL stored procedures to C#/CLR. In the T-SQL, we use XACT_STATE() to determine if an error requires rolling back to a savepoint or rolling back the transaction entirely. I ...
1
vote
1answer
88 views

SQL Server Log Shipping Randomly Stops Copying and Restoring

I'm fairly new to Log Shipping but have configured a few with no issues, however I have a server sitting on SQL 2008 R2 with 6 databases on there, all 6 of these are supposed to be log shipping. The ...
5
votes
3answers
3k views

Using MySQL triggers or transactions?

I want to ask your opinion in using MySQL triggers or transactions in a website. Actually I have a history payment table with - UserId | OperationId | Comment | Credits | Sign (debit or credit). So ...
2
votes
2answers
122 views

Modeling invoices and orders

I have a problem in modeling a database that contains orders and invoices. Actually it's more of a problem from a business point of view, but it's related to modeling the database nonetheless. Model ...
2
votes
0answers
49 views

Trigger/Event that fires when a Transaction finishes

I have a query that pulls from about 15 different tables. I am looking to materialize this into a table that stores it in xml/json. (To improve performance.) The problem I have is that these tables ...
2
votes
2answers
70 views

Locking issues under transaction on Postgres

I recently got a "Out of shared memory - You might need to increase max_locks_per_transaction" error. This is a long running process where I import a bunch of CSV files after proper processing. I ...
1
vote
2answers
50 views

Rollback Committed transaction in SQL Server

Is it possible to rollback a committed transaction? I have deleted all the data in a table. The delete statement is inside Begin Transaction and I have committed the transaction with a commit ...
1
vote
0answers
67 views

Why are SELECT queries in sleeping status holding open transactions

Using sp_WhoIsActive, I see that I have a lot of processes in a "sleeping" status, several for hours. I am not sure of the cause and am still troubleshooting (timeout? bad code with uncommitted ...
2
votes
1answer
31 views

Persistant inserts in a UDF even if the function aborts

I have a rather complicated UDF (moves around a bunch of tables as well as creates a bunch of new ones) where multiple aborts may occur. Before every operation I would like to record the time when an ...
5
votes
2answers
118 views

Avoid unique violation in atomic transaction

Is possible to create atomic transaction in PostgreSQL? Consider I have table category with these rows: id|name --|--------- 1 |'tablets' 2 |'phones' And column name has unique constraint. If I ...
4
votes
2answers
433 views

What happens if a client connection is killed while holding a lock?

What happens if a client establishes a transaction, locks a row or table, and the client process crashes before it has a chance to rollback or commit the transaction? What, if anything, does SQL ...
1
vote
0answers
68 views

SQLite insertion with junction table

I need to write a transaction for SQLite, that adds a blog post and links it to content tags, and it seems to be a bit too complicated for me. Here's an example schema: CREATE TABLE blogpost( ...
2
votes
2answers
37 views

Keep local variable after rollback in mysql

I wish to keep local variable from stored procedure even if rollback occurres. In following procedure, i "register" numbers to users. The procedure takes user_id, value_from and value_to as ...
5
votes
3answers
811 views

Deadlock Error from SQL Server 2000 to SQL Server 2008

After migrating data from SQL Server 2000 to SQL Server 2008, when I am doing any a transaction in the table which find out more row on the database, that's gives me error. Transaction (Process ...
1
vote
1answer
53 views

parent child records - cascade deletes or transactions?

I'm about to create a parent and child table. In planning how to do CRUD operations on this data, I was wondering what is better design when it comes to deleting data. I'm using SQLite. I know ...
2
votes
1answer
97 views

Exclusively access a table and prevent other queries, even read queries

I'm stuck at setting transaction isolation level. Here's my scenario that happens in the application: Get unprocessed messages (using the IsProcessing flag) Set their IsProcessing to true (in RAM) ...
12
votes
1answer
7k views

MySQL transaction size - how big is too big?

I have an import process that runs every so often and I want it to be an 'all or nothing' kind of deal, aka: a transaction. There are many aspects, and the imports may yield anywhere between ...
0
votes
1answer
98 views

Simultaneous READ COMMITTED transactions updates a select for update columns

I have a READ COMMITTED transaction in which I am trying to update a row and increment a column value till it reaches a bucket value contained in another column. Please see the code below CREATE ...
0
votes
0answers
206 views

MySQL Transaction with rollback upon @@error

I have done several transactions with MySQL but mainly using php's libraries as the handle. Using php you would have something as: $pdo->beginTransaction(); $pdo->execute(' query1; ...
1
vote
1answer
173 views

MySQL: Why is inserting data with transactions faster then without them

I am using stored procedure to generate sequence consisting of prefix and number in given range (etc sequence "AB100", "AB101", ..., "AB199", "AB200") The stored procedure includes REPEAT loop, in ...
0
votes
1answer
82 views

How to execute two transactions concurrently in PostgreSQL?

I want to execute two transactions concurrently in pgAdmin: --The first transaction should modify the age of Paul BEGIN; DECLARE newage INTEGER; --the original age of Paul is 32 newage := Select age ...
1
vote
2answers
1k views

How to avoid locks by large transactions?

We have a large application running, that requires cleanup for performance reasons. This was however not forseen when designing the application. Basically, the delete is executed from a stored ...
1
vote
0answers
263 views

How to find the number of transactions per day or per hour in a SQL 2000 database

I am trying to find a query that I can run in order to find the number of transactions per day or per hour in a SQL 2000 database. The purpose is to get the number of hits on the database. I have ...
0
votes
1answer
20 views

Use a value for a Postgresql setting in a trigger

We're using a trigger to track changes in various database tables. This works fine. Now we want to be able to track the changes back to the web request that caused them. I'd like to be able to use ...
0
votes
0answers
21 views

Transaction block on coordinator

I tried to commad a procedure described here: http://files.postgres-xl.org/documentation/add-node-datanode.html We went as far as the third step, and everything was going perfect, but during the ...
0
votes
0answers
46 views

How to reset @@IDENTITY value inside a Sybase SP

Is there any way to reset the @@IDENTITY value inside a Sybase SP? I'm writing a SP which involves a transaction, so everytime I do an insert, to check data was correctly inserted, I assign ...
1
vote
2answers
2k views

Using transactions in sqlplus

I'm coming to oracle from postgresql. In postgresql, I can use the command-line tool, psql, to execute a delete or update in a transaction: dbpool.production=> begin; BEGIN ...
3
votes
2answers
359 views

How to minimize log operations in SQL Server to avoid “log full” error

I have a database in production which constantly get log file filled, its a data warehouse and have many jobs/queries running for obvious. Below is the error which I am getting Msg 9002, Level 17, ...