RE: ODS concurrency question
--0__=CTPObHElJp77K2n2a6YIFG0arZmvE5ZusMET9C3xrRGIiepUQnGlk26m Content-type: text/plain; charset=US-ASCII Thank you, Sujit. Indeed, I do have a unique index on that table. I have a serial column that must be unique. Would I be better off constraining the column and making the index non-unique? Thanks again. -- wanda spal@scotch.den.csci.csc.com (Sujit Pal) on 08/27/97 01:01:39 PM To: wbeck@sybase.com ("'Wanda Beck'") @ smtp cc: informix-list@rmy.emory.edu ("'informix-list@rmy.emory.edu'") @ smtp (bcc: Wanda Beck/SYBASE) Subject: RE: ODS concurrency question Wanda Maybe your example illustrates the "pessimistic locking" implemented by Informix as against the "optimistic locking" implemented by Oracle? Here Informix does not actually write the rows till the COMMIT WORK is encountered, so it will write the records for userA together (consecutive rowids) and records for userB together. Informix assumes that the INSERTs may be rolled back so it does not grab rowids when cacheing the inserted rows in buffer, whereas Oracle assumes that the rows may be committed and if it is rolled back it will have holes in the rowids. However this will only explain the sequence of rows in your table. The strange locking behavior may be because of the current ISOLATION LEVEL. If it is anything other than DIRTY READ, then during the INSERT by userB, any unique indexes on the table need to be read to ensure that uniqueness is not violated. This will hang up userB until userA commits the transaction he is in. I would suggest keeping transactions more atomic to ensure concurrency. However I am just guessing about the possible causes of the problem, so I may be wrong. HTH Sujit Pal ---------- From: Wanda Beck[SMTP:wbeck@sybase.com] Sent: Wednesday, August 27, 1997 7:16 AM To: informix-list@rmy.emory.edu Subject: ODS concurrency question Hi, All! I'm running ODS 7.21 on HP, and I'm trying to understand what my concurrent users can expect. I have a table on which I've set LOCK MODE to ROW. Users will be doing inserts. The behavior I'm seeing is that if one user is inserting into the table, **no** other user can insert until the first user is finished: USER1 USER2 ----------- ----------- BEGIN WORK insert A insert B insert C BEGIN WORK insert X insert Y COMMIT WORK insert D insert E insert F COMMIT WORK Depending on whether user2 does a SET LOCK MODE TO WAIT or not, s/he --0__=CTPObHElJp77K2n2a6YIFG0arZmvE5ZusMET9C3xrRGIiepUQnGlk26m Content-type: application/octet-stream; name="ATT01" Content-transfer-encoding: base64 eJ8+IisSAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAENgAQAAgAAAAIAAgABBJAG AKgCAAACAAAADQAAAAMAADAEAAAACwAPDgEAAAACAf8PAQAAAFUAAAAAAAAAgSsfpL6jEBmdbgDd AQ9UAgAAAQBpbmZvcm1peC1saXN0QHJteS5lbW9yeS5lZHUAU01UUABpbmZvcm1peC1saXN0QHJt eS5lbW9yeS5lZHUAAAAAHgACMAEAAAAFAAAAU01UUAAAAAAeAAMwAQAAABwAAABpbmZvcm1peC1s aXN0QHJteS5lbW9yeS5lZHUAAwAVDAIAAAACAfkPAQAAAFUAAAAAAAAAgSsfpL6jEBmdbgDdAQ9U AgAAAQBpbmZvcm1peC1saXN0QHJteS5lbW9yeS5lZHUAU01UUABpbmZvcm1peC1saXN0QHJteS5l bW9yeS5lZHUAAAAAAwD+DwYAAAAeAAEwAQAAAB4AAAAnaW5mb3JtaXgtbGlzdEBybXkuZW1vcnku ZWR1JwAAAAIBCzABAAAAIQAAAFNNVFA6SU5GT1JNSVgtTElTVEBSTVkuRU1PUlkuRURVAAAAAAMA ADkAAAAACwBAOgAAAAACAfYPAQAAAAQAAAAAAAAEDAAAAAMAADAFAAAACwAPDgAAAAACAf8PAQAA ADkAAAAAAAAAgSsfpL6jEBmdbgDdAQ9UAgAAAABXYW5kYSBCZWNrAFNNVFAAd2JlY2tAc3liYXNl LmNvbQAAAAAeAAIwAQAAAAUAAABTTVRQAAAAAB4AAzABAAAAEQAAAHdiZWNrQHN5YmFzZS5jb20A AAAAAwAVDAEAAAADAP4PBgAAAB4AATABAAAADQAAACdXYW5kYSBCZWNrJwAAAAACAQswAQAAABYA AABTTVRQOldCRUNLQFNZQkFTRS5DT00AAAADAAA5AAAAAAsAQDoBAAAAAgH2DwEAAAAEAAAAAAAA BdCHAQiABwAYAAAASVBNLk1pY3Jvc29mdCBNYWlsLk5vdGUAMQgBBIABAB0AAABSRTogT0RTIGNv bmN1cnJlbmN5IHF1ZXN0aW9uADoKAQWAAwAOAAAAzQcIABsADAABACcAAwAuAQEggAMADgAAAM0H CAAbAAsAJAAuAAMAVwEBCYABACEAAABDOERFQzFBMEJDMUVEMTExODYyMjAwMDBDMEFGNDQ5NwAf BwEDkAYA3AgAABIAAAALACMAAAAAAAMAJgAAAAAACwApAAAAAAADADYAAAAAAEAAOQDAED5EE7O8 AR4AcAABAAAAHQAAAFJFOiBPRFMgY29uY3VycmVuY3kgcXVlc3Rpb24AAAAAAgFxAAEAAAAWAAAA AbyzE0Q2oMHeyR68EdGGIgAAwK9ElwAAHgAeDAEAAAADAAAATVMAAB4AHwwBAAAAFwAAAElOVEVM SUNPTS9DRU5UUkFML3NwYWwAAAMABhArTITbAwAHEM8HAAAeAAgQAQAAAGUAAABXQU5EQU1BWUJF WU9VUkVYQU1QTEVJTExVU1RSQVRFU1RIRSJQRVNTSU1JU1RJQ0xPQ0tJTkciSU1QTEVNRU5URURC WUlORk9STUlYQVNBR0FJTlNUVEhFIk9QVElNSVNUSUNMAAAAAAIBCRABAAAAWQcAAFUHAACZDgAA TFpGddQbNZL/AAoBDwIVAqgF6wKDAFAC8gkCAGNoCsBzZXQyNwYABsMCgzIDxQIAcHJCcRHic3Rl bQKDMzcC5AcTAoM0BEYTMzEguwhVAoB9CoAIzwnZOxfPeDI1NQKACoENsQtgbvBnMTAzFFALChVh C/KSYwBAIFcAcGRhCoUBCoVNYXliZSB5wQhhIGV4YW0LUB4gZwMQCkATwHJhE9AEIHSyaB4gInAH kACQbQQAkHRpYyAXYGNrC4B8ZyIfAB7CB4ACMAmAIKBieSBJbgIQciBwyHggYQQgYWcLcRPAXR/E bwUwIG8hfU8fcGNxHuA/IEgEkB4gImdkMm8HkW5vBUAA0HR19QdAbCJAdwUQE9AfwwNgDncfsR8R H8NDT01NxElUHKBPUksfAAQgGwnwBaB1IeEX0Swgc35vHwAFQAPwKbEoqQWSZFsEICKBIB9ABJBB H8Bv7mcRwB/gBcAoBaAAgAWQqnUkYHYpI2ktgCki4Csc0C0vQi5HLiJac3UPB4If0B+AH8NJTlNF /FJUBCAAwCJAHhEDYB8g+yICANBrK9UnhwnAAaAvpfcokB/gA6BjANAf4CThH8O/I1EEkCHyKUML gCIgdQ3Q/wSQK8A3MRfQIvEmJDK/KTT7NFUFoG0kMAJAIgEwMgaQvywCKuE0yiwWEYAvgWgG8F8H kTkxKQUv4TIQSClQZf8vgAXAH9Aq4SxDAiAocR6QdwtTH8MRsHEKUCsgHiBv3z2QOOYeQwGRHuAu HQwK9CBsaTE0NALRaS3PRnMM0EZzG58gVELyH2HfGtAeICSlNIE/UWkFsTRVvx4QN4At8UOiH9Iv QHIX0AMCMAqFSVNPTEFUAElPTiBMRVZF7kwyET2WAHB5QXE34Sfgpy6iM0EDoERJNBBZB/C4RUFE K8Af0QOgZAhx/zfhCoUzmCIiMTMrwE6BLeDvAwBDQThBDbB4B5ECIB/D/0SzJ8AJ4CIQLlA0gzog IhD/UUYr8AnwMuAmwTNDU0RVAP8EESrhJ9JKUQtgIfEyEEjQ50GWT4E38HVwMSUKhStR3wMRLfQ8 pB+0SSJzKBFKYG8DoDgiORJFDUkokAhgbFsiEDLgZy5wI3FrCeBw/zfTXKg0MQWwOrEuUCQwJIDP VngKhS7xTARjeUDYXsD5HrAgah9BNmAKUCBBN+HnAaAIYCOEcG8gQVTCSzOvVBE9kFFJE1BvRNFt K8MvXsA0VSigAiBnRQ1IVKJIHQxTdWosEVAHQDNFH0YkODBGrwtZMTbPCqADYBPQKCAgLXAXCofX bssMMG+WRgNhOnEeb5YTDIIcpCBCBZBrW1MgTVRQOndLEWtA3nMeACLwRPA8oV1wv3HNfwZgAjBy /3QMCYBXwRzgeU0rwEFfUGSSMjcrwDHAOTk3IDc6b2AUsOZNdu9xzVRveS90CwuA7SKELUZQE8BA IqBjgBPgHwWwgiFQ0H0vd/51Ympnb9F/T3QLT0QF8GLpIJ9DQSRRAiBsH0YjMzZHl28UUQvyb5Zq Bml7kR8gIfVd7SdkYHIrUAMAN+GHIpg3LjIWQFQxSFBS4vciEI1yH2B5N9Mr8CtQBIH/E8AwMjcw M2GCEGLYTGYt8v8EIDeAA6BCYW/RMhBesT9T33UQVKRUMTcwJHBojWEvgVMRsU1wT0MqwE2HIEXR VUJST1dFBlWSoyxD/x4RJ4A30jhUQMFIw0oXjXL/EbA3wyrhM0M9gQIgHiAt8n8KhV2DOHKYVFVR VGcrwCr+KifQniBPBS3ykuM4VFsFfx/SbcCQkZIpKtJtwAMAc9kf4GQ6HQyjM1Uz8RZA26Pso5Iy otpwGC2j7KXp8aLaQkVHM9Aqg6Lan4W2QamPBUBCqs8FQEOi2m+j7ajvr/+qGFivn7CvWd+xz6Po KimsDwVARLZfBUC+RbefcpGi2rWvHWZEX+D9CfBkTtOUwi6ELfIR4CeDT3UQM/AqcJXYVE8coEHv KmEFsSfRK8EvH+EsQgqF+zfALpN3C3Cf1i3yFkBb1v8FsSxDLnEwIR6ATCAFsRfQ/ygwBKGQwz8W Vgg0wjUzUIP3j7EjFEUNQQGAnsUWQKGp9CAmvaQnPfIpYQdAK+Hfw+ILgFBjMGEy4Gyc01UA/wfg KUKb5lRKohApUKCEy1H/QfE9kMKDyuGYlVB0ypaYle+iX6MxLjAxcENPsLiwcqDzsaCz0CBaXe4f YAiQVTH/QYJUMSYkjxTKm0EQJsGdIb8EkB7gP2HFQh/Qz+YsCoX/B4AAcDfUVzK9w05SVMJVUf/L Y5+FlOEe4clXnIfSP9NH/9Qz08SMn2TIM0OVQyQxLvH/bcBk0BfRK9EHgE60OTEiZv8rwEsWH9EK hZl/moQn0pGIfyvAKMDK4S4BBzGPFEFzKq5kH3AkUihSKgqFYTmB9yggBCAgIHIiggBwQ4CTkR5D A5FOgZtiH+BscD+3HQxI0ABwazkTVdB27HL/jIZwl3TYHQzx/4lPR8uLLQUW8QD2gAAAAAMAEBAA AAAAAwAREAAAAABAAAcwQGGVyg+zvAFAAAgwQGGVyg+zvAEeAD0AAQAAAAUAAABSRTogAAAAABQL --0__=CTPObHElJp77K2n2a6YIFG0arZmvE5ZusMET9C3xrRGIiepUQnGlk26m--