A newbie question about database transactions

Question

I know that database transactions are used to ensure all the statements in the transaction will be executed or none of them will.

But what about locking and thread safety, for example if I have an sproc which affects multiple tables and I use a transaction in that sproc, that sproc is called from different clients at the same time, will the transactions work in parallel or will they be queued? In other words, does using a transaction guarantee that it will 'lock' all other clients and cause them to wait until the transaction finishes?

Also are there any differences between database transactions in SQL Server and MySQL? I mean the way they work.

Answer 1

Depending on the DBMS but stored procedures aren't usually locked, rows in the database tables are locked and that's what prevents two stored procs from interfering with each other.

That has a finer granularity of locking so is less likely to cause contention.

Re differences between SQLServer and MySQL, I wouldn't know at the lower level - they may well do vastly different things. But they will be identical in terms of the SQL standard (otherwise they'd be pretty useless).

Answer 2

TStamper already provided a link to SQL Server vs MySQL, but if you interested in general transactional behavior check out these links:

http://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html http://en.wikipedia.org/wiki/Isolation_(database_systems)

The isolation level varies depending on the DBMS. Some DBMS systems let you configure the isolation level (higher isolation level usually means lower speed/throughput due to contention).