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

learn more… | top users | synonyms

0
votes
1answer
1k views

Lock wait time out exceed restart transaction

I had got error on the lock wait time out so below I got 3 samples taken. One I took before the increase innodb_lock_wait_timeout to 120. So is there anything else I must tweak based on the logs ...
2
votes
2answers
645 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 ...
1
vote
0answers
34 views

Does SQL Server place shared locks on scanned records when using REPEATABLE READ [duplicate]

Assume a SQLCmd session which is using transaction isolation level REPEATABLE READ. In this session I start a transaction and execute an UPDATE statement with a WHERE clause on a non indexed column. ...
0
votes
1answer
137 views

How do I stop a query from running if the row is locked?

I have a section of code that locks the database, updates it and then confirms. This is all working fine, if another user attempts to update the same row they cannot and their changes are discarded. ...
0
votes
1answer
826 views

postgres deadlock without explicit locking

I use PostgreSQL 9.2, and I do not use explicit locking anywhere, neither LOCK statement nor SELECT ... FOR UPDATE. However, recently I got ERROR: 40P01: deadlock detected. The query where deadlock ...
0
votes
1answer
257 views

Database atomic operations implementation

The question is about queries that are not wrapped in 'begin-commit' block, but about plain inserts and updates that are atomic in PostgreSQL, MySQL (innodb engine at least). So how is this ...
4
votes
2answers
1k views

Transactions, references and how to enforce double entry bookkeeping? (PG)

Double entry bookkeeping is a set of rules for recording financial information in a financial accounting system in which every transaction or event changes at least two different nominal ...
2
votes
1answer
715 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 ...
1
vote
1answer
113 views

Does PostgreSQL optimize queries in transaction?

In my app I need to make big imports from user files, and to achieve that all records are updated/created I do it inside transaction. But before it I need to update massive, already existing in DB, ...
0
votes
1answer
530 views

Query notifications and connection options

I'm investigating a problem with query notifications. The platform is SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Standard Edition. .NET errors show a problem reported from SqlNotificationInfo ...
11
votes
1answer
4k views

Trigger in combination with transaction

Suppose we have the following situation: We have a table (let's say Table_A), wich has a trigger on INSERT. The trigger job is to update some rows in table_B based on the inserted values in table_A. ...
0
votes
0answers
47 views

Examples of a database with no consistency measures that ensures consistency through schema alone

To clarify, the database can be any type RDBMS, NoSQL/BigTable. However, it must not employ any usual consistency measures such as locks, or synchronization, but the inconsistency-invincibility must ...
0
votes
0answers
97 views

What would this non-'unique index with a unique search condition` query lock?

I am not sure about this part of the transaction manual For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement ...
1
vote
2answers
109 views

Do I need a locking read?

I am inserting rows in a table, based on a value and on the existence of a row, which I get from SELECT statements. This function can be run concurrently, so I need to do some extra obviously. The ...
2
votes
1answer
224 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 ...
1
vote
2answers
296 views

Are MySQL transactions under-utilised?

I have a vague understanding of transactions that it means you can help the server understand that a series of queries are related. Scenario : Suppose I have a series of queries which creates a user ...
0
votes
1answer
844 views

Does TRUNCATE TABLE affects isolation level in Sql Server?

As I am working on the project which has SQL SERVER as a database....I'm maintaining isolation level in my project where I enabled snapshot isolation level for my project.... As I have to make some ...
1
vote
1answer
116 views

Does a transaction cover the subroutines?

When I begin transaction and exec a stored procedure, does that transaction cover the changes made by the stored procedure I executed? Would a rollback transaction cancel the changes made by the ...
1
vote
1answer
433 views

Commit Insert but Hold Lock

I have implemented what is effectively a reliable queue through the following: CREATE TABLE Queue (MessageID UNIQUEIDENTIFIER, Message varchar(255)) --Enqueue Command DECLARE @MessageID ...
2
votes
1answer
232 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 ...
1
vote
1answer
2k views

SQL Server - how transactions and transaction log work (simplified)

I have a theory on how transactions work, but I would like if someone could verify it or correct some points if possible. Let's consider we have a database with full recovery model. Now, everything ...
3
votes
1answer
235 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 ...
4
votes
2answers
5k views

Transaction and Try-catch in SQL Server Job

We have DML operations in each step of a SQL Server job. To ensure the update/insert will be rolled back in case something goes wrong, I have wrapped the data modifications of each step in TRY CATCH ...
4
votes
2answers
284 views

Delete all rows within transaction: what would happen to other transactions?

I currently have a function in PostgreSQL that creates a temp table, fills it, performs some tricky selects and returns results. postgresql's temp tables are local to functions, so this helps me avoid ...
6
votes
1answer
3k views

Deleting MySQL table with pending transactions

Is there a way to delete an InnoDB table or database with pending transactions in MySQL (preferably on file system level)? What happened: I use MySQL 5.5.28 and ran LOAD DATA INFILE… to import a ...
10
votes
1answer
5k views

SQL query for combinations without repetition

I need a query which can be used in (or as) a function and retrieves all combinations of n values. And I need all combinations of length k where k = 1..n. Extended sample input and result so input ...
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 ...
0
votes
1answer
607 views

jdbc: does setAutoCommit(true) commits past executions?

I would like to execute some statements, and commit the entire batch at the end. I've tried the following approach: connection.setAutoCommit(false); // Don't commit for each statement ...
2
votes
2answers
6k views

Table Lock on INSERT - How to optimize

we have a few long running procedures in the database, that cause an instant Table Lock on our main text storage table, which causes all other requests of the web app to wait (because the table is ...
2
votes
1answer
518 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 ...
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 ...
2
votes
1answer
816 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 ...
0
votes
1answer
420 views

MYSQL MyISAM data recovery implementation

I am creating integration into eshop script, shop is using MyISAM tables and I need to implement something like transactions in InnoDB, what I actually need is full recovery of data after modifying or ...
1
vote
0answers
102 views

migrations within transactions

I have a bash script to run migrations on our MySQL databases. Is it possible to wrap migrations within a single transaction when running multiple migration scripts from the command line? ...
1
vote
1answer
735 views

Does TRANSACTION/COMMIT/ROLLBACK only work on certain MySQL engines such as InnoDB?

Are transaction functions only relevant for particular database engines on MySQL, or are transactions supported under all MySQL engines, including MyISAM and InnoDB? someone says here that ...
6
votes
3answers
2k views

Can I change table structure in a transaction and then roll it back if there is an error?

I have some ALTER TABLE statements that I am running. Not all of them work (they are the result of running SQL Data Compare) and I want to group them in some transactions and roll back the statements ...
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 ...
0
votes
0answers
100 views

Being careful about additional records being added in a duplicate record merge?

We have a multi-user MS-Access 2003 database. Connecting to it using SQuirrel SQL, and the JDBC/ODBC bridge, I've managed to eek transactions of some sort out of JET by turning off autocommit. I've ...
1
vote
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 ?
3
votes
1answer
314 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
2answers
3k views

Will a delete then insert within a transaction cascade?

I had a question about what the expected and actual behaviour for the following scenario is. The scenario is one where a database table ([table1]) is cleared and reloaded every day. The table has an ...
0
votes
1answer
422 views

Can I rollback/abort a transaction if no rows were inserted?

I have a transaction in which I want to do something like this: START TRANSACTION; INSERT INTO table1 (...) SELECT ... FROM table 2; ... if select count(*) from table1 returns 0 abort/rollback ... ...
9
votes
1answer
5k 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 ...
10
votes
1answer
458 views

Disable explicit commits in JDBC, detect them in SQL, or put the database in a readonly state

Background: I'm working on http://sqlfiddle.com (my site), and am trying to prevent one avenue of abuse possible there. I'm hoping that by asking about a problem I'm currently addressing, I don't ...
2
votes
1answer
902 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 ...
1
vote
1answer
176 views

timestamp based concurrency

I am wondering if someone could help me out with a question I have about time stamp based concurrency. I found the following practice problem on a website It basically asks which of the following ...
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

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
3answers
727 views

Why am i getting deadlocks with 1 read and 1 write thread in SQL

if anyone could please help me understand why the following transactions deadlock? I am providing the 2 transactions and the indexes for each table: Transaction 1: SELECT blockId, blockData, ...
4
votes
2answers
382 views

How do I query only for committed data while avoid a deadlock

I have a system writing batches of queries as transactions (to maintain coherency), these can be sets of 30-100 queries per transaction. These are done on multiple tables At the same time there are ...