Separate SQL transaction per request?

<p>In a web server I&#39;m working on, every request may issue a couple of SQL queries.</p> <p>I&#39;m thinking, why not have a separate transaction per request to ensure any changes get rolled back in case the handling of the request failed?</p> <p>For instance, in a user registration, I may insert a record to table <em>users</em> and for whatever reason, I insert his email separately to table <em>user_emails</em>. Now if anything goes wrong after the first INSERT and I return with an error, since I have a transaction per request and my middleware has access to the request&#39;s transaction, it would rollback the transaction and in no way a user would exist without his email or an email record in table <em>user_emails</em>.</p> <p>Also, this might improve performance in requests that issue a lot of UPDATEs/INSERTs.</p> <p>What are your thoughts about this?</p> <hr/>**评论：**<br/><br/>xtralarge65: <pre><p>This doesn&#39;t seem to be a go language issue. This is a best practice when using transactional databases. Yes, you should use database transactions at the proper place so that your database does not become inconsistent. It might be at the request level, it might be at another level.</p></pre>zippoxer: <pre><p>Yes, you are correct. But I&#39;m hoping to discover how people handle this in Go. After reading <a href="https://medium.com/@benbjohnson/standard-package-layout-7cdbc8391fc1#.rugppc1ml" rel="nofollow">Ben Johnson&#39;s Standard Package Layout</a>, I wonder how people do database transactions in this or in other common Go server models.</p></pre>neoasterisk: <pre><p>Transactions do not have much to do with project layout. You just use transactions in your database code:</p> <ul> <li><a href="http://go-database-sql.org/modifying.html" rel="nofollow">http://go-database-sql.org/modifying.html</a></li> <li><a href="https://github.com/golang/go/wiki/SQLInterface" rel="nofollow">https://github.com/golang/go/wiki/SQLInterface</a></li> </ul></pre>residentbio: <pre><p>I created a postgres package, where I implemented the CRUD methods. I have a domain package where I setup my main structures(User, Invoice, ETC). Each in his own file, where I also put an interface UserStore(for example) with the methods my postgres should implement. Finally I have a package router, where all my handlers are. I create a local struct: User and an interface lets says, UserReader. The interface defines methods that need to be injected to my router. Finally, my main wraps everything up, using a struct named Env defined at the domain level where I define members that need to be injected, so that my router init functions recieves and env struct as a parameter.</p> <p>I hope it makes sense. </p></pre>kaeshiwaza: <pre><p>I create one transaction by request that i pass to each handlers. If the handler panic or return an error i rollback if not i commit.</p> <p>When you speak about email, it&#39;s good to do the same when you send email in an handler. I mean to just put the mail in a request queue and really send it only if the handler was going to the end without error.</p></pre>