How to ensure your sql transaction are safe?

Question

Maybe this question is misleading but if you can answer it then do so.

I remember posting many SQL questions and once in a while someone would say my transaction isnt safe. I can never remember the case. What are they?

IIRC its when i do reads at the beginning of a table then write later and apparently other transactions can write even tho your not finished? I dont remember how to correct them either, in that case i am suppose to do dummy writers? But i am positive this paragraph is COMPLETELY wrong so hence the question. Are are cases where you transactions may be unsafe and what can i do as good practice to make them safe?

I mostly use SQLite and sometimes use mysql

Answer 1

Rather than transactions, you may be talking about the broader concept of locking, i.e. managing an orderly concurrent access to the database.
The two concepts are independent but often used together (in particular transaction tend to imply some form of locking but it is also possible to introduce locks without any transaction). Most DBMS which provide support for transactions offer specific features such as Transaction Isolation Levels which tie the two concepts together.

The purpose of transactions is to handle a series of changes to the database as ONE single operation. In this fashion if somehow one of the steps associated with these changes were to fail, SQL would be able to "roll back" all changes made as part of the transaction. This is important to ensure referential integrity of the database. For example to prevent adding say a new Employee record to the database, but failing to relate this record in the Payroll table. ;-)

The purpose of Locking, i.e. of specifying a particular list of temporary rules which will prevent other users of the DBMS to access (for writing, and possibly for reading) parts of the database associated with particular CRUD operations (ie. operations that changes the database content). The idea is to prevent say Alice and Bob to try and decrement the inventory level of soda for the office at the same time. They (the SQL statements they run actually), could feasibly read the current amount of a given soda brand, decrement this value by the amount they each just took and store the new amount back into the database. Since they both read the same original value, the change done by the first of these two SQL statement to complete will be overwritten by the last one.

Locks (or Transaction Isolation Levels) can be tricky to setup because of their impact on the SQL server's throughput. Basically may cause a particular query to stop, and wait till the lock is released. There are different kinds of locks: row level, table level etc. and also write-lock or read_and_write_lock. One can imagine that if all parties using a SQL server were to lock at table level for any query they were running, the amount of users that can be served would drop dramatically. In some cases, deadlock occur, that is for example when a particular SQL statement locks a particular section of a table, but then waits for another query to complete and remove its lock on another table. If the other query itself is waiting for the first query to remove its lock on the first table, we have a circular situation that would last indefinitvely (and prevent yet other queries to access data associated with these two locks!)

More info: For MySql
Note: not all storage engines in MySq1 support transactions.
Transaction Isolation Levels
Locks with InnoDB store
For SqlLite
SqlLite transaction syntax
With SqlLite the transaction level is defined by the deferred, immediate, and exclusive keywords.