Tag Info

New answers tagged

4

what you would need is a so called "autonomous transaction" (a feature provided by oracle). at this point this is not possible in PostgreSQL yet. however, you can use savepoints: BEGIN; INSERT ... SAVEPOINT a; some error; ROLLBACK TO SAVEPOINT a; COMMIT; it is not entirely an autonomous transaction - but, it allows you got get "every transaction" right. ...


4

You could try it yourself: WARNING: there is already a transaction in progress It starts no new (sub)transaction as nested transactions are not implemented in PostgreSQL. (You may do some magic in a pl/pgsql function, for example, that mimics that behaviour, though.)


0

Bulk writes indeed are fast but will not be logged. If that is not what you want, consider procedure calls with table valued parameters. That way we have realised significant write improvements.


2

The only isolation level that influences writes is SNAPSHOT (and the READ_COMMITTED_SNAPSHOT). Snapshot isolation requires row versioning and row versioning requires extra writes. Read Understanding Row Versioning-Based Isolation Levels. Now about the 'super-fast' part of the question: the 'super-fast' option for INSERT is the bulk insert path. This ...


3

An ORM requires information about the first write (SCOPE_IDENTITY or such) to complete the second write. This means 2 (with an OUTPUT clause) or 3 database (with SELECT SCOPE_IDENTITY) calls in general in a client side transaction. No amount of tinkering with isolation levels will eliminate these 2 or 3 calls. If you want more performance, then the best ...


4

You simply don't use begin in sqlplus if you're just going to issue a series of SQL queries. You're in a transaction already. You can't really be outside of a transaction anyway. A few things to be careful with though: sqlplus does have an autocommit setting. It's off by default in modern versions, but just to make sure: SQL> show autocommit ...


1

Every time you have perform something atomically you should use transactions. Please understand that (depending on the language/framework/ORM), MySQL may be in auto-commit mode, which means that, functionally speaking, if you are using InnoDB something like this: INSERT INTO innodb_table VALUES (1, 2, 3); gets converted internally into: START ...


2

Normally in OLTP databases, transactions are so quick that we need not to place explicit transactions.Though internally MySQL take care of this and place implicit transaction. But if you think your scripts are long and it will take time to execute and if unrealistic/unwanted results can occur if failed in middle then you must apply explicit transactions.


0

Head First SQL was very helpful for me in terms of SQL. I also found Beginning Database Design From Novice to Professional to be very useful for understanding the Relational Model. The website SQL Zoo is also very useful for learning SQL. To be honest I wouldn't get so many books just on SQL - it isn't that hard, and Head First SQL explains it very ...


2

I found the course at Standford to be helpful - if you sign up, you can view past lectures on many of these topics. https://class.stanford.edu/courses/Engineering/db/2014_1/info


0

If you have a foreign key constraint from the data table that points to any of these 20 tables you will not be able to bulk delete from them. Obviously the application will not receive any results if it tries to read any of the lookup tables after the deletion and before the insertion of the new values. Will the application crash if this happens? Deleting ...


1

Assuming that the script contains only INSERTs, UPDATEs and DELETEs to the target table(s), and that the app never attempts row locks (SELECT ... FOR UPDATE/SHARE) or DML on those tables, then the script shouldn't affect the app except for the increased load on the DB server. When the script commits, the changes will instantly become visible to new ...


0

No. The operations go to DR site in exactly the same sequential order as they were performed on the primary site. You can only re-order operations at the level of your application code, the database engine cannot do this for you. The database engine cannot reorder changes to data because it needs to ensure that DR database is bit-to-bit identical to the ...


3

Regular trigger will see changes made in other tables within the same transaction. However, if you create the trigger (or other programmable object) with PRAGMA AUTONOMOUS_TRANSACTION, it will be executed within the scope of new transaction and won't be able to see "parent" transaction changes.



Top 50 recent answers are included