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

learn more… | top users | synonyms

7
votes
2answers
436 views

ROLLBACK doesn't work after INSERT INTO newly created destination table

I am working on PHP-script which imports CSV file (customers.csv) into MySQL table (customers). Before inserting contents of CSV-file into the mysql table I am first backing up the original customers ...
1
vote
2answers
48 views

Query data before a commit transaction

My understanding is that, in a MS SQL Server Management Studio window, after doing a "begin transaction" and then making some changes like add data to a table , you can only query that table and those ...
1
vote
0answers
29 views

Make the SQL Database manage CRUD operations

Is it possible to have a client app send a transaction of INSERTs to a table. Then have the table triggered somehow to run through the inserts and either INSERT the row or UPDATE an existing row? If ...
0
votes
1answer
44 views

How do I report progress of a long running transaction in MySQL?

I have this transaction in a stored procedure: ... START TRANSACTION; WHILE @Codes > 0 DO EXECUTE qry_rand_stmt; EXECUTE qry_insert_stmt; SET @Codes = @Codes - ROW_COUNT(); END ...
1
vote
1answer
86 views

Why doesn't SQL Server rollback the identity_insert state?

I have a large script for setting up a new database that does lots of identity inserts on many tables; while I'm working on extending it I quite often have transient errors due to it being a work in ...
0
votes
2answers
49 views

How can I in Oracle discover the average number of rows affected per transaction? [closed]

Our Oracle server is a big cluster of three very large individual instances. I want to try to understand the average number of rows per transaction. How can I go about doing this?
2
votes
2answers
63 views

Is there a way to “SELECT and TRUNCATE” without losing new data?

I have an application that writes raw log files to a table called new in a PostgreSQL database -- lots of rows and lots of duplicate data. Via a cron job running every minute, I normalize the raw data ...
0
votes
1answer
54 views

How to determine which statement is blocking an update in SQL Server

We have an UPDATE statement running in a large J2EE application that is getting blocked by some other statement. The update is using the primary key, so it should affect only one row. The isolation ...
0
votes
0answers
17 views

MySQL JDBC Driver puts transaction in read only. Why?

Using the MySQL JDBC connector with a MySQL 5.6.22 data source defaults the transaction mode to read only. Anyone know why?
3
votes
1answer
52 views

Rollback on \include error in psql

I'm trying to execute an sql script file in a single transation. The file contains includes for some other scripts which in my example create some tables. It looks like this: \include ...
2
votes
1answer
106 views

transactions and Oracle dblink

I have 2 dbs, let's call them writedb and readdb. Readdb has a dblink to writedb and a view to a table over that dblink. I do an update on writedb to that table which view points to and then read ...
3
votes
1answer
89 views

Effects of turning the transaction safety OFF

I am considering to turn off the transaction safety to maximize the performance. Before that i am interested in finding the current status of transaction safety. Is there any command that we can get ...
2
votes
0answers
82 views

Phantom vs. Non-repeatable read [closed]

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 ...
0
votes
0answers
6 views

Percona XtraDB and innodb_flush_log_at_trx_commit=2

I'm running Percona XtraDB on a three nodes cluster. Each one has this piece of configuration: innodb_flush_log_at_trx_commit=2 sync_binlog=0 I perfectly know that this is not the safest ...
0
votes
0answers
62 views

Many transactions are getting stuck in the “cleaning up” state - what could cause that?

I'm trying to track down a locking issue, and found via SHOW PROCESSLIST that when the queries in question are waiting for locks and timing out, there are no other queries active in the process list. ...
1
vote
1answer
41 views

MySQL/Percona Server is taking long and I'd prefer to skip all this waiting even if it means data loss

Originally posted to https://www.facebook.com/groups/GNUAndLinux/permalink/10155020539805019/ but no replies received. I run Percona Server 5.6.21-70.0-688 x64 on Ubuntu Server 12.04 LTS x64, 64GB ...
1
vote
2answers
89 views

Writing a large update/delete script that doesn't provoke locks

While running a T-SQL script that is performing massive amounts of deletes, I had the unpleasant experience of causing some sort of lock contention that prevented inserts of data in some tables for ...
2
votes
2answers
104 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 ...
1
vote
0answers
20 views

MySQL - Transactions and causality

I'm new to MySQL's LOCK and TRANSACTION and have a question about different isolation levels of a transaction: Given two transactions executing the same code (auto-commit is false): (1) get data of ...
1
vote
1answer
49 views

Why “SET LOCAL statement_timeout” does not work as expected with PostgreSQL functions?

My understanding is that PostgreSQL functions are executed similar to a transaction. However, when I tried to "SET LOCAL statement_timeout" within a function, it did not work. Here's how it works ...
4
votes
2answers
466 views

How to rollback when 3 stored procedures are started from one stored procedure

I have a stored procedure that only executes 3 stored procedures inside them. I am only using 1 parameter to store if the master SP is successful. If the first stored procedure works fine in the ...
0
votes
0answers
65 views

How can I lock an INSERT operation

OK, I know the title is confusing, let me rephrase with a description of the problem. The environment is SQL Server 2012. I have a table with Customers Id 1 And a table with their transactions ...
1
vote
2answers
122 views

Truncating and inserting in a safe transaction on MySQL

I am trying to TRUNCATE and INSERT data into a MySQL InnoDB table. I wanted to make sure that if any statement in the transaction failed, the data will be untouched. I can't have an empty or ...
1
vote
1answer
73 views

Deadlock detected on CREATE TABLE in PostgreSQL

I'm creating multiple tables asynchronously when I get deadlock detected. Both tables (Table A & B) have FK to one table (Table C) and I get deadlock on relation to Table C. Scripts CREATE TABLE ...
3
votes
2answers
85 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 ...
2
votes
3answers
176 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. ...
1
vote
2answers
235 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 ...
-2
votes
2answers
48 views

Explicit transactions for each DML query are necessary or not? [closed]

My question is simple: Which mechanism should I use in databases to ensure that if device shuts down in middle, the process is unified. transactions?
1
vote
3answers
42 views

Study material for Advanced Databases module inc SQL, Relational Model, ERM & TM [closed]

For my next module in my masters, I am planning on studying Advanced Databases. (Long story short, the masters is designed for people with NON-CS degrees, so I have never studied databases, therefore ...
0
votes
2answers
38 views

Best way to update the content of large lookups tables

In our application we have a about 20 tables that contain lookup information (read only data that the application doesn't modify). Nightly we'd like to update this information by deleting all the ...
1
vote
1answer
24 views

Does trigger “see” all changes done in the transaction it was fired from?

Let me elaborate on the title. Suppose we have two tables, t1 and t2. t2 has a trigger t2_trgon it. Same transaction inserts/updates data in t1, then does insert/update in t2. t2_trg is fired and ...
0
votes
1answer
20 views

Oracle dataguard - Redo apply

In a low bandwidth environment when a transaction commits that generates a large amount of redo data will cause other independent transactions to slow down. This is because the redo data of small ...
1
vote
2answers
205 views

Using MySQL/PHP PDO, is there a 'simulate' or dry run option?

Does MySQL (or, ideally, PHP PDO) have some way of simulating transactions without actually committing them? (Can I get MySQL or PDO to act as if data is inserted/deleted/updated, without actually ...
2
votes
2answers
289 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
115 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 ...
1
vote
1answer
57 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
156 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 ...
4
votes
1answer
619 views

Sql Server - Select while insert on another transaction gives unexpected results

I stumbled at situation that changes my knowledge about transactions and locking fundamentally (I don't know much though), and I need help to understand it. Let's say I have a table like this: ...
1
vote
1answer
44 views

Transaction Rollback

I have a purging process that runs to delete old data from table to avoid table growth. My delete statement would be a simple delete based on join something like below Delete FROM TABLE_A A JOIN ...
6
votes
1answer
99 views

In Postgres, how to get a list of current defined savepoint?

I'm using postgres SAVEPOINT which create a new savepoint within the current transaction, and would like to display the list of currently defined savepoint in a connection. To be more precise: I'd ...
1
vote
1answer
312 views

PostgreSQL 9.3: Primary key violated by a trigger INSERT

My problem Consider a table t with many frequent updates from users, from which only the last few are relevant. In order to keep the table size reasonable, whenever a new row is inserted old rows ...
0
votes
2answers
27 views

transaction not processing?

I'm trying to test out an update prior to running it on our production server, however when running my transaction in sqlplus, I don't seem to be able to commit the transaction or get any data back ...
0
votes
1answer
94 views

Is is possible to handle a UserAbort or ClientTimeOut within a Stored Procedure?

An example: create proc [dbo].[usp_SlowProc] as begin try set xact_abort on; -- Ensures rollback on some errors that bypass the try/catch mechanism raiserror('Waiting...', 0, 1) ...
1
vote
0answers
76 views

firebird update very slow

I am writing a stored procedure for Firebird 2.5 in which I sometimes need to update a single row in a data table more than once in the same transaction. However, I have noticed that the performance ...
3
votes
1answer
88 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 ...
4
votes
2answers
973 views

Is it possible for SQL Server to miss rows on a select * where PK = “value” query

We have just migrated an application to an SQL Server 2008 R2 from SQL Server 2005 and moved from a physical to virtual hosting environment. I have a very strange error in our application and I was ...
1
vote
1answer
94 views

How to log within a stored procedure that is encapsulated within a transaction

I have a stored procedure, let us call it sp_A which calls other stored procedures, let us call them sp_B and sp_C. For the sake of consistency an XA TRANSATCION is started within sp_A. What I ...
1
vote
1answer
124 views

Postgres : Executing SELECT within a transaction does not return most recent rows

Given a table layout that looks like this inv_items ========= * id bigserial sku character varying(22) name character varying(32) ... inv_items_stock =============== item_id bigint (inv_items) qty ...
2
votes
1answer
40 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 ...
1
vote
1answer
38 views

SQL Server: affect other transactions?

I'd be surprised if this existed, but will ask just in case. Is there some mechanism which would allow one transaction to affect another (outside) transaction? For example: BEGIN TRY BEGIN TRAN ...