Transaction

Question

Transaction

pluck9909

SSC Rookie

Points: 32
More actions
April 23, 2011 at 2:46 am

#238992

Hi there,
Im confuse with transaction and Tables locking.
When a user perform a transaction on a table A,
can other user access table A?
If can then what is Two phase locking?
since i got the example:
Tran T Tran U
Begin;
read(a) Begin;
read(b) Wait..
Commit; read(a)
Commit;
why transaction U have to wait for transaction T?

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

Duplicate post, double click...

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Hi,

You may want to slightly rephrase your question.

Listed below is an article that describes two phase locking:

http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0901/en/html/dbugen9/00000159.htm

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

pluck9909 (4/23/2011)
When a user perform a transaction on a table A,
can other user access table A?

Depends what is done in the transaction and what locks are taken.

If can then what is Two phase locking?

I've heard of 2-phase commit, never of 2-phase locking before.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

Thank you for your point.

As far as I know two Phase locking does not exist in SQL Server but it does exist in Oracle.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

Please remove post.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

Welsh Corgi (4/23/2011)
As far as I know two Phase locking does not exist in SQL Server but it does exist in Oracle.

2PL is actually just a conceptual description of the locking process that divides it into two phases:

1- locks are acquired and no locks are released.

2- locks are released and no locks are acquired.

Two Phase Commit refers - at least in the Oracle world - to distributed transactions where one datatabase is an Oracle one while the second one is a non-Oracle one.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Ajay.Kedar Old Hand Points: 329 More actions · Answer 7

I guess the other user can not able to access Table A until second commit get executed.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Ajay.Kedar (4/25/2011)
I guess the other user can not able to access Table A until second commit get executed.

Depends what is done in the transaction and what locks are taken.

It's not as simple as they can or they can't access the table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pluck9909 SSC Rookie Points: 32 More actions · Answer 9

eh? sry maybe i missed out my points guys..

i just want to know:

if user A perform a transaction T, before the transaction T end.

can other user access the tables?

Transaction T maybe jus a simple transaction like:

Begin;

Update table Z

Commit;

will the transaction lock the table Z before commit?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

As I said earlier

GilaMonster (4/25/2011)
Depends what is done in the transaction and what locks are taken.
It's not as simple as they can or they can't access the table.

Locks from insert, update, delete are held until the end of the transaction, select as well if you're in repeatable read isolation or higher. Anything that's locked exclusively is unavailable until the end of the transaction.

Locks can be at row, page or table level, so it really does depend on what you're doing how much gets locked and with what mode (shared or exclusive) whether other sessions can access the objects or not.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 11

Excellent interpretation and explanation Gila Monster.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

jitendra.padhiyar SSCertifiable Points: 5824 More actions · Answer 12

We can read that data if we done with the below settings:

Set transaction isolation level read uncommitted

When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 13

Are you sure you want dirty reads?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

jitendra.padhiyar SSCertifiable Points: 5824 More actions · Answer 14

Am I wrong? I am newbie so I have just share what I know. If I am wrong than please correct me.